from:"Wolfgang Breitling"

Title: RE: Remote DBA





At 02:18 PM 4/3/2003 -0800, you wrote:
From: Nelson Flores [EMAIL PROTECTED]
VNC and OpenSSH are slow

Anything cross platform isn't going to have the same kind of optimization 
as a single platform solution. I find them fast enough to be useable, but 
you're right terminal server on windows is faster for windows boxes.


However, there are limitations for what you can do in a terminal server 
session. Those same limitations do not appply to VNC.



and VNC is still a little unstable (IMHO),

What kind of trouble did you have, mine has been rock solid.

I personally manage my windows 2000 Oracle DB with a VPN and then a
Terminal server window direct to my desktop - from there I have all
the tools that I usually have - notepad, mspaint, dir :).

That's a good solution, but costs money for those terminal server licenses.
My department has little or no budget for non-critical purchases (and 
sometimes none for those either hehe) so I have to go with the free 
option, and besides, this lets me manage my linux stations from my win2k 
boxes and vice versa.


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



 


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier lectronique est une communication prive  l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris  recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.

RE: Remote DBA

At 05:27 PM 4/3/2003 -0800, you wrote:



-Mensaje original-
De: Wolfgang Breitling [mailto:[EMAIL PROTECTED]
Enviado el: Jueves, 03 de Abril de 2003 19:24
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Remote DBA


At 02:18 PM 4/3/2003 -0800, you wrote:
From: Nelson  Flores [EMAIL PROTECTED]
VNC and OpenSSH are slow

Anything cross platform isn't going to have the same kind of optimization
as a single platform solution.  I find them fast enough to be useable, but
you're right terminal server on windows is faster for windows boxes.
However, there are limitations for what you can do in a terminal server
session. Those same limitations do not appply to VNC.
Like what ???
We can not, for example, start our tuxedo servers from the terminal window, 
but it works fine from a VNC connection.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: oracle job question

Yep, that's the reason.

At 03:53 PM 4/3/2003 -0800, you wrote:
I am trying to set a job in db , but db is not executing the job although 
it passed the next_date (dba_jobs).
when I looked at  parameters i found JOB_QUEUE_PROCESSES =0  ( i guess its 
default).
sholdn't oracle automatically kicks a process ?
Is this the resson job is not being executed .
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 8i to 9i clone

If it is cloned from a cleanly shut down database / clean cold backup and 
you don'nt need to change the dbname it should be possible. You'd run the 
9i upgrade immediately. It wouldn't be any different from an upgrade in place.

At 02:18 PM 4/3/2003 -0800, you wrote:
List,

Has anybody cloned a 8i database and moved it over to 9i?  I want to clone a
database that is on 8.1.7 AIX 4.3 32-bit now,  move it to a different server
that is running 9iRel2 AIX 5 64-bit.  Is this possible and has it been done?
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: submitting statspack.snap through dbms_job

2003-04-04 Thread Wolfgang Breitling

Title: Re: submitting statspack.snap through dbms_job





var jnr number


exec dbms_job.submit(job=:jnr, what='statspack.snap(5);', 
next_date=trunc(sysdate)+10/24, interval='trunc(sysdate)+10/24');
exec dbms_job.submit(job=:jnr, what='statspack.snap(5);', 
next_date=trunc(sysdate)+14/24, interval='trunc(sysdate)+14/24');


commit;


At 06:23 AM 4/4/2003 -0800, you wrote:


Quick question, I want to submit statspack.snap at 10:00am and 14:00pm 
through dbms_job, it's friday afternoon and my head is hurting me from a 
heavy night and the manuals are as clear as mud.

Thanks



http://uk.yahoo.com/mail/tagline_plus/?http://uk.promotions.yahoo.com/yplus/btoffer.htmlYahoo! 
Plus - For a better Internet experience


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



 


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier lectronique est une communication prive  l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris  recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.

Re: why ?????????

2003-04-04 Thread Wolfgang Breitling

Title: Re: why ?

Oh, Oracle KNOWS that where 1=2 is always false, so it knows that the
resultset will be empty. But it has to come up with a plan, so I assume it
just takes the simplest plan it knows - a full table scan - knowing full
well that it does not actually have to do the scan.

At 05:58 PM 4/3/2003 -0800, you wrote:
I'm going to take a guess, since I know that there is at least one member
of the list that can do better, but here goes...

Predicates (where clauses) are applied as result sets are processed.
Oracle has no knowledge that the predicate will always evaluate to false.
This results in a row being read, checking the predicate and rejecting the row.

Okay, gurus, please correct me if I am wrong (by the way, I'm being
totally serious...)

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

Re: count(*) vs count(0)

2003-06-06 Thread Wolfgang Breitling

It may be at your end, I have no difficulty getting to asktom. Here is a 
link to one of the threads about count(*) vs other count() techniques:

http://asktom.oracle.com/pls/ask/f?p=4950:8:26428220175898::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4567980767113,

At 06:45 AM 6/6/2003 -0800, you wrote:
Having tested both forms... it looks like to be the
same...
asktom has a detailed thread about this...

sorry, because I'm not posting the url link, but in
this moment the site is experiencing 'technical
difficulties'
:-)
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 10053 trace

2003-05-27 Thread Wolfgang Breitling

At 04:15 PM 5/27/2003 -0800, you wrote:
hi gurus
i am trying to generate 10053 trace in 9i and have met
with no success.
i used both 'alter session set events' and
'dbms_system.set_ev'.
i used the above two to generate 10053 trace for the
same query in 8i w/o problems.
has anything changed in 9i?
can you please help me on this?
There is no difference in creating the 10053 event trace in 9i as compared 
to 8i. The trace itself is a bit different in some areas. What is the 
problem you are having? No trace at all? Can you greate an ordinary 
sql_trace and find it in the udump directory? BTW what platform?

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 10053 trace

2003-05-28 Thread Wolfgang Breitling

If it has only the query part then the sql was not parsed by the CBO but by 
the RBO. Make sure the tables in the query are analyzed, or force the CBO 
by putting a hint in, even a bogus one that doesn't affect the plan like 
asking for a non-existing index.

At 09:14 PM 5/27/2003 -0800, you wrote:
hi

thanks for the info.i could make it work for some 9i
sqls but a particular sql is not generating trace.
the 10053 trace file has only the query para but no
other details. the query selects from a simple view
built on a table. will this make a difference?
here is the query
select id from sai.ext_view
where (sai.ext_view.ind=0)
order by id;
where sai is the schema name,ext_view is a view built
on a table and ind is a column in the table
to force it to parse everytime(test1 to testn..is this
a good approach?), i used hints like this below
select /*test1 */ id from sai.ext_view
where (sai.ext_view.ind=0)
order by id;
and finally when i see the 8i trace, there is a line
which says bitmap access path rejected under access
path:index(index only).
what does this mean?

btw,the platform is aix 5l and oracle version is
9.2.0.3
thanks again for taking some time to look into this
sai
-- Wolfgang Breitling [EMAIL PROTECTED] wrote:
 At 04:15 PM 5/27/2003 -0800, you wrote:
 hi gurus
 i am trying to generate 10053 trace in 9i and have
 met
 with no success.
 
 i used both 'alter session set events' and
 'dbms_system.set_ev'.
 
 i used the above two to generate 10053 trace for
 the
 same query in 8i w/o problems.
 
 has anything changed in 9i?
 can you please help me on this?

 There is no difference in creating the 10053 event
 trace in 9i as compared
 to 8i. The trace itself is a bit different in some
 areas. What is the
 problem you are having? No trace at all? Can you
 greate an ordinary
 sql_trace and find it in the udump directory? BTW
 what platform?

 Wolfgang Breitling
 Centrex Consulting Corporation
 http://www.centrexcc.com

 --
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 --
 Author: Wolfgang Breitling
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list and web
 hosting services

-
 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from).  You may
 also send the HELP command for other information
 (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sai Selvaganesan
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

At 02:59 AM 5/28/2003 -0800, you wrote:
Okay, I have a developer here who has been reading the docs (this can
be dangerous!)
we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date
Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.
The developer is telling me to create a skip scan index instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.
Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.
This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris
Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.
As others already said, it is a index skip scan access method, not a 
skip scan index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Hidden Columns

Hidden columns are used if you use certain features of Oracle, e.g. 
materialized views, replication, objects

At 06:44 AM 5/28/2003 -0800, you wrote:

Hi all,

I know this is RTFM but I can't find the right part in the FM to R.  So 
any help would be appreciated:

In the context of:

 DBMS_STATS.GATHER_DATABASE_STATS(method_opt='FOR ALL HIDDEN COLUMNS');

what is a hidden column.  I tried gathering stats FOR ALL HIDDEN COLUMNS 
and it didn't seem to gather any stats for any columns.  What is it meant 
to do?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

I was about to post the results of my test which also did prove you wrong. 
I ran the test with a 10046 level 8 trace to show the individual index 
block reads which nicely show why it is called a skip scan, but since you 
already proved yourself wrong there is no need.

BTW, as of Oracle 9 you don't necessarily need to restart the database to 
reset the pools. This should do the trick:

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
alter system flush shared_pool;
At 06:08 PM 5/28/2003 -0800, you wrote:
Here is the idea:
Index test_skip1 is located in the tablespace INDX which has
one file, FILE#=5
I restart the database, execute your query, then see V$FILESTAT for blocks 
read.
(select PHYBLKRD from v$filestat where file#=5;)

Then restart the database, execute query asking for a fast full scan and see
how many blocks do get read. If the number is the same, then the 
conclusion is inevitable.
So, here we go:



SQL set autotrace on explain
SQL select /*+ index_ss(test_skip1 ) */
c1,c2
from test_skip1 where c2 = 100;
  23
C1 C2
-- --
 1100
 2100
Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
  52)
   10   INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
  d=302 Bytes=7852)
SQL select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
10
---DATABASE RESTART---

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production
SQL set autotrace on
SQL select /*+ index_ffs(t test_skip1_pk ) */
c1,c2
from test_skip1 t where c2 = 100;
  23
C1 C2
-- --
 1100
 2100
Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
  2)
   10   INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
   Card=302 Bytes=7852)




Statistics
--
300  recursive calls
  0  db block gets
777  consistent gets
724  physical reads
  0  redo size
464  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  6  sorts (memory)
  0  sorts (disk)
  2  rows processed
SQL select PHYBLKRD from v$filestat where file#=5;

  PHYBLKRD
--
   722
That means that fast full scan will read 722 blocks where skip scan will 
read only 10,
which means that you were right and I was wrong. Obviously, my metodology 
was incorrect
or 9.2.0.1 database that I've tested it on has had a bad bug, which would 
really be
surprising and unusual. Anyway, you are right. That, in turn, implies that 
oracle
indexes are not classic B*Tree structures as I was lead to believe but are 
spiked with
an unknown liquor. Thanks for helping me clarify this.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: skip scan index

I have to give credit to Julian Dyke (BMC) who had this - and many other 
gems - in his presentation at the Hotsos performance symposium in Dallas in 
February. Because of personal reasons he unfortunately could not make it to 
IOUG for his presentation on indexes, but I got to download his 
presentation during the pre-access period. Excellent stuff as well.

I hope I'll meet you someday in person. I always find it endearing when 
someone not only goes out of their way to prove themselves wrong but then 
goes and publishes it to the world. You're an ace.

At 07:50 PM 5/28/2003 -0800, you wrote:

On 2003.05.28 23:04 Wolfgang Breitling wrote:
 I was about to post the results of my test which also did prove you wrong.
 I ran the test with a 10046 level 8 trace to show the individual index
 block reads which nicely show why it is called a skip scan, but since you
 already proved yourself wrong there is no need.
Thanks, Wolfgang! If there is one thing I really excel at, that is proving
myself wrong. I succeeded again.

 BTW, as of Oracle 9 you don't necessarily need to restart the database to
 reset the pools. This should do the trick:

 ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
 alter system flush shared_pool;
Thanks! How did you come accross this little gem?


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: x$bh.dbablk values (repost)

You are running into the classical problem with monitoring an experiment: 
To what extent (no pun intended) does the monitoring change the experiment.

Referencing dba_extent pulls in the segment headers of all segments owned 
by 'BCA'. When you interrogate x$bh have nothing else in your sql. Dump the 
result set somewhere and then get the information from dba_extents so that 
you can match dbarfil and dbablk to a segment. I usually use excel and its 
vlookup function to do that.

At 09:19 AM 5/29/2003 -0800, you wrote:
I am reposting this in the hopes that someone can help me with this puzzler.

I am working on determining which objects have 'hot blocks'.
In two different sessions, I issue select count(*) from random_data where 
rowid_rownum in (1,2,3); repeatedly to see what happens with the touch 
count (x$bh.tch).

In another session, I look for the blocks related to this object by 
issuing the statement:
select x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk, 
b.class, b.state, sum(b.tc
from dba_extents x, x$bh b
where b.dbarfil = x.file_id
and b.dbablk between x.block_id and (x.block_id + blocks - 1)
and x.owner = 'BCA'
group by x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk, 
b.class, b.state;
[snip]

The oddity is that I have restarted the instance and have only issued 
queries against the random_data table. Since I am the only user on the 
system, I know that no other sessions are accessing the objects.
The interesting bit in all this is that the blocks other than random_data 
listed in x$bh are the segment headers.

select segment_name, file_id, block_id, blocks, block_id+blocks-1
from dba_extents
where owner = 'BCA'
 and extent_id = 0
order by file_id, block_id;
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: dbms_stats

I wouldn't call 1.4%  [ (603826-595500)/603826 ] way wrong. Actually, for 
a 1% sample I find that pretty good. The problem I found with low sampling 
percentages is if you have skewed column values. If some values occur very 
often and others rather seldom, a 1% sample may only encounter the 
frequently occurring values and none of the infrequently occurring ones and 
come up with a really way off estimate for num_distinct.

At 10:05 AM 5/29/2003 -0800, you wrote:
Hi John,

Yes, monitoring was set.  I wouldn't see anything in *tab_modifications if 
monitoring wasn't set.

Here's a new twist.  What percentage are you comfortable with for valid 
estimates?  I attended a seminar given by Jonathan Lewis a few weeks ago, 
and he stated that adequate statistics can be gathered using 1% 
sample.  That was great news to me, who has time for huge estimates?  OK, 
so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and 
estimate_percent set to 1.  The rowcounts are way wrong.

(FACP-LISA)SELECT COUNT(*)
  2  FROM VEGAS_MART
  3  PARTITION (MAY_28_2003);
  COUNT(*)
--
603826
(FACP-LISA)select num_rows
  2  from dba_tab_partitions
  3  where table_name = 'VEGAS_MART'
  4  and partition_name = 'MAY_28_2003';
  NUM_ROWS
--
595500
(FACP-LISA)

And Jonathan if you happen to read this email, if I am mis-stating what 
you stated in class please correct me.  I am on 8.1.7.4 and that may be 
the difference.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: skip scan index

Hey, with all that praise being heaped on you for publicizing your 
wrongness, who would ever want to be right?
:-)

At 10:51 AM 5/29/2003 -0800, you wrote:
Thanks, Pete.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: dbms_stats

The answer is as usual it depends. If the table has a reasonably uniform 
row size and the blocks are approximately evenly utilized, then the analyze 
can extrapolate the total number of rows fairly accurately even from a 
small sample. However, if the row size fluctuates wildly, or if the block 
utilization fluctuates heavily, then you need a larger sample for accurate 
estimates of the totals.

At 12:14 PM 5/29/2003 -0800, you wrote:
Hi Wolfgang,

In the grand scheme of things, that probably isn't awful.  However, if the 
analyze can't get the row count right (how easy is that?) then how can I 
trust it to get the rest of the statistics correct?  Just my two cents.

Thanks for your reply.
Lisa
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Tablespace management.

2003-05-31 Thread Wolfgang Breitling

So what is wrong with having the SLOTS table occupy several hundred 
extents? If it grows to 500MB it will occupy 1000 extents, so what. If it 
were to grow into GB I'd probably make the extents 1MB and swallow the 
wasted .5M in the CELL extent - what is half a meg when you're in the GB.

As for Peoplesoft, I manage Peoplesoft systems as well and I have separated 
the tables into tiny (extent size 16K, tables do not have more than 1 block 
- ~90%-95% of all tables in the system, most of them even empty), small 
(extent size 64K), medium, large, and XXL plus one for the active _TMP, 
_WRK, and _TAO tables, and then the same for the indexes. Works like a 
charm. The only tablespaces I have to worry about are the large and xxl 
table and index tablespaces. Everything else is pretty much static.

At 07:59 AM 5/30/2003 -0800, you wrote:
Richard,

My troubles come mainly form PeopleSoft and some in-house created 
applications.  I'll use the in-house applications as the example since 
their simpler.

Our CIM system has tables that contain very few rows of data, 
like the identification information for each robot(CELLS).  Now there are 
only 30 robots on the longest/most complex line we have (BTW: due to the 
duhvelopers of this application each line needs it's own instance on it's 
own server, don't ask why).  Now this table NEVER grows beyond 512KB is 
size.  But each robot can have up to 1024 component slots (512 on each 
side) that need to be defined with what is in them (SLOTS).  This table 
easily gets into a couple of MB but then sits there since we do tons of 
updates but no more inserts.  If we're doing LMT's then to optimize the 
storage on this mess I either need 2 tablespace or else set the uniform 
extent size to 512K and allow the SLOTS table to have several extents.

This example is one of the simpler ones, there are a lot more 
that get even more problematic, like those for our test data.  If 10i has 
bad news on this front it may well become the straw that breaks the 
camel's back for Oracle around here.  We're already toying around with DB2.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RE: Tablespace management.

2003-05-31 Thread Wolfgang Breitling

What Oracle documentation would that be?

At 09:39 AM 5/30/2003 -0800, you wrote:
i read some oracle documentation that recommends you keep the number of 
extents below 1024.

do you feel that this is inaccurate in an LMT? What if Im stuck with 
dictionary tablespacse and am not allowed to change? Does it matter? I do 
keep all my extents uniform. I thought there were issuse with contention 
on FET$ and UET$ in dictionary managed tablespaces for a transaction database?

or am I just wrong?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: RE: Tablespace management.

2003-05-31 Thread Wolfgang Breitling

To quote the paper:

Oracle supports an unlimited number of extents in a segment. The 
performance for DML operations is largely independent
of the number of extents in the segment. However, certain DDL operations 
such as dropping and truncating of segments are
sensitive to the number of extents. Performance measures for these 
operations have shown that a few thousand extents can be
supported by Oracle without a significant impact on performance. A 
reasonable maximum has been determined to be 4096.
The goal of our recommended algorithm is to keep the number of extents 
below 1024 which is well within the range that
Oracle can efficiently handle. When a segment reaches 1024 extents it is a 
candidate to be moved to the next larger extent
size tablespace. The segment does not necessarily have to be moved 
immediately or at all. The segment may be near its peak
steady state size, in which case even if it has a few thousand extents, it 
should be left where it is. It is only the segments which
are growing that have to be targeted and potentially moved to tablespaces 
with larger extents.

A few comments:

- This was written in the days of DMTs, so not everything that is said 
applies to LMTs. The nr of extent stuff certainly does not.

- Event within the confines of DMTs it clearly states that only drop and 
truncate are sensitive to the nr of extents (because of the necessary DML 
to FET$ and UET$).

- And even then, 1024 is not really a limit, just a recommended comfort 
level: The goal of our recommended algorithm is to keep the number of 
extents below 1024 which is well within
 the range that Oracle can efficiently handle and  The segment does not 
necessarily have to be moved immediately or at all

At 11:59 AM 5/30/2003 -0800, you wrote:
The How To Stop Defragmenting... paper says it in section 2.1.4.

Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Gathering statistics on function-based index

2003-06-03 Thread Wolfgang Breitling

Have you tried methods 3 or preferrably method 4:

method 3: analyze index index_name compute statistics

method 4: exec dbms_stats.gather_index_stats(...)

What do you mean by the output below is similar for both methods? What 
are the differences? Can you use Tom Kyte's print_table procedure to list 
the contents of user_indexes for the index after each of the analyzes?

At 05:45 AM 6/2/2003 -0800, you wrote:
Hi,
Can someone shed some light on the differences of gathering statistics on 
a function-based index using the following two methods?
method 1: analyze table table_name compute statistics
vs
method 2: create unique index index_name on table_name 
(upper(columne_name)) compute statistics;

I could not get the CBO optimizer to use the function-based index if I 
were to gather statistics on my index using method 1. However, if I were 
to use method 2, the function-based index is used. Method 2 would require 
me to drop the index everytime I gather statistics on the index.
I tested this on 8.1.7.4 and 9.2.0.3.

Method 1: Execution Plan
--
SELECT STATEMENT   Cost = 3211
 COUNT STOPKEY
   VIEW
 SORT ORDER BY STOPKEY
   TABLE ACCESS FULL AC_FORWARD_DEST
Method 2: Execution Plan
--
SELECT STATEMENT   Cost = 1068
 COUNT STOPKEY
   VIEW
 TABLE ACCESS BY INDEX ROWID AC_FORWARD_DEST
   INDEX RANGE SCAN DESCENDING ACFD_INDX1
After analyzing the index using both method 1 and 2, the output below is 
similar for both methods:

select 
clustering_factor,avg_leaf_blocks_per_key,avg_data_blocks_per_key,distinct_keys 
from user_indexes where table_name='AC_FORWARD_DEST' and 
index_name='ACFD_INDX1'
/
CLUSTERING_FACTOR=80774
AVG_LEAF_BLOCKS_PER_KEY=1
AVG_DATA_BLOCKS_PER_KEY=1
DISTINCT_KEYS=914532

select num_rows, blocks from user_Tables where table_name='AC_FORWARD_DEST'
/
NUM_ROWS=914532
BLOCKS=13066
Thanks!

Elain

_
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: elain he
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Oracle's use of Indexes

2003-06-03 Thread Wolfgang Breitling

It will be hard to find what changed since you don't have the information 
on exactly what production looked like when it was using the index. 
Whenever you do somthing which may affect the statistics, make a backup of 
the current statistics with dbms_stats.export_table_stats(..., cascade = 
true). Then you can always restore the statistics if what you try doesn't 
work out. You can even import the saved statistics into you test schema and 
see if you then get the same execution plan there as well. If yes, then 
it's the statistics, if no then it's some other parameter(s) that cause the 
difference.

When you are saying test - and now production - is not using the index, 
what is it using instead? an FTS or a different index. If per chance two 
indexes were tied in the cost, the order in which they were created may be 
used as a tiebreaker. I am not 100% certain, but I have the impression that 
the one with the lower object_id is then used, i.e. the one created first. 
By dropping and re-creating indexes you change the object_id and thus may 
change the index choice in a tie.

Ultimately, an 10053 event trace is the best way to pinpoint the cause for 
the different plans.

At 05:45 AM 6/2/2003 -0800, you wrote:
OK, I'm confused. Maybe it's Monday morning and my brain's not working.
We have a production schema and a test schema on the same Oracle 8.1.7
instance, running on Windows. They both have a customer table, with 3
million and 2 million records respectively. They both have the same
indexes, and both have been analyzed today. Production used an index and
took 40ms. Test didn't and took 20s. I played around, analyzing,
dropping and creating indexes etc. Now neither of them use the index,
both taking around 20s.
I can add a hint, which works, but I want to know what changed.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: cannot allocate new log

2003-06-03 Thread Wolfgang Breitling

I am siding with Scott and would double-check that automatic archiving is 
on. Also, what are your log_archive_dest settings? If you are using 
log_archive_dest_n make sure you add the reopen = nn attribute.

At 08:49 AM 6/2/2003 -0800, you wrote:
Nuala,

It looks like the DB is waiting for you to manually archive the 
logs.  Like Lisa suggested, could you do an archive log list from the 
sqlplus prompt and post the results?  There are two steps in setting up 
archiving.  You have to set the proper parameters in the init.ora file, 
and then bring the db up in mount mode to start the archiver before you 
open the database.

-Scott
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Oracle's use of Indexes

2003-06-04 Thread Wolfgang Breitling

The cardinalities are the same, but the costs are different. It looks as if 
production has somehow optimizer_index_cost_adj set to 50 or lower or has 
db_file_multiblock_read_count set to 8. It's the same instance so that is 
not possible unless they are changed at a session level.
To check that, or any other init.ora differences that may be of importance 
select the name-value pairs from v$parameter in both schemas and do a diff. 
Of course the easiest is to diff 10053 event traces and see where they 
deviate, which will hopefully provide a clue as to why.

At 09:44 AM 6/3/2003 -0800, you wrote:
 From: DENNIS WILLIAMS
I believe that all the statistics that CBO uses to make a
 decision are in
 USER_TABLES and USER_INDEXES. You might compare the values
 for both tables
 to see if there is a difference that might cause the CBO to
 make a different
 decision.
I'll look into that, thanks.
Are you using different usernames? Any chance one
 session is doing
 an ALTER SESSION?
Yes, different usernames, but neither are doing an ALTER SESSION.
Wolfgang, yes, it's doing a FTS instead of using the index. I'll look at
doing a trace on it tomorrow.
Daniel:
Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=7060 Card=338
Bytes=52728)
   10   SORT (ORDER BY) (Cost=7060 Card=338 Bytes=52728)
   21 TABLE ACCESS (FULL) OF 'TBL_CUST_MAST' (Cost=7050 Card=338
Bytes=52728)
Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=12711 Card=338
Bytes=52728)
   10   SORT (ORDER BY) (Cost=12711 Card=338 Bytes=52728)
   21 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_CUST_MAST'
(Cost=12701 Card=338 Bytes=52728)
   32   INDEX (RANGE SCAN) OF 'IDX_CUST_MAST_EMAIL1'
(NON-UNIQUE) (Cost=887 Card=338)
Top plan takes about 20 seconds, the lower one less than 1 second.

* DBA_TABLES.NUM_ROWS = 3,161,764
* DBA_TABLES.BLOCKS = 73,294
* DBA_INDEXES.CLUSTERING_FACTOR = 118,131
* DBA_INDEXES.AVG_LEAF_BLOCKS_PER_KEY = 1
* DBA_INDEXES.AVG_DATA_BLOCKS_PER_KEY = 6
* DBA_INDEXES.DISTINCT_KEYS = 18,767
* DBA_INDEXEX.BLEVEL = 2
* DBA_INDEXES.LEAF_BLOCKS = 8850
index is on CLI_CD, CUST_EMAIL1, CUST_STATUS, densities are
CLI_CD = 0.1
CUST_EMAIL1 = 0.6
CUST_STATUS = 0.5
db_file_multiblock_read_count = 16
Craig Healey
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: dbms_stats via dbms_job - syntax question

2003-06-10 Thread Wolfgang Breitling

Why not submit it exactly the same as you do it interactively:

declare
l_job number;
begin
dbms_job.submit 
(l_job,'dbms_stats.gather_schema_stats(ownname=''RPT_3G_MASTER'',estimate_percent=10,cascade=true);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/

All you need to do is replace the single quotes around the schema name with 
double quotes.

At 06:34 AM 6/10/2003 -0800, you wrote:
Listers,

Looking for a bit of help with the syntax to submit a dbms_stats run via 
dbms_jobs

The following syntax works fine to run the procedure interactively
execute 
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true);

but trying to get that into an dbms_job is destroying my brain.The syntax 
I am trying is based around this script

declare
l_job number;
begin
dbms_job.submit 
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/

The problem area is the cascade keyword. I am using single quotes in the 
line but 2 of them around the schema name as that is a varchar2. Ideally I 
think I want 2 single quotes around the cascade but I cannot get it to 
work properly. The above example submits the job as
Job What
==
2   dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');

but that fails to run

Has anybody got any ideas.

I seem to recall Connor McDonald having some information about this on his 
web site but it doesn't appaer to exist any more
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: wait event puzzler

2003-06-11 Thread Wolfgang Breitling

db file sequential read means single block read, which can be either from 
an index or from a table. Likewise, db file scattered read means 
multiblock read, which again can be either from a table or from an index.

At 10:01 AM 6/11/2003 -0800, you wrote:
Good day all,
We have a bunch of queries in our data warehouse environment that are 
taking a long time to complete.
I did a select on v$session_wait on one of the queries when it is running 
and it shows the wait event to be 'db file sequential read', which means 
it is scanning index blocks, correct?  But when I put the p1 p2 p3 values 
into another query to see which segment it belongs to, it shows me a 
table, not an index. How can this be?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: selectivity of predicates with LIKE - diff between 8i and

2003-06-11 Thread Wolfgang Breitling

 you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: wait event puzzler

2003-06-11 Thread Wolfgang Breitling

Yes, 'db file sequential read' is always a single block read. It is 'db 
file scattered read' which is = DB_FILE_MULTIBLOCK_READ_COUNT

At 10:35 AM 6/11/2003 -0800, you wrote:
A SINGLE block? I was convinced that it was = DB_FILE_MULTIBLOCK_READ_COUNT
blocks.
Are you sure?
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, June 11, 2003 2:19 PM
To: Multiple recipients of list ORACLE-L
Dennis:

THe event 'db file sequential read' is NOT always an INDEX Scan. It is
just a single block read which TYPICALLY happens during an INDEX scan.
The word 'TYPICALLY' is the key here.


Best Regards,
K Gopalakrishnan
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL Query Help

2003-06-13 Thread Wolfgang Breitling

select columns from table A)
where predicates
  and datecreated = (select min(datecreated)
from table b where b.cid = a.cid and b.pid = a.pid)
At 08:14 PM 6/13/2003 -0800, you wrote:
I have a table with records like this

  CID S TO_CHAR(DATECREATED, MESSAGE PID
- -   --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service 
change1*
3 N 01-feb-1974 21:45:45 service change1
1 N 01-jan-1974 12:34:45 msisdn change 1*
1 N 01-jan-1974 19:45:45 service change1
2 N 01-jan-1974 19:45:45 service change1
1 N 01-nov-1974 17:45:45 service change1
1 N 01-nov-1974 19:45:45 service change1



I want to display only the records with the *(not a value stored in the 
database.just used as a marker here).
i.e the records which meet the following.

1.earliest date
2.if there are multiple occurances of records with the same cid and pid 
combination i want only the record for
the combination of cid-pid and with the most earliest record(oldest time 
stamp).

i want to achieve this

  CID S TO_CHAR(DATECREATED, MESSAGE PID
- -   --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service 
change1*
1 N 01-jan-1974 12:34:45 msisdn change 1*

I need some help in getting the query that can get the results like that.

Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: sequential waits -- how to proceed

2003-06-17 Thread Wolfgang Breitling

Your pl/sql procedure is obviously doing a sql  per row updated rather than 
a set update. Unless you change the procedure you can expect only marginal 
improvement from any other measure.

At 03:29 PM 6/17/2003 -0800, you wrote:
Hello ALL,
   Oracle ver is 9.2 running on EMC array. I am executing a pl/sql
   procedure which does an update on a fact table. There is an unique
   index on the fact, with clearly shows up in the explain plan for
   udapte.
I ran 10046 event for a 18 min duration during this update process and
then killed it.
On doing a tkprof on the trace file with waits set to Y, i get
call count   cpuelapsed   disk  querycurrent
  rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
 0
Execute 470509238.311091.93 1178541413284 479488
470508
Fetch0  0.00   0.00  0  0  0
 0
--- --   -- -- -- --
--
total   470510238.311091.94 1178541413284 479488
470508
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 36  (NEVADMIN)   (recursive depth: 1)
Rows Row Source Operation
---  ---
 470508  UPDATE  (cr=1413396 r=117854 w=0 time=1049454599 us)
 470509   INDEX UNIQUE SCAN DM_ACTUAL_CASHFLOW_HIST_UK (cr=1411527 r=3916
 w=0 time=49102823 us)(object id 31693)
Rows Execution Plan
---  ---
  0  UPDATE STATEMENT   GOAL: CHOOSE
 470508   UPDATE OF 'DM_ACTUAL_CASHFLOW_HIST'
 470509INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
   'DM_ACTUAL_CASHFLOW_HIST_UK' (UNIQUE)
Elapsed times include waiting on following events:
  Event waited on Times   Max. Wait  Total
  Waited
     Waited  --
  
  db file sequential read1178542.81
  935.80
  log file switch completion 191.00
  2.23
  log file switch (checkpoint incomplete)211.00
  17.45
  log buffer space20.07
  0.07

As you can see the wait on db file sequential read is 935 ...i am
thinking it is in sec or is it centisec ??
i can see a degradation of perf as time continues. After 10 min , the
number of rows updated stays at 150 rows/sec which is pretty bad.
I have figured out the db file, table and block by looking into p1,p2.
That table is partioned and all the partitions are present on the same
tbs. It has 2 -- 8 gb files and p1 consistently points to either of the 2
data files.
I would like your help in trying to find out how to proceed from here ?.
I am stuck.
Ohter than moving the data files aound to different file systems ans
spreading them around, is there anything else thaty i can do to figure
out this problem.
Thanks,

Sathish.

--
http://www.fastmail.fm - Same, same, but different
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: sequential waits -- how to proceed

2003-06-17 Thread Wolfgang Breitling

Does the UAT have the same data volume, same nr of rows and blocks in the 
table being updated?

At 08:49 PM 6/17/2003 -0800, you wrote:
Thanks for your reply ...

I understand that it is a sql per row update but the same update on UAT
environment works at a rate of
about 2000 rows per sec. Though the data volume is definetly less
compared to PROD, still there is night and day difference between prod
and uat..In terms of the tables themselves, they are analyzed and the
explain plan for update also looks exactly the same bet the environments.
Both the instances are off the same SAN.
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Multiple addreses in tns

2003-07-10 Thread Wolfgang Breitling

Works for me. I just did a the test on Oracle 8.1.7. Here is the trace 
file. Note the nsopen: unable to open transport after trying to find host 
fleming. Then it goes on to try host altair which succeeds. One thing 
though. The host must not exist or be unreachable. If the host is reachable 
but the listener for the requested port is down then the connection fails 
without trying a second address with a different port. You can not fail 
over to a different port on the same server.

nigini: Count in NI global area now: 1
nigini: Count in NI global area now: 1
nrigbni: Unable to get data from navigation file tnsnav.ora
nnftmlf_make_local_addrfile: construction of local names file failed
nnftmlf_make_system_addrfile: system names file is 
c:\oracle\network\admin\tnsnames.ora
niotns: niotns: setting up interrupt handler...
niotns: Not trying to enable dead connection detection.
niotns: Calling address: 
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=fleming)(Port=1526))(ADDRESS=(PROTOCOL=TCP)(HOST=altair)(PORT=1526))(ADDRESS=(PROTOCOL=NMP)(SERVER=altair)(PIPE=ORAPIPE)))(SDU=16384)(TDU=16384)(CONNECT_DATA=(SID=stats)(CID=(PROGRAM=C:\ora81\bin\SQLPLUSW.EXE)(HOST=ALTAIR)(USER=Administrator
nscall: connecting...
nttbnd2addr: port resolved to 1526
nttbnd2addr: looking up IP addr for host: fleming
nsopen: opening transport...
nttcnp: Validnode Table IN use; err 0x0
nttcni: trying to connect to socket 352.
ntt2err: soc 352 error - operation=1, ntresnt[0]=505, ntresnt[1]=60, 
ntresnt[2]=0
nserror: nsres: id=0, op=65, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, 
nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
nsopen: unable to open transport
nttbnd2addr: port resolved to 1526
nttbnd2addr: looking up IP addr for host: altair
nsopen: opening transport...
nttcnp: Validnode Table IN use; err 0x0
nttcni: trying to connect to socket 356.
nttcon: set TCP_NODELAY on 356
nsopen: transport is open
nsnainit: inf-nsinfflg[0]: 0x61 inf-nsinfflg[1]: 0x61
nsopen: global context check-in (to slot 0) complete
nscon: doing connect handshake...
nscon: sending NSPTCN packet
nscon: got NSPTRD packet
nscall: redirected
nstimarmed: no timer allocated
nsclose: closing transport
nsclose: global context check-out (from slot 0) complete
nscall: connecting...
nttbnd2addr: port resolved to 1151
nttbnd2addr: using host IP address: 172.20.230.237
nsopen: opening transport...
nttcnp: Validnode Table IN use; err 0x0
nttcni: trying to connect to socket 364.
nttcon: set TCP_NODELAY on 364
nsopen: transport is open
nsnainit: inf-nsinfflg[0]: 0x61 inf-nsinfflg[1]: 0x61
nsopen: global context check-in (to slot 0) complete
nscon: doing connect handshake...
nscon: sending NSPTCN packet
nscon: got NSPTAC packet
nscon: doing connect handshake...

At 06:54 AM 7/10/2003 -0800, you wrote:
Thanks Rachael

Getting some more feedback on this

What this appears to be is a cluster configuration with a middle ware
capability ( like Oracle FailSafe) to fail a database over from one node
to its backup. This would be the reason each IP is configured with two
ports.

That sounds reasonable Although my docs souw mulitple ADDRESS_LIST =
eg for parrallel or cluster server.
However if I performd the simple test below, as is it- fails, if I place
valid first, it connects. Which raised my concern of Oracle not going to
the next address
??

locdb =
  (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = invalid)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = valid)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVICE_NAME = locdb)
)
  )
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: OEM Repository Problem

2003-07-15 Thread Wolfgang Breitling

This is a shot in the dark, but try and delete (or rename) 
Oracle_Home\sysman\config\omsconfig.properties

At 10:14 PM 7/14/2003 -0800, you wrote:
Hi Listers,

I had OEM repository on my database installed on my PC.

Accidentally I dropped the database. Now I have recreated the database. 
But an not able to use this database to create New OEM repository as the 
OEM config assistant says that the database already had a repository 
installed. If I try to drop the repository it gives me error as the 
repository is not actually present. Please tell me if anyone of u knows 
how to solve this problem. Do I have to reinstall the Oracle Software again.
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: imp table data, but not PK indexes?

2003-07-19 Thread Wolfgang Breitling

Set indexes=no in the import script

At 06:14 AM 7/19/2003 -0800, you wrote:
Hi,

I sent this yesterday but it did not seem to get posted. Anyway I am
posting it again.
I am trying to speed up a schema imp process by import data and indexes
separately to an Oracle 8173 db. While importing table data, I don't want
to import PK indexes which are sitting in tablespace indexes (because I
can create PK indexes later from a script), So I have this running before
imp data:
alter user ABC quota 0 on indexes;
alter user ABC quota unlimited on ABC_DEFAULT_TS;
I found that this way the PK indexes are imported in ABC_DEFAULT_TS,
together with table data. So my question is what I can/should do so that I
can imp only table data into ABC_DEFAULT_TS, and not imp PK indexes at the
same time?
Thanks.

Guang

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: logon trigger to start tracing

2003-07-22 Thread Wolfgang Breitling

The logon user needs to have granted alter session privileges directly to 
her, not just through a role.

At 03:54 PM 7/22/2003 -0800, you wrote:
In an attempt to catch all SQL issued by a report, I created a logon
trigger in the report's logon schema.  (As SYS, I granted the user
EXECUTE on DBMS_SESSION before creating the trigger.)  As the schema
owner, I am able to execute DBMS_SESSION.SET_SQL_TRACE interactively.
On logon, trace files are created, but they contain the following:

*** SESSION ID:(27.4739) 2003-07-22 18:52:53.000
Skipped error 604 during the execution of RPT_PERF.TRACE_ALL
*** 2003-07-22 18:52:53.000
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SESSION, line 126
ORA-06512: at line 2
When SQL statements are executed in the session, no further trace
information is added to the file.
Anyone know what's going on here?

TIA



=
Paul Baumgartel
Transcentive, Inc.
www.transcentive.com
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Index Usage ?!

The trace seems to be from when the index is not analyzed. The CBO then 
uses defaults for the index statistics - leaf_blocks=25 and clustering 
factor=800. These defaults are much lower than when the index is analyzed 
and the resulting cost for using the index is very low (7 compared to 1676 
for a full scan). When you analyze the index, the statistics will be orders 
of magnitude larger - I estimate that the clustering factor will be  
300,000, and therefore the cost of using the index exceeds that of the full 
scan ( still 1676 ).

There are two things you can do
Leave the index un-analyzed if it works for you ( I have a few tables where 
I use that trick)
Set optimizer_index_cost_adj to a value lower than 100 - again if it works 
for you. Test that it does not adversely affect other queries. Many 
advocate that it should be set lower but I have not had any luck with it.

At 04:24 AM 7/24/2003 -0800, you wrote:
Hi Tanel,

quote
did you analyze your table in addition to index as well?
first time you were probably using RBO, which always counts index access
better than table access.
/quote
i have analyzed PROFILE table also and hope it's CBO by default in 9i.
anyway,it is CBO right from the beginning in my case here.
SQLselect num_rows,avg_row_len,chain_cnt from user_Tables where 
table_name='PROFILE';
  NUM_ROWS AVG_ROW_LEN  CHAIN_CNT  BLOCKS
   ---  -  --
736820 168 42  17407

quotebecause of optimizer_index_cost_adj and optimizer_index_caching 
parameters./quote

optimizer_index_cost_adj = 100
optimizer_index_caching = 0
db_file_multiblock_read_count = 16
quoteit's called index skip scanning/quote
Thanx for the info Tanel. I was not knowing this.
As u said ,I have attached the Trace file also.

Kindly throw some light on this Tanel.

Regards,
Jp.
Content-Disposition: attachment;
filename==?iso-2022-jp?Q?memb=5Fora=5F2400.trc?=
Content-Type: application/octet-stream
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Timestamps in trace files (and other trace file oddities)

My interpretation would be as follows:

The wait entries are written whenever a wait ends, so at 15:40:59.149 the 
session has just been waiting .00 seconds for a scattered read of 18 
blocks. At 15:46:06.340 it just had been waiting on a latch free event. For 
the almost seven minutes between, it had not been waiting on any of the 
established wait events. It had either been processing the blocks returned, 
or it could have been waiting in the OS scheduler queue waiting for a cpu 
to become available - or both intermittendly. After coming out of the latch 
free wait, it found that the blocks it had previously read had been flushed 
from the buffer (not surprising after 7 minutes) and needed to read them again.

At 07:04 AM 7/24/2003 -0800, you wrote:
(Tried sending this yesterday. I'll try again)

Dan,
I was running a 10046 (level 12) trace on an awful piece of PeopleSoft SQL
today and got some really odd results in my trace file (8.1.7).
*** 2003-07-23 15:40:59.149
WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
*** 2003-07-23 15:46:06.340
WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
*** 2003-07-23 15:47:53.851
WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
Two things struck me (three if it takes me too long to write this and I get
home late). First, the timestamps show an elapsed time of ~7 minutes, but
the trace file has ela=1 (one onehundredth of a second). The 7 minutes is
closer to reality. Huh???
Secondly, the first scattered read reads 18 blocks starting at 6041. Why
does the next scattered read start at block# 6042?
Any ideas?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Timestamps in trace files (and other trace file oddities)

://www.orafaq.net
--
Author: Henry Poras
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Timestamps in trace files (and other trace file oddities)

It's been about a decade that I have been working with AIX. There must be 
tools that give you a breakdown by cpu. Else you can do a ps -ef or ps aux 
and watch your process. If I'mm right you should see it consuming 99% cpu 
all the time.

I have been looking after Peoplesoft systems for several years and I know 
those cascading NLs. They can drive up logical reads and cpu usage to 
astronomical heights. Do you by any chance have optimizer_index_cost_adj or 
optimize_index_caching changed from their defaults? Care to send me the sql 
and a 10053 trace of the explain? A warning though. I will be out camping 
the next three days, so the earliest I will be able to look at it is Monday 
(july 28th).

At 01:54 PM 7/24/2003 -0800, you wrote:
Wolfgang,
There are 4 cpu's, and file# 65 block# 6041 is from the driving 
table of
the 5 table join (all NL joins). I will take a look at v$bh to see what
blocks from the other tables are in memory next time I run this. Aside from
this indirect approach, any other suggestions on confirming your plausible
hypothesis? Is there a way to breakdown the workload of individual cpu's?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Timestamps in trace files (and other trace file oddities)

If the sort requires disk you'll the direct write/reads in the trace. They 
do not count towards LIO and PIO as they are not using the buffer pool.

At 03:49 PM 7/24/2003 -0800, you wrote:

Another significant area of processing can be sorting. I don't know if 
sort processing is counted along with LIOs or PIOs.

Dan
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Timestamps in trace files (and other trace file oddities)

2003-07-28 Thread Wolfgang Breitling

Of course, anytime. Have fun with the CBO trace.

At 01:19 PM 7/28/2003 -0800, you wrote:
Wolfgang,

Thanks for the offer. You're lucky. I got to go to New Jersey for the last
three days. The trace I posted was my initial run with no attempt to tune. I
had just never seen such a large discrepency between the wait elapsed time
and the wall clock time in the trace file. The explanation posted by you and
by Cary makes sense.
One way to get a breakdown by cpu on AIX is via sar. I've been trying for
months, unsuccessfully, to get the SA to grant permissions to that command
(by default in AIX it is only granted to root).
Thanks for the offer to look at the 10053. Now that I've finally gotten the
chance to work on some of the fun stuff, I'd like to give it a whirl myself.
Is the offer still open if I run into trouble :)
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Inlist Iterator and NULLs

2003-07-29 Thread Wolfgang Breitling

Can you post the sql, or even the 10053 trace up to the GENERAL PLANS 
section. That would also answer the question which exact version/release 
of Oracle 9?

At 12:59 PM 7/29/2003 -0800, you wrote:
Is the INLIST ITERATOR unable to use the index unless we specify NOT NULL? 
I wanted to bounce this off the list before we log a TAR.

We are examining the performance of a query and I am trying to understand 
why an INLIST ITERATOR is not used if there is not an explicit IS NOT NULL 
predicate condition.

TableA.column1 is nullable, has null values and has a high number of 
distinct values. It also has a nonunique index with only column1.
The table and indexes are recently analyzed. The column has a histogram 
with 2 buckets.

In the query, the predicate for column1 is where column1 in ('value1', 
'value2'). Both of the values are literals and actual values do exist 
in the table.

If we use just this predicate (along with the other join conditions), the 
execution plan is a series of hash joins on full table scans (cost of 
38756/card of 3). If we add and column1 is not null, the execution plan 
is an INLIST ITERATOR with a series of nested loops using index range or 
unique scans.

In looking at a 10053 trace file, it becomes clear as to why the query is 
taking a bad plan. The cost of a single table access is radically different.

With Not Null (Note the tb_sel values (which seem to be the density * # of 
values) are correct in this computation)
SINGLE TABLE ACCESS PATH
  TABLE: SERIALS ORIG CDN: 3318658  ROUNDED CDN: 2  CMPTD CDN: 2
  Access path: tsc  Resc:  7137  Resp:  7137
  Access path: index (no sta/stp keys)
  Index: SERIALS_MAN_SER
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 19877
  IX_SEL:  1.e+00  TB_SEL:  6.0265e-07
  Access path: index (scan)
  Index: SERIALS_SER
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 5
  IX_SEL:  6.0277e-07  TB_SEL:  6.0265e-07
  Access path: index (equal)
  Index: SERIALS_SER
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 3
  IX_SEL:  3.0138e-07  TB_SEL:  3.0133e-07
  Access path: index (equal)
  Index: SERIALS_SER
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 3
  IX_SEL:  3.0138e-07  TB_SEL:  3.0133e-07
  BEST_CST: 5.00  PATH: 4  Degree:  1

Without Not Null (Note the tb_sel values are not correct. If I read this 
correctly, this is telling the CBO that there is a single value for each 
of the index columns)

SINGLE TABLE ACCESS PATH
  TABLE: SERIALS ORIG CDN: 3318658  ROUNDED CDN: 3318658  CMPTD CDN: 
3318658
  Access path: tsc  Resc:  7137  Resp:  7137
  Access path: index (no sta/stp keys)
  Index: SERIALS_EQ
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 13265
  IX_SEL:  1.e+00  TB_SEL:  1.e+00
  Access path: index (no sta/stp keys)
  Index: SERIALS_MAN_SER
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 19875
  IX_SEL:  1.e+00  TB_SEL:  1.e+00
  Access path: index (no sta/stp keys)
  Index: SERIALS_SER
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 12155
  IX_SEL:  1.e+00  TB_SEL:  1.e+00
  Access path: index (no sta/stp keys)
  Index: SERIALS_UC
  TABLE: SERIALS
  RSC_CPU: 0   RSC_IO: 7361
  IX_SEL:  1.e+00  TB_SEL:  1.e+00
  BEST_CST: 7137.00  PATH: 2  Degree:  1



The interesting thing is if I extract the access of this table to a single 
(non-joined) statement, it computes the cost and plan like I would expect. 
It is when we add in other tables and a join condition that it 'loses' its 
mind.

Thoughts? Need More Detail?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: optimizer parameters in Oracle 9.2

2003-07-30 Thread Wolfgang Breitling

Collecting system statistics in 9i is supposed to automagically do what 
optimizer_index_cost_adj does, at least if you set it according to the 
formula in Tim's paper (The Search for Intelligent Life in the Cost-Based 
Optimizer). The CBO then uses different cost factors for single-block IO 
(predominantly index accesses) and multiblock IO (table and index scans).

At 06:04 AM 7/30/2003 -0800, you wrote:
Hi all:

I have been reading on the optimizer_ parameters for
Oracle 9i - optimizer_max_permutation,
optimizer_index_caching and optimizer_index_cost_adj.
I have also been playing whith them and I can see that
they affect the explain plans for some queries quite
dramatically. Is there any guidelines for what they
should be set to for optimum performance (some kind of
range and may be relationship between the values) or
is this mostly a trial and error kind of thing? I have
seen a few discussion on the Oracle Metalink, but
mostly they are very specific  - try this value or try
that value. Does anyone have a link to a document with
something more systematic than that?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: rebuild index -initial extent - magic??!!

2003-07-31 Thread Wolfgang Breitling

Remember that initial_extent and next_extent in dba_indexes (and 
dba_tables) records what you requested in your storage clause - NOT what 
Oracle actually allocated. You need to look at dba_extents for tha.

At 02:34 AM 7/31/2003 -0800, you wrote:

If this is true:

The rebuild creates a new temporary segment that is the same size as the
required extents in the old index. If there is insufficient space to create
this temporary segment you get this error.
It doesn't reuse the existing space the index occupies but builds a second
identical index then renames and drops the old one.
Then rebuild will take longer as volume of data increases and more space 
will be required.  Where is the temp.. segment created?  In the old 
tablespace, the new tablespace (if you are moving it) or in memory or 
.in memory then ...??

I did the following in Oracle RDBMS 9i:

_ 

SQL alter index xsc_uk rebuild tablespace ax_le_small storage (initial 128K
  2  next 128K);
SQL select initial_extent,next_extent,index_name from dba_indexes
  2  where index_name like 'XSC%';
 65536  131072 XSC_CLNT_FK_I
 65536  131072 XSC_PK
131072  131072 XSC_UK
_ 

As I wish to use uniform extent sizing and I was given an import that does 
have that.  I am a little concerned about the initial extent changing - 
what if there is data in the index?  - how it could possibly deallocate 
space if you wish to have a smaller extent size.  It was very quick.  Did 
I really end up with new extents for XSC_UK each 128K
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Inlist Iterator and NULLs SOLVED

2003-07-31 Thread Wolfgang Breitling

Beacuse of the outer join, the optimizer had to ignore the inlist predicate 
and therefore the filter factor for the table became 1 (= all rows), 
manifested in TB_SEL 1..

At 06:29 AM 7/31/2003 -0800, you wrote:
Thanks to Wolfgang for spotting the problem. It was not the inlist 
iterator at all but an outer join! The NOT NULL predicate invalidated the 
outer join, so the optimizer was smart enough to make a different 
decision. I am still perplexed as to why the table access information was 
so radically different, but in light of the new finding, an explanation 
can be found.

The lesson learned here is to not to focus on a 'problem' until you fully 
understand the whole situation.

Daniel
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: HELP! Index Debate!

2003-07-31 Thread Wolfgang Breitling

Provided we are talking cost based optimizer, then the order of the 
predicates in the where clause does not matter - except under very rare 
conditions to break a tie. Nor is the order of the predicates in the where 
clause related to the order of the columns in the index. The only thing 
that matters is which columns of the index are present in the where clause 
and what is their relative position in the index. It's probably best 
explained with an example:

index on c1, c2, c3, c4, ...

where clause:
c1 = ... and c2 = ...   index can be used
c2 = ... and c1 = ...   index can be used
c1 = ... and c3 = ...   index can be used, but only c1 = portion of index 
since c2 is not in a predicate, here an index on c1, c3, c2, ... would be 
better, then both c1= and c3= can be used.
c2 = ... and ...index can not be used (not until Oracle 9); 
but index on c2, ... could be used
c1 = ... and c2 = ... and c3  ... and c4 = index can be used, but only 
the c1=, c2= and c3  portion since the inequality breaks the chain; an 
index on c1, c2, c4, c3 (or c2, c1, c4, c3 or c4, c1, c2, c3 etc ) could 
use all predicates on the index.

as long as the leading columns are present in the where clause with an 
equal predicate, the index can be used. The first omission or non-equal 
predicate breaks the chain and only the part of the index up to that column 
can be used.

Oracle is built around composite (or compound) indexes. Except for bitmap 
indexes it does not easily use more than one index for the same table 
access. The optimizer is slowly learning to use more than one index, but 
it's still rare.

And yes, you can determine how many and which predicates are used for the 
index access, but you need to run a CBO trace to find out.

At 12:34 PM 7/31/2003 -0800, you wrote:

Please help resolve this dispute.
We have a query that runs over 5 hours.  Sections of the query are
listed below.
The table QOH_DAY_FACT table had only on index and that was on the
TIME_ID column.  I propose adding an index with PROD_ID, WHS_ID, LOT_ID,
WHS_LOC, TIME_ID and QUALITY_ID.  My test shows the runtime was reduced
to about 1.5 hours.
The developer said all the columns in the index except PROD_ID were
being ignored.  He says there should be six separate indexes, one for
each column before Oracle will use them.
Also, does the order of the columns in the index have to match the order
of the columns in the WHERE CLAUS or is it more important to match the
WHERE CLAUS to the data content (least number of rows first)?
I will be running more tests, but I would like some input on this if
anyone has an opinion.
Thanks!
Ron
select T1.COUNTRY_NAME c1
  , T2.PRODTN_PROC_NAME c2
  , T2.PLANT_NAME c3
  , T1.WHS_NAME c4
  , T1.WHS_CMPLX_NAME c5
  , T3.WHS_LOC_NAME c6
  , T4.GRADE_DESC c7
  , T4.PACK_DESC c8
  , T5.FULL_DT c9
  , T6.QOH_MT c10...
from DWMART.DISTRIB_FCLTY_DIM T1
  , DWMART.MFG_FCLTY_DIM T2
  , DWMART.DISTRIB_LOC_DIM T3
  , DWMART.TIME_DIM T5
  , DWMART.QUALITY_DIM T7
  , DWMART.QOH_DAY_FACT T6
  , DWMART.PROD_DIM T4
where T6.PROD_ID=T4.PROD_ID(+)
   and T6.WHS_ID=T1.WHS_ID
   and T6.LOT_ID=T2.LOT_ID
   and T6.WHS_LOC_ID=T3.WHS_LOC_ID
   and T6.TIME_ID=T5.TIME_ID
   and T6.QUALITY_ID=T7.QUALITY_ID
order by c9 asc
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Smith, Ron L.
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 9i then 8i Installs

2003-08-01 Thread Wolfgang Breitling

I haven't actually done it in this order, but I don't see any reason why 
the order would matter as long as you install into separate homes. In case 
you do run into problems, I would take out the refreneces to the Oracle 9 
paths, including java from the path and classpath environment variables. 
Oracle 8i is using jre 1.1.7, Oracle 9.0 jre 1.1.8 and Oracle 9.2 jre 1.3.1 
and with the OUI being java, there could be issues if a newer jre is in the 
path.

At 12:34 PM 8/1/2003 -0800, you wrote:
Hello:

I got an odd request today to install 8.1.7 on an existing server which is
running 9.2 (Windows 2000).
Has anyone successfully been able to install Oracle 8.1.7 on the same
Windows Server that already has 9.2 running?
I know that if you install 8.1.7 then 9.2 both in different homes it will
work, but I have never seen it work where 9.2 was installed first.
I have not been able to locate a test box yet and was wondering if it is
possible and if so if there are any pitfalls I should watchout for, other
then the standard Windows stuff
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Multiple Datafiles and performance?

2003-08-08 Thread Wolfgang Breitling

The benefits of spreading the data over as many physical access paths ( ~ 
disks ) using multiple datafiles notwithstanding, there is always the case 
of too much. Keep in mind that at checkpoint time the DBWR need to visit 
the header of every ( non read-only ) datafile. That's unlikely to be an 
issue for a few dozen datafiles, but if you are getting into hundreds of 
them, keep that in mind. If you can get the striping done without multiple 
datafiles you get the best of both worlds.
I am just suffering that exact issue on a test system for an upgrade with 
an extremely poor IO subsystem where bottlenecks like this get magnified.

At 07:24 AM 8/7/2003 -0800, you wrote:
Gee, that question sounded a whole lot better when I wrote it yesterday 
than it did this morning when I saw it. :) Maybe I should be a little more 
vague.:)
The problem is there are a couple of things I am trying to accomplish. We 
have clients that use our application that have specific performance 
issues which I am working to improve. The other issue is to provide 
recommendation to development/tech staff on initial  setup  of 
database/tablespaces/datafiles etc.., along with hardware recommendations 
for our application.
So,  that being said, I'll try and ask better questions.

The environment is W2K, Oracle  8.1.7.2 or higher
All tablespaces are LMT
Most disk config's  are 1 (or 2)  Raid 1 along with a Raid 5 for basic 
systems.
Most operate application 24/7
Questions:

1) Is there any advantage to uniform datafile sizes?
2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G. 
(Other than time to recover from datafile loss)
It is probably safe to assume that the datafiles  exist on a RAID 5. (for 
now)
3) Why the recommendation to take a Win2k datafile to just over 2G?

For future apps I am pushing for optimal recommendations that go  for more 
raid 1 sets or raid 10 over the Raid 5. This should allow for more 
flexibility for spreading out the i/o.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Multiple Datafiles and performance?

2003-08-08 Thread Wolfgang Breitling

At 09:59 AM 8/7/2003 -0800, you wrote:
Hi!

 The benefits of spreading the data over as many physical access paths ( ~
 disks ) using multiple datafiles notwithstanding, there is always the case
 of too much. Keep in mind that at checkpoint time the DBWR need to visit
 the header of every ( non read-only ) datafile. That's unlikely to be an
The number of files had some impact in older Oracle versions (7.x).

Starting from 8.0 I believe, this issue is somewhat relieved, as you
probably know. Not all file headers are updated together and the update
doesn't have to go to disk immediately (this goes for checkpoints caused by
log switches).
Actually I didn't or else I wouldn't have made the point. I guess that is 
how myths start and get perpetuated: by no keeping up with changes.


Also, in older versions db_files parameter affected DBWR batch size and some
buffer cache structures as well, IIRC.
The biggest number of files I've had in a production database is about 1150,
960MB each. On WindowsNT4... =8´o
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: possible Bug in Oracle 9.2.0.2

2003-08-09 Thread Wolfgang Breitling

Can you please list select emplid, empl_rcd, effdt, effseq,  empl_status 
from ps_job where emplid = '3442'

At 03:34 PM 8/6/2003 -0800, you wrote:
While I am waiting for oracle support to respond to my tar update (2nd
callback) I am just wondering if anybody has found this problem.
We have the following select query (from a peoplesoft implementation)

SELECT a.emplid, a.effdt
FROM PS_JOB A
WHERE A.EFFDT = (SELECT MAX(A1.EFFDT)  FROM PS_JOB A1 WHERE A.EMPLID =
A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD  AND A1.EFFDT = SYSDATE)
AND A.EFFSEQ =  (SELECT MAX(A2.EFFSEQ)  FROM PS_JOB A2  WHERE A.EMPLID =
A2.EMPLID  AND A.EMPL_RCD = A2.EMPL_RCD  AND A.EFFDT = A2.EFFDT)
AND A.EMPL_STATUS = 'A'
and a.emplid='3442'
when we run the query we get one row back, but when we replace the field
names with count(*), the resulting answer back is 2.
We have tested it in 8.0.5.1.1 and we get the correct results, 1 row,
and a count of 1.
Darren


--
Darren Browett P.EngThis
message was transmitted
Data Administrator  using
100% recycled electrons
Information and Communication Technology
City of Coquitlam
P:(604)927 - 3614
E:[EMAIL PROTECTED]

---


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Browett, Darren
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Can't insert into partition

2003-08-10 Thread Wolfgang Breitling

NESTED LOOPS OUTER
  2 NESTED LOOPS OUTER
  2  HASH JOIN OUTER
   6412   HASH JOIN OUTER
   6412HASH JOIN
246 TABLE ACCESS FULL MARKETINGDIM
   6412 TABLE ACCESS FULL CURRJOB_STAGE
   3093VIEW
   3093 SORT GROUP BY
  13728  VIEW
  13728   SORT UNIQUE
  35929TABLE ACCESS FULL OFFERLOAD_STAGE
 47   VIEW
 47SORT UNIQUE
177 NESTED LOOPS
178  PARTITION RANGE ITERATOR PARTITION: KEY (null)
180   TABLE ACCESS FULL CURRJOBFACT PARTITION: KEY (null)
177  INDEX UNIQUE SCAN (object id 2941)
  2  TABLE ACCESS FULL PERIOD_STAGE
  0 VIEW
 55  HASH JOIN
110   VIEW
110SORT GROUP BY
 98 PARTITION RANGE ALL PARTITION: START=1 STOP=31
 98  TABLE ACCESS FULL WKLYJOBFACT PARTITION: START=1 STOP=31
32824864   PARTITION RANGE ALL PARTITION: START=1 STOP=31
32824864TABLE ACCESS FULL WKLYJOBFACT PARTITION: START=1 STOP=31
  1VIEW
   2324 SORT GROUP BY
   5908  TABLE ACCESS FULL CASHTXNFACT
-Original Message-
Sent: Thursday, August 07, 2003 10:55 AM
To: Multiple recipients of list ORACLE-L
But then it's not the same sql anymore and the access plan can be wildly
different. You need to use bind variables in your sqlplus session as well.
Unfortunately, even then it is not guaranteed that you'll get the same plan
as you get in the plsql proc.
At 06:44 AM 8/7/2003 -0800, you wrote:
Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL
standalone, we manually change these to literal variables.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, August 07, 2003 12:19 AM
To: Multiple recipients of list ORACLE-L


Is the sql you posted the exact sql as it is executed in the PLSQL
procedure, i.e. is the procedure using literals such as 2004 in the
predicates for sourcefiscalyear, or is it really using a bindvariable?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Can't insert into partition

But then it's not the same sql anymore and the access plan can be wildly 
different. You need to use bind variables in your sqlplus session as well. 
Unfortunately, even then it is not guaranteed that you'll get the same plan 
as you get in the plsql proc.

At 06:44 AM 8/7/2003 -0800, you wrote:
Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL
standalone, we manually change these to literal variables.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, August 07, 2003 12:19 AM
To: Multiple recipients of list ORACLE-L
Is the sql you posted the exact sql as it is executed in the PLSQL
procedure, i.e. is the procedure using literals such as 2004 in the
predicates for sourcefiscalyear, or is it really using a bindvariable?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Can't insert into partition

You are welcome. Happens a lot. You see what should be there rather than 
what IS there and wonder why it's not working as designed. Someone else, 
uninvolved,  comes along, takes one look at the thing, points out the error 
and leaves you (me) feeling like an idiot.

At 12:54 PM 8/8/2003 -0800, you wrote:
Wolfgang
   Thank you so much! You spotted something that we had overlooked! The
dot/comma was indeed the problem. And thanks to you and everyone else for
the help that helped narrow the problem down to this point.
   It seems that as you pointed out, the hint had a syntax error all along,
but CBO was making a good decision anyway for awhile, then for some reason
didn't make a good decision anymore.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Record breaking query

I know what the message says. Do you believe everything you see printed? Or 
expressing is differently: how many error/diagnostics messages have you 
seen that are more misleading than helpful.

At 01:24 PM 8/8/2003 -0800, you wrote:

but message says it is in seconds ... anf yes it is 92 ...

Thanks
Raj
 

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
From: Wolfgang Breitling 
[mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2003 4:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Record breaking query

Is that an Oracle 9 system and the time is in microseconds rather than
seconds?
At 10:39 AM 8/8/2003 -0800, you wrote:
Yeah, but think of the uptime!  One helluva MTBF on that server...

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA

-Original Message-
Sent: Friday, August 08, 2003 12:49 PM
To: Multiple recipients of list ORACLE-L


TICK : Fri Aug  8 09:06:03 2003
SEARCH in kdisti: tsn = 5, objd = 83525, rdba = 33588489
ORA-01555 caused by SQL statement below (Query Duration=1060347963 sec, 
SCN:
0x0011.05e003c2):
TICK : Fri Aug  8 09:06:03 2003
SELECT VOBJID, VNAME, VTEXT, VLEN, VOWNER, VOWNERID, VAUDIT, VCOMMENT,
VCNAME, PROPERTY, DEFER, FLAGS, OIDLEN, OIDCLAUSE, TYPEOWNER,
TYPENAME, UNDERLEN, 
UNDERCLAUSE  FROM   SYS.EXU8VEW  WHERE  VOWNERID
!= :SYS_B_0  ORDER  BY VLEVEL, VOWNER, VOBJID
according to this error message this query has been running for close to 33
years. appears to be a export running for 33 years.
I am clusless
Raj
--
Please see the official ORACLE-L FAQ: 
http://www.orafaq.nethttp://www.orafaq.net
--
Author: Jesse, Rich
   INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 
http://www.fatcity.comhttp://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.comhttp://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: 
http://www.orafaq.nethttp://www.orafaq.net
--
Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 
http://www.fatcity.comhttp://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: HELP! Index Debate!

, since 
db_file_multiblock_read_count is set to 32.

I'll also look for corroborating evidence in Tom Kyte's Expert one-on-one, 
but it's in the office right now.

At 06:19 AM 8/4/2003 -0800, you wrote:
Wolfgang,

 as long as the leading columns are present in the where clause with an
 equal predicate, the index can be used. The first omission or non-equal
 predicate breaks the chain and only the part of the index up to that
column
 can be used.
Could you please elaborate what you meant by that? My understanding (and
brief testing results) are that composite indexes can be used with
inequality predicates (, , between, like) and even a missing predicate in
between. Do you mean that normal index branch block traversing mechanism
can't be used starting with omitted or non-equal predicate and starting from
them leaf block linked list is read up to a value is found which doesn't
match the last equality predicate?


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: quick suggestions for tuning ?

At 06:59 AM 8/11/2003 -0800, you wrote:
Thanks a lot Connor.
Apart from bind vars.,LIO is a big issue here.
54 million consistent gets.
does it mean that db_cache_size (700M) is small ?
No, the buffer pool size has absolutely nothing to do with the number of 
logical IOs. Its size only determines how many of the LIOs end up as PIOs - 
the infamous buffer hit ratio. The only way to reduce the # of LIOs is 
through SQL tuning.

any good docs or links regarding LIO and how to deal with it ?

Can u explain me Connor ?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Record breaking query

Is that an Oracle 9 system and the time is in microseconds rather than 
seconds?

At 10:39 AM 8/8/2003 -0800, you wrote:
Yeah, but think of the uptime!  One helluva MTBF on that server...

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-Original Message-
Sent: Friday, August 08, 2003 12:49 PM
To: Multiple recipients of list ORACLE-L
TICK : Fri Aug  8 09:06:03 2003
SEARCH in kdisti: tsn = 5, objd = 83525, rdba = 33588489
ORA-01555 caused by SQL statement below (Query Duration=1060347963 sec, SCN:
0x0011.05e003c2):
TICK : Fri Aug  8 09:06:03 2003
SELECT VOBJID, VNAME, VTEXT, VLEN, VOWNER, VOWNERID, VAUDIT, VCOMMENT,
VCNAME, PROPERTY, DEFER, FLAGS, OIDLEN, OIDCLAUSE, TYPEOWNER,
TYPENAME, UNDERLEN, UNDERCLAUSE  FROM   SYS.EXU8VEW  WHERE  VOWNERID
!= :SYS_B_0  ORDER  BY VLEVEL, VOWNER, VOBJID
according to this error message this query has been running for close to 33
years. appears to be a export running for 33 years.
I am clusless
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Can't insert into partition

 ACCESS FULL MARKETINGDIM
 TABLE ACCESS FULL CURRJOB_STAGE
   VIEW

 Query Plan
 --
 --
 SORT GROUP BY
   VIEW
 SORT UNIQUE
   TABLE ACCESS FULL OFFERLOAD_STAGE
 VIEW
   SORT GROUP BY
 TABLE ACCESS FULL CASHTXNFACT
   VIEW
 SORT UNIQUE
   NESTED LOOPS
 PARTITION RANGE ITERATOR

 Query Plan
 --
 --
   TABLE ACCESS FULL CURRJOBFACT
 INDEX UNIQUE SCAN SYS_C00889
 TABLE ACCESS FULL PERIOD_STAGE
   VIEW
 HASH JOIN
   VIEW
 SORT GROUP BY
   PARTITION RANGE ALL
 TABLE ACCESS FULL WKLYJOBFACT
   PARTITION RANGE ALL
 TABLE ACCESS FULL WKLYJOBFACT

 33 rows selected.


 Table truncated.

 SQL
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Henry Poras
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Henry Poras
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send

Re: OCP Architecture question

2003-08-15 Thread Wolfgang Breitling

A lot of those multiple choice question are best approached from a reverse 
standpoint, much like a lot of my recent election experiences. You look at 
the choices and cross out the ones which are definitely not it until you 
whittled it down to the number of supposedly correct choices you need. If 
you don't like any of the choices you have to use those that you least not 
like.

At 05:24 PM 8/14/2003 -0800, you wrote:
Im using the self test software and here is a question... I dont like the 
answers. Please tell me if Im wrong.

Which Three methods can be used to avoid snapshot too old errors. This is 
for the 8i test.

1. User larger extents
2. Increase MAXEXTENTS for existing rollback segments
3. Create rollback segments with higher optimal values
4. Create rollback segments iwth high minextents
5. Run long queries when transaction processing is high.
Ruling out 5 is obvious. The test says its.

1,3,4

How does using large extents help this? What about a higher minextents value?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: NEXT_EXTENT and PCT_INCREASE

2003-08-27 Thread Wolfgang Breitling

Initial_extent and next_extent in DBA_TABLES show what you requested. 
Initial_extent and next_extent in DBA_EXTENTS show what Oracle actually 
allocated. You have an LMT with an 8M uniform extent. When you request 170M 
initial extent, that request is recorded in dba_tables and then Oracle will 
allocate 22 extents of 8M each to satisfy the 170M initial request and 
record that in dba_extents. That's the smallest to be equal or greater than 
the requested 170M.

At 03:44 PM 8/26/2003 -0800, you wrote:

Why the table was created with initial extent as 1700M,
but dba_extents says the first extent is 8M only?
SQL select initial_extent from dba_tables where table_name='BSIS';
1782579200
SQL select bytes from dba_extents where segment_name='BSIS' and extent_id=1;
   8388608
SQL select bytes,count(*) from dba_extents where segment_name='BSIS' 
group by bytes;
   8388608113
  67108864137

-Original Message-
Sent: Tuesday, August 26, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
Roger,

1. Space management can be specified for a tablespace, not a segment. Create
ASSM tablespace and alter table ... move there.
2. Locally managed tablespace, I guess. Oracle doesn't need NEXT_EXTENT and
PCT_INCREASE then.
HTH
Vadim
-Original Message-
Sent: Tuesday, August 26, 2003 3:59 PM
To: Multiple recipients of list ORACLE-L
Hi,

I have the following output when I querying dba_tables.

Question 1: This table is created using manual management method, right?
What do I do in order to turn it to Automatic segment-space
management?
Question 2: How come there are no values for NEXT_EXTENT and PCT_INCREASE?

Thanks,

Roger Xu

  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS
-- -- -- --
10 40  1255
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-- --- --- --- 
 527491072   1  2147483645
 FREELISTS FREELIST_GROUPS
-- ---
 1   1
LOGGING BACKED_UP   NUM_ROWS
--- - --
YES N  216122635
BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT
--  -- --
   7651115 4307319  0
AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
--- - ---
254  4496   2
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Roger Xu
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gorbounov,Vadim
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
For technical support please email [EMAIL PROTECTED] or you can
call (972)721-8257.
This email has been scanned for all viruses by the MessageLabs Email 
Security System.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Roger Xu
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE

Re: Listener problem on one client computer

2003-09-02 Thread Wolfgang Breitling

Is there a sqlnet.ora file on the PC that can connect? If so copy that as 
well. Maybe that pc is not even using the tnsnames.ora.

At 10:44 AM 9/2/2003 -0800, you wrote:

 TNSPING sends a message to some host on (in your case) port 1521 and finds
no program listening on this port and answering. There are several
possibilities :

   o You are addressing the wrong port. Check on which one you are
'talking' from the other machine,
   o Or you are prevented from opening a connection on port 1521 from your
machine

 HTH,
The ports are the same, I've checked the tnsnames.ora files and copied 
pasted the content of the box who work on the web server. Still same prob.
How can I check if i can open the port? It's a Win2000 server.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: latch free wait event

2003-09-04 Thread Wolfgang Breitling

The number of waits is irrelevant. What matters is the time waited which in 
your case shows 0, but I assume that is because you have not set 
timed_statistics to true. Without that the data from v$system_event are 
worthless. Cary will probably step in here and tell you that even with 
timed_statistics that data is at best of dubious worth.

At 02:49 PM 9/4/2003 -0800, you wrote:

System-wide Wait Analysis
 for current wait events
Average
Event Total  SecondsTotal  Wait
NameWaits  Waiting Timeouts  (in secs)
-   -   -   -
---
latch free1,4590 1,393  .000
After querying v$system_event my biggest concern is the latch free  wait
event. I understand that latch free is the process waits for a latch that
is currently busy ( held by another process).How can I drill down and
find the cause of this?   I have a feeling it is about rollback or redo
logs.
thanks,



David Ehresmann

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ehresmann, David
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: How to get the source code of the trigger without using

2003-09-05 Thread Wolfgang Breitling

set long 32656
col trigger_body for a80 word
select trigger_body from dba_triggers where owner='xxx' and trigger_name = 
'yyy';

At 07:34 AM 9/5/2003 -0800, you wrote:
Hi List,

Could you please help me to  get the source code of
the trigger without using any third party tools?
Thanks in advance,
Raj
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Oracle DBA
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: How to get the source code of the trigger without using

2003-09-05 Thread Wolfgang Breitling

Why is it far fetched and unnatural ? In my view that is the only way to 
be sure to get the correct source.

At 08:09 AM 9/5/2003 -0800, you wrote:
Well, the idea of extracting it from user_triggers or dba_triggers is a
little far fetched
and unnatural, but should work.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: 10g

2003-09-09 Thread Wolfgang Breitling

No, you get 3 wishes from the genie, after that you have to buy a licence 
for geniEE.

Come to think of it, maybe the g in Oracle 10g stands for genie rather than 
grid?

At 06:59 AM 9/9/2003 -0800, you wrote:
You're up to 3 wishes and then it will crash ...

I wonder if you have to rub the server or the disk box to get the genie ?

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: overloading and = comparisons in SQL

2003-09-09 Thread Wolfgang Breitling

It looks like it always converts a character column to a number (or date) 
before comparing, never the other way around:

SQL create table a ( N1 number );

Table created.

SQL create table b (c1 varchar2(50));

Table created.

SQL insert into a values (1000);

1 row created.

SQL insert into a values (2000);

1 row created.

SQL commit;

Commit complete.

SQL insert into b values ('1000');

1 row created.

SQL insert into b values('abcd');

1 row created.

SQL commit;

Commit complete.

SQL select * from a, b where a.n1 = b.c1;
select * from a, b where a.n1 = b.c1
*
ERROR at line 1:
ORA-01722: invalid number
=== the order in which the comparison is coded doesn't matter:

SQL select * from a, b where b.c1 = a.n1;
select * from a, b where b.c1 = a.n1
 *
ERROR at line 1:
ORA-01722: invalid number
=== but you can explicitly cast the number as a varchar2, then the 
comparison succeeds:

SQL select * from a, b where b.c1 = cast(a.n1 as varchar2(50));

N1 C1
-- --
  1000 1000
1 row selected.

=== or if you have other predicates which filter out offensive values 
before the comparison it works as well:

SQL select * from a, b where b.c1 = a.n1 and b.c1  'a';

N1 C1
-- --
  1000 1000
1 row selected.

The latter could be the reason the view sometimes works.

At 12:49 PM 9/9/2003 -0800, you wrote:
I don't know what to think re. this.

There is a view here that produces an error, I identified why -- in one AND
clause a number(9) datatype column is being joined with a varchar2(50)
datatype column.
The developer of this code says that this used to run, there must be
something wrong with the server.
I want to verify... Is there any kind of overloading invoked automatically
when Oracle compares columns of different datatypes?
i.e. if the varchar2(50) column only contains numbers, would Oracle convert
it automatically to number before making the comparison?
(My intuition says:  NO.  )
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: overloading and = comparisons in SQL

2003-09-09 Thread Wolfgang Breitling

Courtesy Julian Dyke ( http://www.juliandyke.com ):

SET SERVEROUTPUT ON

DECLARE
  err_msg VARCHAR2(120);
BEGIN
  dbms_output.enable (100);
  FOR err_num IN 1..10999
  LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
  dbms_output.put_line (err_msg);
END IF;
  END LOOP;
END;
/
At 01:59 PM 9/9/2003 -0800, you wrote:
where can i find a list of sqltrace events? seems that 10053 and 10046 are
well documented on hotsos. how many are there?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, September 09, 2003 5:39 PM
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Weird behavior with VARCHAR fields (was: ORA-01403 error,

SQL and PL/SQL are not the same until Oracle 9. There are distinct 
differences between the two in earlier releases.

I don't have an 8.0.3 version of Oracle but the following is the result 
from 8.1.7:

SQL create table wb (c1 char(8));

Table created.

SQL insert into wb values('abcd');

1 row created.

SQL insert into wb values('abcde');

1 row created.

SQL commit;

Commit complete.

SQL select * from wb where c1 = 'abcd';

C1

abcd
1 row selected.

SQL create or replace procedure wbp as
  2v_c1 char(8);
  3l_c1 varchar2(8) := 'abcd';
  4l_c2 varchar2(8) := 'abcd';
  5  begin
  6begin
  7  select c1 into v_c1 from wb where c1 = 'abcd';
  8  dbms_output.put_line('literal: '||v_c1);
  9exception
 10when no_data_found then null;
 11end;
 12begin
 13  select c1 into v_c1 from wb where c1 = l_c1;
 14  dbms_output.put_line('case1: '||v_c1);
 15exception
 16when no_data_found then null;
 17end;
 18begin
 19  select c1 into v_c1 from wb where c1 = l_c2;
 20  dbms_output.put_line('case2: '||v_c1);
 21exception
 22when no_data_found then null;
 23end;
 24* end;
 25  /
Procedure created.

SQL exec wbp;
literal: abcd
case2: abcd
PL/SQL procedure successfully completed.

The literal worked. So did the the varchar2 string padded to match the 
length of the char column.

Now this is the result if run on 9.2:

SQL create table wb (c1 char(8));

Table created.

SQL insert into wb values('abcd');

1 row created.

SQL insert into wb values('abcde');

1 row created.

SQL commit;

Commit complete.

SQL select * from wb where c1 = 'abcd';

C1

abcd
1 row selected.

SQL create or replace procedure wbp as
  2v_c1 char(8);
  3l_c1 varchar2(8) := 'abcd';
  4l_c2 varchar2(8) := 'abcd';
  5  begin
  6begin
  7  select c1 into v_c1 from wb where c1 = 'abcd';
  8  dbms_output.put_line('literal: '||v_c1);
  9exception
 10when no_data_found then null;
 11end;
 12begin
 13  select c1 into v_c1 from wb where c1 = l_c1;
 14  dbms_output.put_line('case1: '||v_c1);
 15exception
 16when no_data_found then null;
 17end;
 18begin
 19  select c1 into v_c1 from wb where c1 = l_c2;
 20  dbms_output.put_line('case2: '||v_c1);
 21exception
 22when no_data_found then null;
 23end;
 24* end;
 25  /
Procedure created.

SQL exec wbp;
literal: abcd
case1: abcd
PL/SQL procedure successfully completed.

The literal still works the same, but now the unpadded string finds the 
row, but the padded one doesn't.

Moral: It pays to test when upgrading software.

At 08:14 AM 9/10/2003 -0800, you wrote:

Yet I do not understand why it returns a row in SQLPlus and it 
does not in a stored procedure... The problem is now fixed, but I'd like 
to know the reason it won't work leaving it as it was. It also fails if I 
put a string instead of a variable in the stored procedure.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: how can I see which database I am logged into without

select sys_context('USERENV','DB_NAME') from anytable;

At 10:49 AM 9/10/2003 -0800, you wrote:
hi.

I think there was a dbms package to get some of
the environment variables for a session, but I can't
remember anyhting specific. If someone know what I'm
talking about and has any details, please Email me or
post here
thanks

Gene
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: index suppression and processing

In my understanding of the English language, in this context believe is 
in the same category as assume, meaning I don't really know. That is 
not good enough to base optimization actions on. You would just be shooting 
in the dark and hope that by sheer luck you hit the target. Better to find 
out where Oracle spends the time when executing the query by turning on 
sql_trace, or polling v$session_wait while it is executing and then go 
after the cause.

At 12:59 PM 9/10/2003 -0800, you wrote:

DELETE FROM TABLE_MESSAGES
WHERE   field1 = lrec_icclaims_dtl (i).seq_id AND
field2 = lrec_icclaims_dtl (i).seq_id AND
type = 'E';
The above code deletes processing messages from a table that may contain
upwards of
1,000,000 rows or more. We have a situation where we are inserting 100,000
rows per day into this table.  When the above code executes within a package
the whole process basically slows to a stop.  With the code commented out
the process runs. Field1 and Field2 are VARCHAR2(30).  seq_id is a
NUMBER(9).  We believe that Oracle is doing an implicit conversion of the
fields when the code is executed and causing the process to slow down
dramatically.  Without doing an explicit conversion with to_char (), because
I believe this will suppress the indexes on field1 and field2, what can be
done to make this efficient as possible?  Field1 is in 2 indexes and field2
is in 1 index and type is not included in any indexes.  Does Oracle suppress
the use of indexes when doing an implicit conversion? Can we force index use
with this statement:
delete /*+ INDEX(tablename indexname [indexname]) */   from
table_messages.
This is the first time we have seen this problem with this table.

thanks,

David Ehresmann
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Difference in Performance in two schemas in the same BD

 BY 
ACM_OFICINA,ACM_MONEDA )
  T  WHERE A.ACM_CODIGO = :b1  AND A.ACM_SUCURSAL = :b2  AND T.ACM_OFICINA =
  A.ACM_OFICINA  AND T.ACM_MONEDA = A.ACM_MONEDA  AND TRUNC(A.ACM_FECACUM) =
  T.MAXFECACUM  GROUP BY NVL(A.ACM_ACUMDBANT,0),NVL(A.ACM_ACUMCRANT,0),
  NVL(A.ACM_ACUMDB,0),NVL(A.ACM_ACUMCR,0)

call count   cpuelapsed   disk  querycurrent 
 rows
--- --   -- -- -- 
--  --
Parse1  0.00   0.01  2  3  1 
0
Execute  16971  7.41   7.41  0  0  0 
0
Fetch16971  1.78   1.89113  62981  0 
 5770
--- --   -- -- -- 
--  --
total33943  9.19   9.31115  62984  1 
 5770

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21  (FBDIN)   (recursive depth: 1)
Rows Row Source Operation
---  ---
   5849  SORT GROUP BY
   5849   NESTED LOOPS
  22820VIEW
  22820 SORT GROUP BY
   8688  TABLE ACCESS BY INDEX ROWID TCON_ACUM
  25659   INDEX RANGE SCAN (object id 11131)
   5849TABLE ACCESS BY INDEX ROWID TCON_ACUM
  14537 INDEX RANGE SCAN (object id 11131)
Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
   5849   SORT (GROUP BY)
   5849NESTED LOOPS
  22820 VIEW
  22820  SORT (GROUP BY)
   8688   TABLE ACCESS (BY INDEX ROWID) OF 'TCON_ACUM'
  25659INDEX (RANGE SCAN) OF 'CP01CON_ACM' (UNIQUE)
   5849 TABLE ACCESS (BY INDEX ROWID) OF 'TCON_ACUM'
  14537  INDEX (RANGE SCAN) OF 'CP01CON_ACM' (UNIQUE)
 

SELECT SUM(DECODE(T.TSA_TIPO,'D',NVL(T.TSA_VALOR,0))),SUM(DECODE(T.TSA_TIPO,
  'C',NVL(T.TSA_VALOR,0)))
FROM
 TCON_TRANSA T,TCON_DESTRAN D  WHERE T.TSA_SUCURSAL = :b1  AND 
T.TSA_CUENTA =
  :b2  AND D.DST_NUMTRAN = T.TSA_NUMTRAN  AND D.DST_SUCURSAL = 
T.TSA_SUCURSAL
   AND D.DST_FECHA BETWEEN :b3 AND :b4  AND D.DST_CUADRA = 'S'

call count   cpuelapsed   disk  querycurrent 
 rows
--- --   -- -- -- 
--  --
Parse1  0.03   0.03  5 94  2 
0
Execute  16971  6.79   6.90  0  0  0 
0
Fetch16971  8.09   8.56   5617 597373  0 
16971
--- --   -- -- -- 
--  --
total33943 14.91  15.49   5622 597467  2 
16971

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21  (FBDIN)   (recursive depth: 1)
Rows Row Source Operation
---  ---
  16971  SORT AGGREGATE
  0   NESTED LOOPS
 214942TABLE ACCESS BY INDEX ROWID TCON_TRANSA
 610884 INDEX RANGE SCAN (object id 11250)
  0TABLE ACCESS BY INDEX ROWID TCON_DESTRAN
 395942 INDEX UNIQUE SCAN (object id 11196)
Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  16971   SORT (AGGREGATE)
  0NESTED LOOPS
 214942 TABLE ACCESS (BY INDEX ROWID) OF 'TCON_TRANSA'
 610884  INDEX (RANGE SCAN) OF 'FK_CF02CON_TSA' (NON-UNIQUE)
  0 TABLE ACCESS (BY INDEX ROWID) OF 'TCON_DESTRAN'
 395942  INDEX (UNIQUE SCAN) OF 'CP01CON_DST' (UNIQUE)
 

TIA

Ramon E. Estevez
[EMAIL PROTECTED]
809-535-8994
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Difference in Performance in two schemas in the same BD