from:"Wolfgang Breitling"

On that I really, really have to disagree with you.

Jonathan's book is not something to read When you're really, really 
bored. You should read it when you're wide awake and eager to learn. Short 
of a database that's in pieces on the floor I can't think of anything that 
should have higher priority. And once you're done with it, continue with 
James (Morle's), Cary's, Steve's, Gaja's and Tom's books ( listed order is 
random ).

At 11:14 PM 1/7/2004, you wrote:
When you're really, really bored, you can read Practical Oracle 8i -
Building Efficient Databases,
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
  INET: [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: Problem with understanding Optimization methods.

Sorry, they are so engrained in my toolset that it didn't even occur to me 
that they could be unlnown.
My apologies also to Mladen. I didn't mean to admonish him, I just found 
the term boring in connection with Jonathan Lewis, or his book, inappropriate.

Jonathan Lewis: Practical Oracle 8i, ISBN 0-201-71584-8

James Morle: Scaling Oracle 8i, ISBN 0-2-1-32574-8

Cary Milsap, Jeff Holt: Optimizing Oracle Performance, ISBN 0-596-000527-x

Steve Adams: Oracle 8i Internal Services, ISBN 1-56592-598-x

Gaja Krishna Vaidyanatha, Kirtikumar Deshpande, John A. Kostelac Jr. : 
Oracle Performance Tuning 101 ISBN 0-07-213145-4

last but not least Tom Kyte:
Oracle Expert one-on-one, ISBN 1-861004-82-6 (that's the old Wrox 
book, the new edition after Wrox went out of buisness may have a different 
ISBN)
Effective Oracle by Design, ISBN 0-07-223065-7
Beginning Oracle Programming, ISBN 1-861006-90-x (co-authored with 
Sean Dillon and
 Christopher Beck)
At 01:34 AM 1/8/2004, you wrote:
Hear, hear!

Wolfgang,
Without wanting to appear really dense here. But, how about putting some
titles and surnames to that list of yours?
As much as I would love to buy books, with our exchange rate and import
taxes, it becomes very expensive!
But I do have a To Get list that I like to update.
regards
Denham
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: Should we stop analyzing?

And if it doesn't it's a documentation error. ;-)

At 12:09 PM 1/8/2004, you wrote:
Waddya mean, propaganda sheets?  We never release propaganda - everything 
always works the way we say it does!  :)

Pete

Controlling developers is like herding cats.
Kevin Loney, Oracle DBA Handbook
Oh no, it's not.  It's much harder than that!
Bruce Pihlamae, long-term Oracle DBA
--
Author: Pete Sharman
  INET: [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: Trigger Question

I'm sure I will be corrected if I'm wrong.
Answers inline
At 12:24 PM 1/8/2004, you wrote:

Hi All,

Firstly my apologies if this seems like a very *stupid* question but I'm a
tad confused (and it's late in the evening)
When an AFTER INSERT trigger is fired (row level) has the row been committed
to the database at this stage?
No. You could raise an error as part of what the trigger does in order to 
reject the action.

If so is it ok to call a package in the trigger that selects that row and
changes some values in the row?
a) it is not so and
b) you can not do anything with that row (or that table for that matter) in 
either the trigger or any called package or procedure. You'll get a 
mutating table error.


Thanks,

N.

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: Trigger Question

Of course. Silly me.

At 12:54 PM 1/8/2004, you wrote:
Wolfgang,

Yes you may, within the trigger only, change values of that row 
only.  it's known as

:new.column_name := whatever;

If so is it ok to call a package in the trigger that selects that row and
changes some values in the row?
a) it is not so and
b) you can not do anything with that row (or that table for that matter) in
either the trigger or any called package or procedure. You'll get a
mutating table error.
--
Author: Goulet, Dick
  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: table reorganizations

2004-01-07 Thread Wolfgang Breitling

SQL select owner, object_name from dba_objects
  2  where object_type like 'TABLE%' and status like 'NEEDS REORG%'
no rows selected

which obviously tells me that my system is fine. No reorgs required.

At 12:59 PM 1/7/2004, you wrote:
Lemme guess: you just started on your new job as a DBA? You are
another person to which can only wholeheartedly recommend Jonathan's book.
As for your questions, the answer is 42.
On 01/07/2004 02:39:26 PM, Shrake, Jolene wrote:
 What SQL statement do you use to identify tables that need
 reorganization?

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: table reorganizations

2004-01-07 Thread Wolfgang Breitling

And for us dylsexics it has always been 24

At 01:09 PM 1/7/2004, you wrote:
and are you sure it's not 57 now due to inflation?

--
Bill Shrek Thater ORACLE 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: HOTSOS Conference

So YOU beat me to it.
I'll be there as well. Registered for Steve's seminar as well.
At 03:44 AM 1/6/2004, you wrote:
I'll be there. I have the distinction (dubious or otherwise) of being
the first to register :)
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: HOTSOS Conference

Do you at least arrive before you leave?

At 06:09 AM 1/6/2004, you wrote:
I'll be there, trying to set a record for the longest
time on a plane by any attendee
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: Statspack wierd Output

Just go in the spreport.sql ($ORACLE_HOME/rdbms/admin) and change the time 
format for that output. On my W2K install it's at line 579 and then again 
line 635 for the full wait events list.

At 07:14 AM 1/6/2004, you wrote:
Hi

Statspack exceptionally showing the following on a particular day :-

Top 5 Wait Events
~ Wait %
Total
Event   Waits  Time (cs)
Wt Time
  
---
db file sequential read   100,106,503 
41.66
db file scattered read 15,134,519 
25.00
latch free  1,692,425 
16.67
buffer busy waits   2,067,006 
16.66
log file sync 543,5762,449,354
.00
  -
NOTE -
Statspack taken from a Production Database for a 1 hour period on Oracle
8.1.7.4 version
Application = Hybrid in nature , Banking s/w
4000 Concurrent Users connect to the Database
Qs What can be the cause of the same?
Qs Can anything be done about such field value Overflow i.e.
 ?
Will provide any info required

Thanks

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
  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: Statspack wierd Output

Alternate solution:
Don't run it for an hour. One would expect the accumulated wait times to be 
smaller then and not leading to the value overflow.

At 07:14 AM 1/6/2004, you wrote:
Statspack taken from a Production Database for a 1 hour period on Oracle
8.1.7.4 version
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: Top level heaps/subheaps

Welcome back. I was wondering where you've been the last couple of weeks.

At 11:19 AM 1/6/2004, you wrote:
As well as Arthur's books and Douglas's books.  Some of the Robert's, too. 
Personally,
I'd recommend Stranger In The Strange Land.
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: (long) Top level heaps/subheaps

Since we're both going to be in Dallas in March I'll have to have you write 
my name 1000 times ;-)

At 01:04 PM 1/6/2004, you wrote:
Yeah I know, did it again.
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: Should we stop analyzing?

2003-12-31 Thread Wolfgang Breitling

I didn't even notice.

As for the rest of your rebuttal. I am not a religious fanatic. If it works 
for you, great. Just be aware of the risk involved and backup the 
statistics before analyzing them so that you can restore them in case 
things go sour after the analyze.

I had one case for example where a developer had problems with a new sql. I 
wasn't at the office that day and the dba they called noticed that the 
statistics were several years old and decided that that must be the cause 
of the performance problem. Of course it wasn't (or else I wouldn't be 
using it, it actually turned out to be an Oracle bug that caused the 
session to terminate) but all the newly gathered statistics caused 
performance problems all over the place. Fortunately it was only a 
development database. I could have just copied the statistics from 
production, but I also have regular backups of the statistics (even though 
most don't change at all) and could easily restore the prior state.

At 12:09 AM 12/31/2003, you wrote:
Wolfgang,

First off, sorry for mangling your name in the previous post.
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: Should we stop analyzing?

2003-12-31 Thread Wolfgang Breitling

Yes, it does.

extract from 10053 trace:

** Executed dynamic sampling query:
level : 2
sample pct. : 11.151079
actual sample size : 2601
filtered sample card. : 2601
orig. card. : 11321
block cnt. : 278
max. sample block cnt. : 32
sample block cnt. : 31
ndv C3 : 12
scaled : 12.00
min. sel. est. : -1.
** Using dynamic sampling NDV estimates.
   Scaled NDVs using cardinality = 23325.
** Using dynamic sampling card. : 23325
It also tells you if it does NOT use the dynamic sampling results. Couldn't 
find an example right now.

At 07:59 AM 12/31/2003, you wrote:
Wolfgang,
I don't have 9i available at the moment so I can't test this. Just 
wondering if
a 10053 trace shows you if the statistics it  is using are gathered from 
dynamic
sampling.

Henry

-Original Message-
Wolfgang Breitling
Sent: Tuesday, December 30, 2003 6:24 PM
To: Multiple recipients of list ORACLE-L
The CBO will do dynamic sampling automatically provided the conditions are
met. The conditions that need to be met depend on the dynamic_sampling
initialization parameter in effect for the session. The default is 1 which
practically disables dynamic sampling. 0 will totally disable it but IMHO
the conditions for dynamic_sampling=1 are so rare (in practice) that one
can regard it as off.
BTW, even if the CBO goes to dynamic sampling that does not guarantee that
it will use the statistics it did gather this way.
At 03:24 PM 12/30/2003, you wrote:
Tanel,

I know the values, you are missing my question ... let me re-phrase it ...

1. To have CBO use dynamic sampling do you have to specify the hint?
or
2. CBO will do that automatically?

Just to let you know, Oracle 9ir2 docs main page is my home page on
Mozilla firebird browser and Metalink is my homepage on IE.
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-
Sent: Tuesday, December 30, 2003 2:44 PM
To: Multiple recipients of list ORACLE-L


Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling
parameter, you'll see descriptions for it's different values there.

Tanel.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
   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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Poras, Henry R.
  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

Re: Should we stop analyzing?

Now there's a thread from my heart. I have been saying and practicing 
(where I'm allowed to as a outside contractor) that for years. I am dead 
against regularly scheduled analyze jobs - it must be Sunday because the 
analyze is running - but it is sometimes hard to convince the resident 
DBAs of the futility and even outright danger of the practice.
In one system for which I was the DBA for several years most of the tables 
have not been analyzed sine May 2001 when the system was upgraded to 8i. 
Even the yearly partitions are not re-analyzed when they are split off the 
maxvalue partition. I just copy the statistics from a prior year partition. 
There are some tables where the histograms on certain columns need to be 
re-calculated every night because of an update that changes the data 
distribution completely ( the column values are ever increasing and the new 
most frequently occurring value is larger than the previous maximum value ).

For me the bottom line is
you need to know your system(s) and what is required, but don't just 
blindly analyze on a schedule for the sole purpose of keeping the stats 
up-to-date. If you analyze, there must be a (documented) reason for it and 
that reason must be tied to improving or preserving the response time of 
the application or parts of the application and not because it is the 
weekend and I have the time and resources to do it.

At 03:34 AM 12/30/2003, you wrote:

Friends,

I'd like to start a debate, which perhaps has already taken place, but if 
so I don't recall it: Should we stop analyzing tables and indexes?

Let me clarify:

I've always told people that using the 'monitoring' option (alter table X 
monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, 
because they would make sure that after a certain amound of data changes 
you got fresh stats (after, of course, using 
dbms_stats.gather_stale_statistics, etc. on the collected objects). We can 
always discuss whether the 10% threshold that gather_stale_statistics is 
based on is sound or not, but it can be as good as any other number. 
Except 42 :).

But then I listened to Dave Ensor at the UKOUG conference, and he said 
roughly this:

* Stop analyzing after the first analyze. It's the new stats that cause 
the optimizer to change execution plans.
* I know that big tables tend to stay big. Small tables stay small. 
Unique indexes stay unique and non-unique indexes stay non-unique...
* If the data changes A LOT you should of course re-analyze.

It made terrific sense in one respect to let the stats stay the same, thus 
letting the optimizer have access to the same information, thus choosing 
the same execution plan instead of changing it constantly. On the other 
hand it was irritating, because I had always beleived (and said) the 
opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, 
of course you shouldn't analyze all the time remark. Hrmf. So everybody 
else knew but me. Typical.

Looking back, I can recall several places where they analyzed every 
weekend, and on Monday the system could very well behave differently. 
Makes sense if the optimizer has some new/different information to consider.

On the other hand, it feels so intuitively right to constantly have 
up-to-date stats, doesn't it?

I'd like to know what practical and philosofical ideas you guys have on 
this topic.

Best regards - and Happy New Year,

Mogens

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
 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: help with estimate row count from asktom

v$sql_plan_statistics (and consequently v$sql_plan_statistics_all) only
have data to show if statistics_level is set to ALL. You can set that at
the session level.
Has anyone done measurements on a busy system to evaluate what the impact
is of setting that system-wide. The impression I have is that it is not
something I want to set in production all the time.

At 08:39 AM 12/30/2003, you wrote:
I have a very strict SLA and I posted a question on asktom about the best
way to get the 'estimate' of rows and return it to the user. Im getting
'no data found'. anyone have ideas? Im on 9.2, tables are analyzed, and Im
in a DBA account.

my question is at the bottom.

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
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: Should we stop analyzing?

The CBO will do dynamic sampling automatically provided the conditions are 
met. The conditions that need to be met depend on the dynamic_sampling 
initialization parameter in effect for the session. The default is 1 which 
practically disables dynamic sampling. 0 will totally disable it but IMHO 
the conditions for dynamic_sampling=1 are so rare (in practice) that one 
can regard it as off.
BTW, even if the CBO goes to dynamic sampling that does not guarantee that 
it will use the statistics it did gather this way.

At 03:24 PM 12/30/2003, you wrote:
Tanel,

I know the values, you are missing my question ... let me re-phrase it ...

1. To have CBO use dynamic sampling do you have to specify the hint?
or
2. CBO will do that automatically?
Just to let you know, Oracle 9ir2 docs main page is my home page on 
Mozilla firebird browser and Metalink is my homepage on IE.
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-
Sent: Tuesday, December 30, 2003 2:44 PM
To: Multiple recipients of list ORACLE-L
Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling
parameter, you'll see descriptions for it's different values there.
Tanel.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
  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: Should we stop analyzing?

At 03:29 PM 12/30/2003, you wrote:
But then again, if re-collecting statistics causes your database performance
to suddenly become very bad, it seems at first cut there are only two 
conclusions
you can come to.

1)  CBO is broke if fresh statistics result in poor performance
That a plan changes due to changes in the statistics doesn't mean that the 
CBO is broke. That's the whole name of the game. The optimizer uses 
statistics - together with initialization parameters,  heuristics and rules 
- to develop the anticipated best access path. If you change any of these, 
statistics by analyzing, initialization parameters by changes to the 
init.ora, or heuristics and rule by upgrading to a new version or applying 
a patch. I regard any of these changes as serious changes to the database 
which should go through a test and acceptance cycle. And that includes 
refreshing statistics. I am constantly amazed how nonchalantly most shops 
schedule daily, weekly, or whatever analyze jobs even if they batten down 
the hatches against changes to the application (Don Burleson alluded to 
that as well). Most of the time the changed statistics do not cause a 
change in access plans ( which immediately begs the question why do it then 
), but ever so often the changed statistics cross a threshold to make a 
different plan appears to be better. It may be better, or it may turn out 
to be horrible. My point is: shouldn't that be tested first?

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: Should we stop analyzing?

Note inline

At 10:29 PM 12/30/2003, you wrote:

If my data changes, and I analyze it, CBO should still find
reasonable execution paths for the current data.
If the CBO were infallable we wouldn't have this discussion. There are many 
reasons why even the most up-to-date statistics can lead to less than 
optimal access plans. My point is not necessarily with the frequency of 
statistics gathering but with the untested activation of new statistics, 
which is the hallmark of scheduled analyze jobs, as it carries the same 
risk as any untested change.


If my data does not change, and I analyze it, CBO should have
the same set of statistics as it did previously.
If your data didn't change, or didn't change enough to make a difference in 
access plans, wouldn't you agree that the exercise of gathering statistics 
was futile and useless.


Is that not true, or is there some other piece missing here?
If the current statistics produce access plans that render the required 
data in the time stipulated by your SLAs, why the urge to change something. 
You are getting dangerously close to symptoms of CTD.
If, on the other hand, there are performance problems, they should be 
analyzed case by case and at that time the possibility that newer 
statistics will change the access plan and improve the performance should 
be explored.


Jared

--
Author: Jared Still
  INET: [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: A performance problem

2003-12-29 Thread Wolfgang Breitling

 the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Potluri, Venu (CT Appl Suppt)
  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: Who are certified Oracle Masters?

2003-12-14 Thread Wolfgang Breitling

Honi soit qui mal y pense

At 04:34 PM 12/13/2003, you wrote:
This is a family oriented mailing list folks.  :)

Jared

On Fri, 2003-12-12 at 15:19, Richard Foote wrote:
 Hi Jeremiah,

 I find the mental image of the six of you holding up your shafts for a
 publicity shot profoundly disturbing...

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: analyze problems

2003-12-11 Thread Wolfgang Breitling

Not that he needs it, but I can confirm Jonathan's claim that the 
method_opt clause you are using does not collect column statistics:

SQL @delete_table_stats tp1

PL/SQL procedure successfully completed.

SQL @tblstats tp1

avg
TABLE_NAME free used  fl 
log rows   blks   emptyrow px LAST_ANAL pool G U
--   --- ---  -- 
--- -- -- -  - -
TP1 
1   DEFAULT  N N
TP1.P1 (1)   10   40   1 
YES DEFAULT  N N
TP1.P2 (2)   10   40   1 
YES DEFAULT  N N
TP1.P3 (3)   10   40   1 
YES DEFAULT  N N

4 rows selected.

SQL @colstats tp1

tablecolumn   NDV  density 
nulls lo hi  bkts
 - --  
--- -- -- -
TP1  N1
TP1  N2
TP1  N3
TP1  C1
TP1  C2
TP1  C3
TP1  D1
TP1  D2
TP1  D3
TP1  L

10 rows selected.

SQL exec DBMS_STATS.GATHER_TABLE_STATS (ownname = 'SCOTT', tabname = 
'TP1', method_opt = 'FOR COLUMNS', cascade = TRUE);

PL/SQL procedure successfully completed.

SQL @tblstats tp1

avg
TABLE_NAME free used  fl 
log rows   blks   emptyrow px LAST_ANAL pool G U
--   --- ---  -- 
--- -- -- -  - -
TP125,000  8,402 
   0100  1 11-DEC-03 DEFAULT  Y N
TP1.P1 (1)   10   40   1 
YES2,490836   010011-DEC-03 DEFAULT  Y N
TP1.P2 (2)   10   40   1 
YES2,489852   010011-DEC-03 DEFAULT  Y N
TP1.P3 (3)   10   40   1 
YES   20,021  6,714   010011-DEC-03 DEFAULT  Y N

4 rows selected.

SQL @colstats tp1

tablecolumn   NDV  density 
nulls lo hi  bkts
 - --  
--- -- -- -
TP1  N1
TP1  N2
TP1  N3
TP1  C1
TP1  C2
TP1  C3
TP1  D1
TP1  D2
TP1  D3
TP1  L

10 rows selected.

SQL

If you are seeing column statistics then they are old.
As to your original questions
Are there any known do and don'ts concerning dbms_stats which might 
explain this?
Nothing specific to dbms_stats, just the general advice: DON'T believe 
everything a self-proclaimed Oracle-Guru or consultant tells you. DO your 
own homework and due diligence. TEST what you are doing or planning to do. 
If you insist in painting all tables in your schemas with the same brush 
then at least just gather basic column statistics (num_distinct, min, max, 
nulls) by leaving the default method_opt alone. Afterwards you can collect 
histograms on select columns. In my opinion for all indexed columns is 
both too broad and too narrow - not all indexed column need or even should 
have histograms and some non-indexed columns could benefit from a histogram.

Is it better to stay on analyze table ?
No
Can I expect lot's of problems in execute plans when migrating?
Yes
At 03:44 AM 12/11/2003, you wrote:
Hi Jonathan,

Can you please elaborate on this 75 buckets issue.
I had an advice from an oracle consultant to implement analyzing
Like this. BTW column statistics are there but it makes no difference
In plans. I also added optimizer_index_caching=90 and
Optimizer_index_cost_adj=40 both also without effect on my testcase
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

Re: Who are certified Oracle Masters?

2003-12-11 Thread Wolfgang Breitling

Thanks for the vote of confidence, but I'm not an OCM.

In order to pass, I am lacking a few vital pieces (in increasing severity)

a) $$
b) time
c) knowledge
My knowledge and interest is rather limited to tuning and the CBO.

I believe Tanel is, according to credentials listed on the slides of his 
10g presentation.

At 01:54 PM 12/11/2003, you wrote:
I know only of Pete Sharman. Who are other Oracle Certified Masters on
this group? I suspect Tanel to be one, as well as Steve Adams, Cary Millsap,
Mogens Norgaard, Anjo Kolk, Wolfgang Breitling, Gaja V. and Kirti Deshpande.
Am I correct?
Mladen Gogala
Oracle 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: FW: raw traces - EXEC: c=10,000 e=40

2003-12-03 Thread Wolfgang Breitling

Thanks for the clarification.

At 10:29 AM 12/3/2003, you wrote:
Wolfgang,

The OS has always provided microsecond data to the Oracle kernel (see
the 'man gettimeofday' and 'man getrusage'). It's only in release 9 that
the Oracle kernel stopped truncating the data at the centisecond digit
(by doing an integer division of 1).
Oracle gets elapsed times by comparing pairs of gettimeofday() calls
(truss to find out for yourself). The e=40us is actually accurate to
within +/-1us (not counting measurement intrusion effect). The c value
is potentially way off, as I explained in the other note.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
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

2003-12-03 Thread Wolfgang Breitling

Maybe I didn't make my point clear enough. If you use 
dbms_stats.gather_table_stats with  method_opt=''for all indexed columns 
size 2' , i.e. any other than size 1, dbms_stats in Oracle 8i will  issue 
an analyze ...  command to gather the statistics. Run a sql_trace if you 
don't believe me. Therefore the results of gathering statistics with 
dbms_stats are no different than gathering them with the equivalent analyze 
command.
It is different if you use the default method_opt (for all columns size 1). 
Then gather_table_stats uses its own sql to collect table and column ( 
num_distinct, min, max, null, avg_col_length) statistics. For index 
statistics always resorts to the analyze command.

That all changes in Oracle 9i. There the gather procedures do their own 
work and do not use analyze anymore.

How did you determine the bucket size of 2?

And no, I have not had any issues with analyze for partitioned tables; 
however, I do not gather histograms blindly on all indexed columns. Only on 
a few columns with highly skewed data content and some of them are on 
partitioned tabled.

At 09:29 PM 12/3/2003, you wrote:
The reason why we switched from 'analyze table .. 10 percent' is because 
when we partitioned some of the huge tables, the query performance against 
these tables was really bad. 'Gather_table_stats' with size 2 on indexed 
columns did a much better job.

Have you had issues with 'analyze' against partitioned tables?

-Original Message-
Wolfgang Breitling
Sent: Wednesday, December 03, 2003 5:29 PM
To: Multiple recipients of list ORACLE-L
In Oracle 8i you may as well stick with analyze since the dbms_stats call
you use translates simply into a
   analyze table ... ESTIMATE statistics sample 10 percent FOR TABLE FOR
ALL INDEXES for all indexed columns size 2
Why did you go from a simple analyze to gathering histograms on all indexed
columns? I question the rationale of gathering histograms of size 2.
Aside from that, I question the rationale of a blanket histogram gathering
(regardless of # of buckets) on all indexed or all columns. Histograms
are like medicine. In the right (i.e. sparing dose) they are a therapeutic
tool. In the wrong, especially too high dose, they become poison.
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: raw traces - EXEC: c=10,000 e=40

In Oracle 9, Oracle tries to measure times in microseconds (as opposed to 
centiseconds pre-9). However, many Systems only slice time far coarser than 
that so Oracle has to fake it to some degree and that faking may be 
different between cpu time and elapsed time. Take your cpu times for 
example. They are all 1 microseconds which is exactly 1/100 = 
0.01 = 1 centiseconds. That's a bit too much of a coincidence for me. Who 
knows where Oracle get the elapsed times from. I wouldn't put too much 
faith in the accuracy of an purported elapsed time of 40 microseconds. 
Unless we are getting at least into the milliseconds range I would regard 
all elapsed times as rounding errors.

At 02:09 PM 12/2/2003, you wrote:
Hi!

I haven't read Cary's book yet (although it's already waiting on my
bookshelf), but I think CPU time c is measured in timeslice steps (100ms)
and elapsed time e is taken from system timer or smth like that. Others will
know better :)
Tanel.

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:49 PM
 Reading Cary's book I understand that c and e are
 measured via different system calls (haven't truss'ed
 [well tusc'ed] them yet - I am on HP-UX 11.11), but
 would anybody know what the reasonable upper limit of
 c-e might be?

 I am looking at the trace file where c is more than
 two orders of magnitude greater than e, which make me
 wonder if I a have some anomaly on my system

 Some examples:

 EXEC
 #98:c=1,e=433,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1777312113968

 EXEC
 #110:c=1,e=390,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1777312181650

 PARSE
 #103:c=1,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1777314524922

 Oracle 9.2.0.4.0 on HP-UX 11.11

 Thanks,
 Boris Dali.

 __
 Post your free ad now! http://personals.yahoo.ca
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Boris Dali
   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: Tanel Poder
  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: Copying stats between/amongst schemas

LOGGING   : YES
BACKED_UP : N
NUM_ROWS  : 16280
BLOCKS: 376
EMPTY_BLOCKS  : 0
AVG_SPACE : 0
CHAIN_CNT : 0
AVG_ROW_LEN   : 78
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS   : 0
DEGREE:  1
INSTANCES :  1
CACHE : N
TABLE_LOCK: ENABLED
SAMPLE_SIZE   : 16280
LAST_ANALYZED : 07-sep-2003 11:07:01
PARTITIONED   : NO
IOT_TYPE  :
TEMPORARY : N
SECONDARY : N
NESTED: NO
BUFFER_POOL   : DEFAULT
ROW_MOVEMENT  : DISABLED
GLOBAL_STATS  : YES
USER_STATS: NO
DURATION  :
SKIP_CORRUPT  : DISABLED
MONITORING: NO
CLUSTER_OWNER :
DEPENDENCIES  : DISABLED
-

PL/SQL procedure successfully completed.

At 07:09 PM 12/2/2003, you wrote:
IIRC, you can do all of it from one session.  Let's say you're copying
stats from 'SOURCE_USER' to 'DEST_USER' while logged in as ADAWDOA (a
DBA account which doesn't own anything ;-)), who owns a statistics
table called XFER_STATS.
exec dbms_stats.export_schema_stats('SOURCE_USER','XFER_STATS',null,
user)
exec dbms_stats.import_schema_stats('DEST_USER','XFER_STATS',null,user)
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 on tkprof output

I'm not so sure. The query returns no rows and the second to last nested 
loop already has only 1 row in the resultset. I'd try to determine what the 
most limiting condition is - or set of conditions - those that eliminate 
most rows early on and make sure the optimizer starts with that.

I could be mistaken, but the query appears odd. Isn't the condition and 
p.business_country_id in ( select countryabbrev from c ) 
nonsensical/superfluous in light of the condition and 
p.business_country_id=c.countryabbrev ?

 select countryname, e.lastupdatedate
from e e, p p, c c
where p.pid = e.pid
and p.hsbc_user_category='GIB'
and p.business_country_id=c.countryabbrev
and e.userstatusid in ( select userstatusid from userstatus )
and p.business_country_id in ( select countryabbrev from c )
order by countryname, e.lastupdatedate desc
At 06:59 PM 12/2/2003, you wrote:
Hi,
It is spending a lot of time waiting for IO and something like that.
If you want to see what is the session waiting for ,just do:
alter session set timed_statistics = true; (ignore it if it is 
already true)
alter session set events '10046 trace name context forever,level 8';
--do your sql here.
find the trace file and tkprof(use oracle 9.2 tkprof if your oracle 
version is not 9.2, not sure 9.0 will work)it like:
tkprof file=your_tracefile waits=y

For your SQL, I think more hash_join should be used instead of nested 
loop. Try it.

regards
Zhu Chao
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: Copying stats between/amongst schemas

What about export (without data), ensuring calculated statistics are 
included in the export, and then import to the new schema. That should be 
just as viable and sanctioned. Of course, Oracle 8 has some serious 
limitations which may prevent it from exporting calculated statistics ( the 
presence of unique or primary key constraints for example ), most of which 
Oracle 9 fortunately has eliminated.

At 04:24 PM 12/2/2003, you wrote:
dbms_stats is the only sanctioned way to do it.

Orr, Steve wrote:

1 database instance, 2 nearly identical schemas. What's the best 
sanctioned way to copy stats, (including histograms), from one schema to 
another?


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
 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: bad SQL day...help please

2003-11-27 Thread Wolfgang Breitling

I have had good success with the minus operator:

select ob_oid, sku, qty from tbl where transact in ('RPCK','PICK')
minus
select ob_oid, sku, qty from tbl where transact = 'SHIP'
At 12:14 PM 11/27/2003, you wrote:
List,

Please excuse the content of this question. I haven't had a breakthrough
yet so I'm hoping for some assistance... it may seem trivial to some but
for some reason I am SQL-ly challenged today.
I have a table which holds historical transaction records. Each PICK or
RPCK record should have a corresponding SHIP record with a match on
quantity, sku, and order_id. I have to create an exception report where
if for any PICK/RPCK record there isn't a corresponding SHIP record, I
should be shown the PICK/RPCK record. In other words, each sku has
records in multiples of 2 - i.e. 1 PICK/RPCK, then 1 SHIP record; 2
PICK/RPCK records, then 2 SHIP records.
I know what I want in English, but I'm having trouble designing the
query in SQL. In the table below, you can see that SKU 117127 has a PICK
record but no SHIP record, same case for SKU 701206.
Is someone kind enough to offer me some SQL advice?

Thanks in advance,
Saira
OB_OID  SKU TRANSACTQTY
50340   115227  RPCK36
50340   115227  SHIP36
50340   115304  RPCK36
50340   115304  SHIP36
50340   174040  RPCK12
50340   174040  SHIP12
50340   177127  PICK36
50340   177144  PICK24
50340   177144  SHIP24
50340   177624  PICK24
50340   177624  SHIP24
50340   177634  PICK48
50340   177634  SHIP48
50340   19  PICK20
50340   19  SHIP20
50340   20020   RPCK6
50340   20020   SHIP6
50340   701079  PICK100
50340   701079  SHIP100
50340   701206  RPCK30


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saira Somani-Mendelin
  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: Using miss-spelled hint changes explain plan ...

2003-11-20 Thread Wolfgang Breitling

Me wonders if the optimizer ignores all the hints after the first 
misspelled/malformed one. What happens if you change the order of the hints:

FROM (SELECT /*+ INDEX(C PF_EVENTS_N2) INDEX(G PF_ACCOUNTS_U2)  ORDERED 
driving_site(a) */

and

FROM (SELECT /*+ INDEX(C PF_EVENTS_N2) INDEX(G PF_ACCOUNTS_U2)  ORDERED 
diving_site(a) */

At 09:49 AM 11/20/2003, you wrote:
Thanks Rob,

me thought the very same until I ran it through ...
here is my script file ...
[snip]

FROM (SELECT /*+ driving_site(a) INDEX(C PF_EVENTS_N2) INDEX(G 
PF_ACCOUNTS_U2)  ORDERED */
[snip]

FROM (SELECT /*+ diving_site(a) INDEX(C PF_EVENTS_N2) INDEX(G 
PF_ACCOUNTS_U2)  ORDERED */
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: Using miss-spelled hint changes explain plan ...

2003-11-20 Thread Wolfgang Breitling

Maybe not so much an undocumented feature than documentation that is open 
to interpretation. It is documented that the optimizer will ignore 
malformed hints. It is just not made clear that everything after that 
malformed hint up to the end of the comment is ignored as well.

BTW, you mis-spelled miss-spelled  :-)

At 01:05 PM 11/20/2003, you wrote:
BINGO !!

Thanks Wolfgang  if I put the misspelled hint at the end, it is 
ignored ... all plans look the same then.
Another (probably) undocumented feature.
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: col_usage$ question

2003-11-18 Thread Wolfgang Breitling

That is a new table in Oracle 9 and is used by Oracle to track what columns 
are used in predicates. At present the only use of that information that I 
am aware of is in the procedure dbms_stats.gather_table_stats ( ..., 
method_opt = 'for columns ... size auto');

At 09:29 AM 11/18/2003, you wrote:

Does anyone know what this table (sys.col_usage$) is used for? To me it 
sounds like something that CBO might appreciate ... but any ideas? It is 
referenced by dbms_stats and dbms_stats_internal packages ...

Thanks in advance
Raj
 

Rajendra dot Jamadagni at nospamespn dot com
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: col_usage$ question

2003-11-18 Thread Wolfgang Breitling

Absolutely

At 01:39 PM 11/18/2003, you wrote:
Could the column info be used to 'recommend' indexing?

Daniel Fink

Tanel Poder wrote:
Hi! If you describe this table then you see that this table stores column 
usage information in filter and join predicates for database objects. 
From describe, you see there are several filter and join conditions 
tracked for an object's (obj#) columns (intcol#). You can join them to 
col$ table for example. During shutdown, the session executing shutdown, 
writes the column usage statistics to col_usage$. During normal 
operations, it's SMON who's doing that over regular intervals. You can 
disable collecting these statistics by setting _column_tracking_level to 
0. I don't really see where CBO could use those statistics for speeding 
up statement execution, because during execution CBO knows all the 
predicates  statement structure anyway. But it is probably useful for 
various 10g's advisories, which can make you recommendations based on how 
the tables (columns) are used. Also, it might help automatic statistics 
gathering to determine which stats need to be updated or not (this gather 
stale stuff). Tanel.
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: col_usage$ question

2003-11-18 Thread Wolfgang Breitling

At 11:04 AM 11/18/2003, you wrote:
Hi!

If you describe this table then you see that this table stores column 
usage information in filter and join predicates for database objects. From 
describe, you see there are several filter and join conditions tracked for 
an object's (obj#) columns (intcol#). You can join them to col$ table for 
example.

During shutdown, the session executing shutdown, writes the column usage 
statistics to col_usage$. During normal operations, it's SMON who's doing 
that over regular intervals.

You can disable collecting these statistics by setting 
_column_tracking_level to 0.

I don't really see where CBO could use those statistics for speeding up 
statement execution, because during execution CBO knows all the predicates 
 statement structure anyway. But it is probably useful for various 10g's 
advisories, which can make you recommendations based on how the tables 
(columns) are used. Also, it might help automatic statistics gathering to 
determine which stats need to be updated or not (this gather stale stuff).
Which statistics may be stale is tracked by SYS.MON_MODS$. col_usage$ 
tracks the use of columns as predicates, not any updates. It is currently 
(Oracle 9) used to decide if i might be worth gathering histogram 
information for a column - together with the determination if the data in 
the column is sufficiently skewed. No point in gathering histograms on 
non-skewed data or on data that is never referenced in a predicate.
I can imagine that Oracle 10 will use that data to recommend indexes - as 
Daniel suggested.



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: granting SELECT privilege on SYS.X$ TABLES

2003-11-15 Thread Wolfgang Breitling

Someone must have created sys.x_$ views on some of the sys.x$ tables. 
Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, 
X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of 
the x$ tables as well. I routinely do it for all x$ tables in my test 
databases and grant select to the select_catalog_role. Then I can access 
the x$tables without having to log on as sys.

In my test databases I always
At 07:14 PM 11/14/2003, you wrote:
P.S. I forgot to mention that in all the databases (including the 8.1.7 
databases) in which I tried this, init parameter 
O7_DICTIONARY_ACCESSIBILITY was set to FALSE.

I always thought that one could not grant SELECT privilege on the SYS.X$ 
tables, and to make them accessible to another user one would have to 
create a view on the table (as mentioned on Steve Adams' ixora website:
http://www.ixora.com.au/scripts/prereq.htm
create_xviews.sql)

However someone told me recently that you could grant SELECT on sys.X_$...

When I tried this, I saw results that confused me.
In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to
1- grant select on SYS.X_$KTFBFE to another_user ;
2- grant select on SYS.X_$KTFBHC to another_user ;
3- grant select on SYS.X_$KTFBUE to another_user ;
BUT

4- grant select on SYS.X_$KDXST to another_user ;
returns ORA-00942 table or view does not exist.
In database B, using the same ORACLE_HOME as database A (i.e. identical 
Oracle version and OS)
even the first three grant statements returned ORA-00942

When I tried it on more recent Oracle databases on Windows / SunOS 
servers, it worked intermittently:
Oracle 9.0 (SunOS): all GRANTS failed
Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed
Oracle 10.1 beta (Windows 2000): all GRANTS failed

Does anyone know the reason for this strange behaviour?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
  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: RE: Index behavior

Actually, it has nothing to do with any of the table or index statistics.
OK, almost nothing. I suppose if Jonathan (Lewis) can get the optimizer to 
do a FTS on an umpteen billion row table to retrieve a single row by its 
prime key, one can concoct a scenario of statistics values, aided by init 
or session parameters, that would cause the CBO to use a full table scan to 
resolve where name like 'AB%.
Back to the topic. I did a test and the situation is easily reproduceable. 
What happens is that as the like comparison string gets short, the 
selectivity of the predicate decreases ( if you look at the 10053 trace, 
the TBSEL value increases but that is the same paradoxon as with 
performance: if something gets faster, did its performance decrease? ) as 
one would expect. The TBSEL selectivity value and the rate of its increase 
depends on the length of the like comparison string and the average column 
length. When it gets down to the transition from ABC% to AB%, that trend 
breaks sharply and suddenly the selectivity increases by orders of 
magnitude ( TBSEL decreases by a huge factor ). for like A% it decreases 
again, but is still lower (depends on avg col length) than the selectivity 
of like ABC%.
You can see that in the following test. The cardinality reflects the 
changes in the tbsel value (cardinality = tbsel * num_rows, which was 
10,000 for the test).

select id from sam where name like 'ABCDEFGHI%';
 card operation
- --
1 SELECT STATEMENT
1   TABLE ACCESS BY INDEX ROWID SAM
1 INDEX RANGE SCAN SAM_IX
select id from sam where name like 'ABCDEFGH%';
 card operation
- ---
1 SELECT STATEMENT
1   TABLE ACCESS BY INDEX ROWID SAM
1 INDEX RANGE SCAN SAM_IX
select id from sam where name like 'ABCDEFG%';
 card operation
- ---
   57 SELECT STATEMENT
   57   TABLE ACCESS FULL SAM
select id from sam where name like 'ABCDEF%';
 card operation
- ---
  100 SELECT STATEMENT
  100   TABLE ACCESS FULL SAM
select id from sam where name like 'ABCDE%';
 card operation
- ---
  178 SELECT STATEMENT
  178   TABLE ACCESS FULL SAM
select id from sam where name like 'ABCD%';
 card operation
- ---
  317 SELECT STATEMENT
  317   TABLE ACCESS FULL SAM
select id from sam where name like 'ABC%';
 card operation
- ---
  563 SELECT STATEMENT
  563   TABLE ACCESS FULL SAM
select id from sam where name like 'AB%';
 card operation
- ---
2 SELECT STATEMENT
2   TABLE ACCESS BY INDEX ROWID SAM
2 INDEX RANGE SCAN SAM_IX
select id from sam where name like 'A%';
 card operation
- ---
  297 SELECT STATEMENT
  297   TABLE ACCESS FULL SAM
At 04:29 PM 11/5/2003, you wrote:
Hi Goulet,

The clustering factor on the index=37930
number of distinct keys=38357
number of leaf blocks=1075
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: Index behavior

I don't know. I'm just reporting what I found. It was new to me too.

At 09:39 AM 11/6/2003, you wrote:
OK, I can follow that, but why the change between ABC% and AB% ?

Henry
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 WHERE clause order

Then put it in procedural logic instead of into the SQL. SQL is a set (or 
more precisely bag) oriented language. You describe the set by its 
attributes, NOT by the steps to create it.

At 10:24 AM 11/6/2003, you wrote:
Wolfgang,
  I think you understand by now : I want a certain predicate evaluated 
first because it has a program variable :select_sen_emp_chk_first and I 
want it checked before going to the check dept or salary since that will 
need a table access. Thank You.

Wolfgang Breitling [EMAIL PROTECTED] wrote:
Why do you want a certain predicate evaluated first?
At 02:34 PM 11/4/2003, you wrote:
Hi,
 In a SQL statement I want a certain where clause to be done first. Is
 it enough to list it first as follows or do I (and can I) do something
 else to make it get checked first before other WHERE/AND clause are
 looked at. Thanks :

SELECT emp_id FROM emp
WHERE select_sen_emp_chk_first = 'Y'
AND dept = :dept
AND salary  :min_sal
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
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 WHERE clause order

Of course there is.

The difference between sets and bags is that sets do not allow duplicates.

At 11:29 AM 11/6/2003, you wrote:
BAG OK, is there a bag of all bags?

On 11/06/2003 01:19:25 PM, Wolfgang Breitling wrote:
 Then put it in procedural logic instead of into the SQL. SQL is a set (or
 more precisely bag) oriented language. You describe the set by its
 attributes, NOT by the steps to create it.

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).

OT - Re: Bitmap join indexes

YES, for once Mladen got caught flat-footed. ;-)

Even I got this one.

At 06:54 AM 11/6/2003, you wrote:
I am running EE. In what sense are they expensive?
On 11/05/2003 04:54:25 PM, Paul Drake wrote:
 Mladen,

 If you are not currently running Enterprise Edition, they are indeed 
very expensive indexes. :D

 Pd

Mladen Gogala
Oracle 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: ** SQL WHERE clause order

You're mistaking bags for windbags.

At 01:44 PM 11/6/2003, you wrote:
Mladen Gogala  scribbled on the wall in glitter crayon:

 BAG OK, is there a bag of all bags?

is that anything like a boss of all bosses?;-)
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: any problem rebuilding indexes used for replication

 
it? TIA.

 __
 Do you Yahoo!?
 Protect your identity with Yahoo! Mail AddressGuard
 http://antispam.yahoo.com/whatsnewfree
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Yong Huang
   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: zhu chao
  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: Re: explain plan conundrum

However, since it is a join predicate, the histogram data can not be used. 
The CBO uses the density values of the join column(s) to derive the join 
selectivity. The density value of a column changes (from 1/num_distinct) 
when you collect a histogram. If you create a frequency histogram (aka 
value based histograms or equi-width histogram), which you most likely did 
for a field with only four distinct values using the default size of 75, 
the calculated density will be much lower than 1/num_distinct (i.e. less 
than 1/4 = .25) and therefore the join selectivity and ultimately the join 
cardinality will be unrealistically low, increasing the likelihood that the 
CBO will choose an NL join.

At 04:49 PM 11/4/2003, you wrote:
the data is very skewed, but i included 'for all indexes' and for all
indexed columns. doesnt that create histograms? or do i have the syntax
wrong. what i really needed was histograms, Ill bet.
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: explain plan conundrum

The join order of an access plan - in the absence of any leading or ordered 
hints - is determined strictly like everything else by the CBO: the join 
order with the lowest estimated cost wins. And the selectivity and 
cardinality estimates play a big role in determining the cardinality and 
thus cost estimates.
To answer your question does oracle use histograms and distinctness in 
determining join order? outright: Yes, but only indirectly: histograms and 
distinctness determine the cardinality - therefore the cost estimates - 
therefore the join order.
And lastly, you can not compare the results, i.e. plans, of two different 
parses. Each is in its own world.

At 10:04 AM 11/5/2003, you wrote:
im not concerned about the type of join. Im strictly concerned about the 
join order. does oracle use histograms and distinctness in determining 
join order? The odd thing is that it chose a different join order on these 
tables earlier and on 'similiar' joins(ie large number of records and only 
4 distinct values on the join column) oracle chooses the proper join 'order'

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 hehavior

What Oracle version?

Can you post more detail about the table and index.

At 10:09 AM 11/5/2003, you wrote:
Hi List,

Does someone throw ligts on the following index behavior

Note
a)name is an unique index column
b) table and index has been analyzed b4 running the query
1) select id from table1 where name like 'ABC%';
FULL Table scan
1) select id from table1 where name like 'AB%';
Index scan
name is an unique index column
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: explain plan conundrum

Histograms are only used to refine the selectivity of a predicate. This in 
turn determines the cardinality estimate and various costs such as index 
access cost and then of course join costs (NL, sort-merge, and hash) and 
join cardinality. This ultimately will drive the decision whether a 
particular index access looks more promising (i.e. has a cheaper estimated 
cost than an FTS) and which join order together with which join method 
looks most promising  - has the cheapest overall cost.

It is all driven by the estimated costs, which are driven by the estimated 
cardinalities, which are driven by the estimated selectivities.

BTW. Histograms on non-indexed columns also affect the cardinality estimate 
when they are used in the where clause, which is why it is not enough to 
collect histograms for all indexed columns. Conversely, most likely not 
all indexed (much less ALL) columns require a histogram. Histograms, and 
the number of their buckets, need to be chosen on a column by column basis, 
not with a broad brush such as for all columns or for all indexed 
columns. In the best case it is a waste of resources to gather them, but 
it easily also can be detrimental to the performance.

At 10:04 AM 11/5/2003, you wrote:
are histograms only used to determine whether to use an index or join 
type, not join order?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.co 

--
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 behavior

But that doesn't really explain why the optimizer chooses an FTS with a 
predicate that presumable is more selective (name like 'ABC%') and an index 
scan with a predicate that presumable is less selective (name like 'AB%'). 
I could understand it if it were the other way around.
Is there a histogram on the name column?

At 11:34 AM 11/5/2003, you wrote:
Sami,

Your problem is not with the index, but rather the cost based 
optimizer.  Most of us have been beat severely over the head and 
shoulders through the years that full table scans are a BAD thing, me 
included BTW.  Well, it's time for the old dog to learn new tricks.  So 
that I'm not a long winded person, take a look in Select magazine, 3rd 
qtr 2003, for the article In Defense of Full Table Scans by Jeff 
Maresh.  For a long time the CBO was a mystery to me as well especially 
when it did unexpected things like this.  I've applied Jeff's ideas on 
computing an index's efficiency to see if it explained what the CBO 
did.  Amazingly in 95% of the cases I've analyzed it made absolute sense.

I'm including Jeff with a courtesy copy of this message so that 
1) I can pat him for making the waters clear and 2) so he can add 
anything he desires.

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: Index hehavior

Just to verify and make absolutely clear: those two sql ran back to back 
with no changes to anything (statistics, session parameters) in between. Right?

At 10:09 AM 11/5/2003, you wrote:
Hi List,

Does someone throw ligts on the following index behavior

Note
a)name is an unique index column
b) table and index has been analyzed b4 running the query
1) select id from table1 where name like 'ABC%';
FULL Table scan
1) select id from table1 where name like 'AB%';
Index scan
name is an unique index column

Any help would be really appreciated.
-Sami
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: explain plan conundrum

2003-11-04 Thread Wolfgang Breitling

It's not 20 billion rows but 20 billion bytes. It's only 1 billion rows. 
The cartesion product of 5K rows and 366K rows is 1830M rows or 1.8G. If 
the join predicate is not very selective, .5 or .33 for example, that would 
yield an estimated join cardinality of 1G (after rounding).

At 11:34 AM 11/4/2003, you wrote:
I cant sql trace it now. I hae run statspack. this query is running now 
and I dont want to run another copy with a trace on until this finishes, 
since I dont want to suck up resources. Im at a loss as to where the 20 
billion rows comes from in this explain plan? Everything including the 
indexes are analyzed.

when the two tables involved have 36k and 5k rows involved.
looks like some form of cartesian join, but its not showing up in the 
plan. The two tables are joined by a column.

any place to look on this? I know I need the 10046 trace, but I cant get 
that yet and it make take 12 hours to get it after this runs.

select col1,
   col2,
   col3
from tab1
 tab2
where tab1.col1 = tab2.col2;
Operation   Object Name RowsBytes   CostObject 
Node In/Out  PStart  PStop

SELECT STATEMENT Optimizer Mode=CHOOSE  1 
G 237
  HASH JOIN 1 
G 20G 237
INDEX FAST FULL SCANPK1 5 K 11 
K3
TABLE ACCESS FULL   TABLE2  366 K   4 
M 231
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: explain plan conundrum

2003-11-04 Thread Wolfgang Breitling

Could you please post the entire sql and plan and statistics of the tables 
and indexes so that we can comment on it rather than letting us guess on 
selective bits of the entire problem.

At 01:29 PM 11/4/2003, you wrote:
everything is analyzed. For all indexes, for all indexed columns.

I used analyze. its the same as dbms_stats, just not as robust. I use it 
when I dont feel like typing out dbms_stats.

Are there optimizer parameters that help the optimizer determine join 
order? Ive never had to use the 'ordered' hint on the CBO before when 
everything is analyzed. The difference was huge. Ran for 2 hours and still 
going, with the hint ran in 45 seconds.

im assuming there are some init.ora parameters that I should check out? 
Does oracle take into account 'distinctness' of the columns being joined?
I have 1 table with 366,000 rows and another with 5,000 rows. the columns 
being joined have 4 distinct values each. However, the table with 366,000 
rows joins on its primary key to another table and that filters out enough 
rows that that join should go first. The optimizer made a bad decision.

how do i analyze why it made a bad join order decision? hints like this 
are a stop gap fix.
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 WHERE clause order

2003-11-04 Thread Wolfgang Breitling

Why do you want a certain predicate evaluated first?

At 02:34 PM 11/4/2003, you wrote:
Hi,
   In a SQL statement I want a certain where clause to be done first. Is 
it enough to list it first as follows or do I (and can I) do something 
else to make it get checked first before other WHERE/AND clause are 
looked at. Thanks :

SELECT emp_id FROM emp
WHERE select_sen_emp_chk_first = 'Y'
AND  dept = :dept
AND  salary  :min_sal
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: tim= values in Windows 2k / Oracle 9.2.0.4 trace files

2003-10-31 Thread Wolfgang Breitling

It appears to be fairly close to the value of GetTickCount (the number of 
milliseconds since boot). However, obviously GetTickCount is measured in 
milliseconds whereas tim in Oracle 9 increments by microseconds. Also from 
first look, tim is slightly larger than GetTickCount*1000 on my current system.
I'll do a bit more inestigating.

At 01:04 PM 10/31/2003, you wrote:
I don't know. But it's apparently *not* a string that includes a
gettimeofday value:
$ perl tim.pl 18446744069800424010
00:00:00.424010 Sunday 00 January 1900
$ perl tim.pl 1844674406980042
04:33:26.980042 Thursday 15 June 2028
$ perl tim.pl 18446744069800
07:05:44.069800 Sunday 02 August 1970
tim.pl is the program shown on p134 of Optimizing Oracle Performance.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
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[2]: What happened to Howard Rogers ?

2003-10-27 Thread Wolfgang Breitling

Once the lawyers get hold of an issue, common sense goes out the window.

At 11:19 AM 10/27/2003, you wrote:
Monday, October 27, 2003, 12:09:25 PM, you wrote:
DW But as a
DW consequence of the discussions with his management chain, he ended up
DW agreeing to resign.
Odd. It must be really important then, when you have a
brilliant and innovative employee capable of inventing
something unique, to have him go work for some other
company, possible even a competitor. I never would have
come up with that strategy, and no doubt that's why I'm not
executive material.
Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * 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: What happened to Howard Rogers ?

2003-10-26 Thread Wolfgang Breitling

There are laws that are in effect without you having to sign papers.
I'm no lawyer and I do understand that just because it appears on some 
website a statement isn't necessarily true ( we all should be very well 
aware of that with all the advice about hit ratios and extent fragmentation 
on myriads of websites ), but here are two excerpts:

Source: http://copylaw.com/new_articles/wfh.html

B. Works Created by Traditional Employees

A work created by an employee within the scope of his or her employment 
is automatically considered a work for hire. These works do not have to 
fall into one of the nine narrow statutory categories of works for hire and 
no written agreement is required. Typically, work for hire situations 
involve independent contracts, not employee-employer situations

C. Independent Contractor of Employee?

The term employee is a legal term of art without precise definition. 
However, a worker is most likely to be classified as an employee if the 
person who employs her has the legal right to control the method and 
result of her work; provides her with tools; pays her on a daily, weekly 
or monthly basis; and can fire her. The IRS use a 20-part test, applicable 
for copyright purposes, to distinguish between employees and independent 
contractors.

Unlike specially commissioned works, for works where a traditional 
employee-employer relationship exists, no work for hire agreement is 
needed. However to avoid any ambiguity, itÕs a good idea to include a 
well-drafted statement in the employment agreement acknowledging that any 
work created in the scope of employment will be considered a work for hire. 
The employment agreement can also include non-competition and 
non-disclosure provisions to protect your business's trade secrets.

Re: What happened to Howard Rogers ?

2003-10-25 Thread Wolfgang Breitling

I wouldn't be too sure about that. At least here in Canada everything job 
related you produce while employed belongs to the employer. The in my own 
time part opens a grey area, but if it is very job related and contains or 
is related to information obtained on the job I don't think you've got a 
leg to stand on.
As a consultant I had a lawyer draw up an amendment that specifically 
retains my rights on anything I produce that is not specifically tied to 
the client's competitiveness - most of my work is in administering and 
tuning Oracle, Peoplesoft, or related middleware and is therefore generic. 
Otherwise everything I create under the contract is automatically property 
of the client.

At 04:14 PM 10/25/2003, you wrote:
is that copyright thing something unique to Australia? I dont think they can
claim that in the US unless you sign some documents first.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, October 25, 2003 4:39 PM
 Howard's response is below. Hopefully this makes things clearer for
 those who are still interested.


 I worked for Oracle for 4 years, producing my own stuff in my
 own time, to elaborate on questions that perplexed me, and
 about which I would often get asked by Oracle course students
 -because the official material either didn't cover it,
 covered it badly, or just plain mis-informed.

 Oracle claimed copyright on the lot, so I had to remove the
 material (Lydian Third is a site which copied the lot first,
 and despite repeated requests still hasn't removed it).

 In June this year, I asked for permission to have a website
 again, offering to have all material and content vetted by
 anyone Oracle cared to choose for the job, before it went up.
 They refused. I also asked for permission to stay at home when I wasn't
 training, so that I could do research on Oracle matters. They
 refused that too.

 In August, I therefore resigned. I finished work *for* Oracle
 on October 6th. I had two weeks of leisure, and now I
 contract back to Oracle, teaching much as before. Only this
 time, I get to write my own material, and when I'm not
 training, I can stay at home and do real research.

 I was never sacked by Oracle.

 Regards
 HJR
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: dba interview questions

2003-10-24 Thread Wolfgang Breitling

That's more like it. The recent postings re this thread got too serious and 
all suffered from the same fatal flaw - they assumed that the interviewer 
knows something about Oracle database administration. But then you don't 
need a list of interview questions. Those lists are for interviewers who 
know nothing about the job they are interviewing for, which is why they do 
not only need a list of questions, but also the cheat sheet with the answers.

I like Bambi's question the best.

At 09:44 AM 10/24/2003, you wrote:

On 10/24/2003 11:04:34 AM, Thater, William wrote:

but that would provide that the company actually know how to define
job
duties and then convey them to HR who would then be able to screen
applicants on actual experience and not just groups of letters.
Now we are talking about the job description for damagement. HR is
usually perfectly capable of picking the best candidate, the one
with the most expensive suit, groundless self-confidence  and least
amount of knowledge or management capabilities. Racial, gender and
religious prejudices are usually a plus.
Mladen Gogala
Oracle 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: Cache a table

2003-10-21 Thread Wolfgang Breitling

Before Oracle 8 and the new touch count algorithm the cache attribute made 
sense. If a small, frequently used table was read by a full scan, it would 
have been put at the end of the LRU chain eligible to be aged out 
immediately, quite possibly by itself if it consisted of more than ~ 
db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd full scan read 
would already override the previously read blocks. Marking the table as 
CACHEd prevented that.

At 01:09 PM 10/21/2003, you wrote:
I always wondered why Oracle thought this was a useful table attribute.

My gut feeling is that it is an extra that does little.

For example, say we want to keep a code table in memory because it is
constantly being hit for column verifiction.  By definition, if a table is
constantly being queried, it's segments will be in memory because they never
age out.  That sounds like cacheing to me.
And then I remember a specific piece of Oracle documentation saying that,
even though we may mark a table to be cached, it *still* may be aged out
if memory is needed for other data blocks.
Like I said, sounds a little like here you have it, and here you don't.

I'm sure that my impression is wrong and someone will correct me.  But I
doubt I will use the CACHE option anytime soon.
Tom Mercadante
Oracle Certified Professional
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: CBO with Foreign Key

2003-10-17 Thread Wolfgang Breitling

Ah, but that cardinality underestimation has nothing to do with the join or 
the foreign key relationship, but solely with the other fallacy of the cbo 
- the predicate independence assumption. In your example, the predicates a 
and b are completely dependent; once you choose one, the other is 
determined, not open to choice anymore. The optimizer has no clue on how to 
estimate NDV(a,b) from the existing statistics which give it only NDV(a) 
and NDV(b). As I said before, in Oracle 9i you can use dynamic sampling at 
a level = 5 to ask the cbo to refine the estimate through sampling at 
parse time.

At 12:43 PM 10/17/2003, you wrote:
Wolfgang,

Thanks for the response. The problem I am seeing is slightly different.
(I'll try to post some more detailed data, when I have the time). It's time
to take a deep breath and be a bit clearer in my description.
The issue arises when the PK of the parent is made up of more than 1 field.
For example:
CREATE TABLE Parent (a varchar2(1), b number primary key (a,b))
INSERT INTO Parent VALUES ('A',1)
INSERT INTO Parent VALUES ('B',2)
INSERT INTO Parent VALUES ('C',3)
Now create a table Child with a FK references Parent (a,b). Assume Child has
10 rows, each Parent PK showing up at least once.
OK, now NDVp(a)=NDVc(a)=3; NDVp(b)=NDVc(b)=3

join cardinality = CARDp * CARDc * 1/max(NDVp(a),NDVc(a)) *
1/max(NDVp(b),NDVc(b)) =
= 3 * 10 /(3*3) ~ 3
The actual cardinality will be 10. This is because we actually should be
using NDV(a,b) not NDV(a)*NDV(b).
Hope this is clearer. Again, I'll try to post some actual data as soon as I
can.
Henry

-Original Message-
Wolfgang Breitling
Sent: Thursday, October 16, 2003 10:04 PM
To: Multiple recipients of list ORACLE-L
That's the problem with answering without thinking the answer completely
through. My example below was only looking at the relationship from the
child table side. If you look at it from the parent table side and add
predicates - probably the more frequently used scenario - the problem
becomes clear:
Let's use the example below and narrow the resultset down to a single
parent via its PK. As far as the optimizer is concerned that means a
selectivity of 0.01 (=1%) and the estimated join cardinality is
0.01 * 100 * 1000 * 1/max(100,10) = 10
However, since the one selected parent has either 0 or 100 children, the
estimate is off. In the majority of cases, the query will be after one of
the parents with children and the cardinality of the join will be 100, the
optimizer having understimated by a factor of 10, just as Henry said. This
underestimation is not because the optimizer does not recognize the
parent-child relationship but rather a consequence of the violation of the
CBO's Join Uniformity Assumption which states that a row from one table
is equally likely to join with any row from the second table
In Oracle 9 you can set dynamic sampling to a value = 5 to get the cbo to
probe the join cardinality at parse time. There is no such remedy in 8.1.7.
In the example scenario I would consider altering the parent table
statistics to counterbalance the violation of the join uniformity
assumption. Of course, in a complex web of relationships this becomes a
difficult, if not impossible balancing act. Or use hints to guide the cbo.
At 04:19 PM 10/16/2003, you wrote:
Do you have concrete numbers. It''s been a while that I did my tests for
the paper and I would have to dig out my old testcases, but I was left
with the impression that the join cardinality formula was derived from
parent-child relationship joins and that the cardinality estimates are OK
for foreign key relationship joins.
for example let's say you have a parent table with 100 (distinct) parents
= NDVp = 100; a child table with 1000 children but only 10 distinct
parent keys = NDVc = 10. Each child must have a parent, therefore you
have 90 childless parents and 10 parents with, on average, 100 children
each (you can choose different numbers, it comes out to the same). If you
join parent and child on the foreign key you should get all 1000 child
rows together with their parent data. The cardinality estimate by the CBO
would be:

  join cardinality = CARDp * CARDc * 1/max(NDVp, NDVc) = 100 * 1000 *
 1/max(100,10) = 100 * 1000 / 100 = 1000 (= rows(child) as you correctly
 observed)

The problems I found - and they are shown with the examples in the paper -
come when you do not have a parent-child relationship, either you have
rows in both tables which do not have a match in the joined table, or if
you have a many-to-many relationship.

But maybe I am missing something ( wouldn't be the first time).

At 02:19 PM 10/16/2003, you wrote:
Is there any way to get the CBO (8.1.7) to recognize parent/child
relationships? This seems to be an extreme example of the Join
Independence
Assumption Fallacy (or I guess maybe the Predicate Independence
Assumption)
discussed by Wolfgang Breitling in Fallacies of the Cost Based
Optimizer.
If I am joining

Re: CBO with Foreign Key

2003-10-16 Thread Wolfgang Breitling

Do you have concrete numbers. It''s been a while that I did my tests for 
the paper and I would have to dig out my old testcases, but I was left with 
the impression that the join cardinality formula was derived from 
parent-child relationship joins and that the cardinality estimates are OK 
for foreign key relationship joins.
for example let's say you have a parent table with 100 (distinct) parents 
= NDVp = 100; a child table with 1000 children but only 10 distinct parent 
keys = NDVc = 10. Each child must have a parent, therefore you have 90 
childless parents and 10 parents with, on average, 100 children each (you 
can choose different numbers, it comes out to the same). If you join parent 
and child on the foreign key you should get all 1000 child rows together 
with their parent data. The cardinality estimate by the CBO would be:

 join cardinality = CARDp * CARDc * 1/max(NDVp, NDVc) = 100 * 1000 * 
1/max(100,10) = 100 * 1000 / 100 = 1000 (= rows(child) as you correctly 
observed)

The problems I found - and they are shown with the examples in the paper - 
come when you do not have a parent-child relationship, either you have rows 
in both tables which do not have a match in the joined table, or if you 
have a many-to-many relationship.

But maybe I am missing something ( wouldn't be the first time).

At 02:19 PM 10/16/2003, you wrote:
Is there any way to get the CBO (8.1.7) to recognize parent/child
relationships? This seems to be an extreme example of the Join Independence
Assumption Fallacy (or I guess maybe the Predicate Independence Assumption)
discussed by Wolfgang Breitling in Fallacies of the Cost Based Optimizer.
If I am joining a parent and child table (parent PK [a,b]), Oracle assumes a
resulting cardinality of :
rows(parent)*rows(child)/{max(NDV[parent.a],NDV[child.a])*max(NDV[parent.b],
NDV[child.b])}.
This is assuming independence of field a and b. However, since they make up
the PK of the parent, the denomenator is actually rows(parent) so the actual
cardinality is rows(child). This causes the CBO to dramatically
underestimate the join cardinality. If this is part of a multi-table join
the entire execution plan can be thrown way off.
I would assume that since the FK constraint is part of the data dictionary,
Oracle would find a way to use this information. I checked by running
autotraces with and without the FK, but the plan remained unchanged.
Any suggestions?

Thanks.

Henry
--
Author: Henry Poras
  INET: [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: CBO with Foreign Key

2003-10-16 Thread Wolfgang Breitling

That's the problem with answering without thinking the answer completely 
through. My example below was only looking at the relationship from the 
child table side. If you look at it from the parent table side and add 
predicates - probably the more frequently used scenario - the problem 
becomes clear:

Let's use the example below and narrow the resultset down to a single 
parent via its PK. As far as the optimizer is concerned that means a 
selectivity of 0.01 (=1%) and the estimated join cardinality is
   0.01 * 100 * 1000 * 1/max(100,10) = 10

However, since the one selected parent has either 0 or 100 children, the 
estimate is off. In the majority of cases, the query will be after one of 
the parents with children and the cardinality of the join will be 100, the 
optimizer having understimated by a factor of 10, just as Henry said. This 
underestimation is not because the optimizer does not recognize the 
parent-child relationship but rather a consequence of the violation of the 
CBO's Join Uniformity Assumption which states that a row from one table 
is equally likely to join with any row from the second table

In Oracle 9 you can set dynamic sampling to a value = 5 to get the cbo to 
probe the join cardinality at parse time. There is no such remedy in 8.1.7. 
In the example scenario I would consider altering the parent table 
statistics to counterbalance the violation of the join uniformity 
assumption. Of course, in a complex web of relationships this becomes a 
difficult, if not impossible balancing act. Or use hints to guide the cbo.

At 04:19 PM 10/16/2003, you wrote:
Do you have concrete numbers. It''s been a while that I did my tests for 
the paper and I would have to dig out my old testcases, but I was left 
with the impression that the join cardinality formula was derived from 
parent-child relationship joins and that the cardinality estimates are OK 
for foreign key relationship joins.
for example let's say you have a parent table with 100 (distinct) parents 
= NDVp = 100; a child table with 1000 children but only 10 distinct 
parent keys = NDVc = 10. Each child must have a parent, therefore you 
have 90 childless parents and 10 parents with, on average, 100 children 
each (you can choose different numbers, it comes out to the same). If you 
join parent and child on the foreign key you should get all 1000 child 
rows together with their parent data. The cardinality estimate by the CBO 
would be:

 join cardinality = CARDp * CARDc * 1/max(NDVp, NDVc) = 100 * 1000 * 
1/max(100,10) = 100 * 1000 / 100 = 1000 (= rows(child) as you correctly 
observed)

The problems I found - and they are shown with the examples in the paper - 
come when you do not have a parent-child relationship, either you have 
rows in both tables which do not have a match in the joined table, or if 
you have a many-to-many relationship.

But maybe I am missing something ( wouldn't be the first time).

At 02:19 PM 10/16/2003, you wrote:
Is there any way to get the CBO (8.1.7) to recognize parent/child
relationships? This seems to be an extreme example of the Join Independence
Assumption Fallacy (or I guess maybe the Predicate Independence Assumption)
discussed by Wolfgang Breitling in Fallacies of the Cost Based Optimizer.
If I am joining a parent and child table (parent PK [a,b]), Oracle assumes a
resulting cardinality of :
rows(parent)*rows(child)/{max(NDV[parent.a],NDV[child.a])*max(NDV[parent.b],
NDV[child.b])}.
This is assuming independence of field a and b. However, since they make up
the PK of the parent, the denomenator is actually rows(parent) so the actual
cardinality is rows(child). This causes the CBO to dramatically
underestimate the join cardinality. If this is part of a multi-table join
the entire execution plan can be thrown way off.
I would assume that since the FK constraint is part of the data dictionary,
Oracle would find a way to use this information. I checked by running
autotraces with and without the FK, but the plan remained unchanged.
Any suggestions?

Thanks.

Henry
--
Author: Henry Poras
  INET: [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).
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net

Re: bitmap conversion on a index that is not bitmapped ???

2003-10-15 Thread Wolfgang Breitling

However, under Oracle 8.1.7, the default for _b_tree_bitmap_plans IS false. 
The default changes to true in Oracle 9, which is one of the reasons for 
plan differences when migrating to 9, not always for the better.
Did the OP set _b_tree_bitmap_plans to true?

At 01:39 PM 10/15/2003, you wrote:
Missed one important point. You can disable that by setting
_b_tree_bitmap_plans to FALSE. Deleting the stats may not be a right
apporach as that may screwup some other plans.
Sorry for missing the important one..

KG

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 10:39 PM
 John:

 Optimizer is a smart boy!!! He knows the column has few distinct values
 and decides the BITMAP access would be appropriate and making BITMAP
 plans from the BTree indexes. If you delete the stats for that index,
 you will get the old behavior.

 KG



 =
 Have a nice day !!
 
 Best Regards,
 K Gopalakrishnan,
 Bangalore, INDIA.
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: K Gopalakrishnan
   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: K Gopalakrishnan
  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: CBO Puzzles

2003-10-10 Thread Wolfgang Breitling

Until I have seen the 10053 traces I do not believe that the access plans 
are identical. Most of the time if the performance of a query changes when 
index statistics are deleted it is because the default statistics, 
especially clustering factor is drastically different than the real statistics.
I have not worked with a 8.0 database in 4 years and can't remember if the 
version emits STAT entries to the trace with a 10046 trace. That would be 
the ultimate word on what access path was used.

At 03:44 PM 10/10/2003, you wrote:
All

We have a 8.0.4 database using choose optimizer. We have a query that access
a single table using index.
If we analyze the table and indexes - query takes 8 min to complete

If we analyze table and then delete stats on indexes - query runs in two
minutes.
Explain plan is the same in both cases. I did a event 10053 trace but could
not find any difference between the choices the CBO makes except when the
indexes are not analyzed it thinks the cost is low.
Any ideas why the query would execute faster - with the same explain plan -
if the CBO thinks its cost is low ?
PS : We ruled out the effects of caching in the buffer by reversing the
order of the tests..
TIA

Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Oracle-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
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: DB Parameters

2003-10-09 Thread Wolfgang Breitling

I was tempted to point to the /*+ _try_harder */ hint (revealed by Jonathan 
Lewis at this year's IOUG), but that won't be available until Oracle 10.
Until then we have to use the old methods of identifying where the time is 
spent and figure out what can be done to eliminate as much of it as possible.

I have seen deletes take a long time if the table was heavily indexed. 
Removing the indexes before the delete and rebuilding afterwards was the 
solution there.

At 02:29 AM 10/9/2003, you wrote:
Hi  List ,

Can you  help me please ,

What  are the database parameters that i have to increase or modify in
order to increase the speed of my ddl
statments ,i have a  statment  that delete a table with 4 record but it
takes about  nine hours to accomplish !!!
Is their   any parameters on the server  that i have to check ??,Increase
rollback segment is not helpfull at all .
Iam runningoracle  9.2.0.1.0  production .
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 keep statistics up to date for CBO

2003-10-09 Thread Wolfgang Breitling

, 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.nethttp://www.orafaq.net
--
Author: Bob Metelsky
  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).

**
This e-mail message is confidential, intended only for the named 
recipient(s) above and may contain information that is privileged, 
attorney work product or exempt from disclosure under applicable law. If 
you have received this message in error, or are not the named 
recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**5 

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 keep statistics up to date for CBO

2003-10-09 Thread Wolfgang Breitling

I don't see any reason why that couldn't happen concurrently. The table and 
its indexes are different physical segments.

At 09:59 AM 10/9/2003, you wrote:

So Wolfgang,

in that case, should _wait_ till table stats (which are probably estimate) 
are complete to perform index stats (which you recommend compute) or can 
that happen in parallel.

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: Thursday, October 09, 2003 11:40 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: how to keep statistics up to date for CBO

One reason to collect index stats separately would be if you use estimate
for the collection of the table statistics. In that case I collect the
index stats separately with a compute. Index statistics collection is fast
enough to always go for exact statistics rather than sampling.
**
This e-mail message is confidential, intended only for the named 
recipient(s) above and may contain information that is privileged, 
attorney work product or exempt from disclosure under applicable law. If 
you have received this message in error, or are not the named 
recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**5 

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: EXP-00091: Exporting questionable statistics.

2003-10-08 Thread Wolfgang Breitling

There are a number of reasons why you might get that error. The most 
frequent one is probably that the NLS_LANG setting between the server and 
the client are different. In Oracle 8i that prevented the export from 
exporting existing statistics altogether. Oracle 9i does export the 
statistics, but gives you this warning. If I'm correct you should also have 
gotten the warning about possible charset conversion.

At 11:49 AM 10/8/2003, you wrote:
Hello,
I upgraded my oracle version from 9.2.0.1 to 9.2.0.3 and now I am getting 
this error.  I collected stats on all the tables and still have this 
error.  Any ideas?

--
Thanks,
Jake Johnson
[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: How to copy recursive files in Windows? like unix cp -r

2003-10-08 Thread Wolfgang Breitling

use xcopy /S or /E

C:\help xcopy
Copies files and directory trees.
XCOPY source [destination] [/A | /M] [/D[:date]] [/P] [/S [/E]] [/V] [/W]
   [/C] [/I] [/Q] [/F] [/L] [/H] [/R] [/T] [/U]
   [/K] [/N] [/O] [/X] [/Y] [/-Y] [/Z]
   [/EXCLUDE:file1[+file2][+file3]...]
  source   Specifies the file(s) to copy.
  destination  Specifies the location and/or name of new files.
  /A   Copies only files with the archive attribute set,
   doesn't change the attribute.
  /M   Copies only files with the archive attribute set,
   turns off the archive attribute.
  /D:m-d-y Copies files changed on or after the specified date.
   If no date is given, copies only those files whose
   source time is newer than the destination time.
  /EXCLUDE:file1[+file2][+file3]...
   Specifies a list of files containing strings.  When any of the
   strings match any part of the absolute path of the file to be
   copied, that file will be excluded from being copied.  For
   example, specifying a string like \obj\ or .obj will exclude
   all files underneath the directory obj or all files with the
   .obj extension respectively.
  /P   Prompts you before creating each destination file.
  /S   Copies directories and subdirectories except empty ones.
  /E   Copies directories and subdirectories, including empty ones.
   Same as /S /E. May be used to modify /T.
  /V   Verifies each new file.
  /W   Prompts you to press a key before copying.
  /C   Continues copying even if errors occur.
  /I   If destination does not exist and copying more than one file,
   assumes that destination must be a directory.
  /Q   Does not display file names while copying.
  /F   Displays full source and destination file names while copying.
  /L   Displays files that would be copied.
  /H   Copies hidden and system files also.
  /R   Overwrites read-only files.
  /T   Creates directory structure, but does not copy files. Does not
   include empty directories or subdirectories. /T /E includes
   empty directories and subdirectories.
  /U   Copies only files that already exist in destination.
  /K   Copies attributes. Normal Xcopy will reset read-only 
attributes.
  /N   Copies using the generated short names.
  /O   Copies file ownership and ACL information.
  /X   Copies file audit settings (implies /O).
  /Y   Suppresses prompting to confirm you want to overwrite an
   existing destination file.
  /-Y  Causes prompting to confirm you want to overwrite an
   existing destination file.
  /Z   Copies networked files in restartable mode.

The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line.
At 12:59 PM 10/8/2003, you wrote:
Hi List:

How to copy recursive files in Windows? like unix cp
-r
Thanks in advance,
Sami
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.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: alter session

2003-10-08 Thread Wolfgang Breitling

Exactly what it says: set the current schema to sys

At 05:44 PM 10/8/2003, you wrote:
List, what does the following do ?

alter session  set current_schema=sys;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
  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).

Cary's Book - new topic

I've got Cary's book for about a week now and I have a comment. On page 12 
he defines response time as

The elapsed time between the end of an inquiry or demand on a computer 
system and the beginning of a response; for example, the length of the time 
between an indication of the end of an inquiry and the display of the first 
character of the response at a user terminal.

I know from the reference provided that he did not create that definition 
himself. Do you agree with it? I don't. I believe that it depends and 
that there are cases where the user would define response time as the time 
from initiating the request until the entire transaction is complete, 
especially if subsequent work is dependent on the completion. You can 
easily play the evil genie in these cases by improving the response 
time such that the first character shows up sooner, yet the last character 
shows up much later (in the vein of first_rows vs. all_rows), effectively 
making things worse for the user. So even the definition of response time 
comes back to the business case. Sometimes the user can continue with the 
next task as soon as the first pieces of the request arrive, while at other 
times she can not until the last pieces are complete.

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: Cary's Book - new topic

Good point. I suppose this gets into the realm of perceived response 
time. Some applications break long transactions into several user 
interactions to hide the real response time. The application still makes 
its SLA defined as 90% of transactions complete in  3 seconds while the 
real transaction takes a lot longer. However, the user is kept busy and you 
get into that perception thing. I know that if I see a traffic jam, I look 
for ways to detour around it. Even it I don't save any time (there is no 
way of telling really), I have at least the impression that I'm doing 
something, that I'm in charge, rather sitting passively in the jam crawling 
along, waiting for something the clear up.

At 12:39 PM 10/7/2003, you wrote:

Also, if we are to really address the business case as you suggest then
the definition should also include the quality of the response. If the
response is quick but incomplete and the user has to ask 10 questions to
get at the one real answer he's after then what good is a fast response
time?
-Original Message-
Sent: Tuesday, October 07, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L
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: Cary's Book - new topic

A totally different point: How come I see your response before I see my own 
post?

At 12:39 PM 10/7/2003, you wrote:

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: Cary's Book - new topic

Yep, sounds familiar. So there you have your biggest hurdle for the 
performance project: getting the business to set measurable targets and 
prioritize the tasks that need attention. How do you Work first to reduce 
the biggest response time component of a business' most important user 
action. if the business can not agree on what that is?
I once tried to explain to the CIO that I need measurable goals for a 
performance project (it wasn't a formal project, rather one of those 
'make it faster - now' things) and she replied fewer knocks on my door by 
the CFO. Of course Cary's evil genie would do the optimization by 
relocating one of the two such that their offices wouldn't be adjacent anymore.

At 01:24 PM 10/7/2003, you wrote:
how many projects actually have SLAs? Ive been on 5 projects and none of 
them have had them. Its always been.

Customer: 'It's Slow'
Me: What is?
Customer: The application. Make it fast.
Me: Define fast.
Customer: As fast as possible. Do it now.
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[2]: Cary's Book - new topic

Unfortunately it's not my ability to see into the future, but an inability 
to see all of the past.

Now what was that I was looking for?

At 03:34 PM 10/7/2003, you wrote:
Wolfgang,

Tuesday, October 7, 2003, 2:04:24 PM, you wrote:

W A totally different point: How come I see your response before I
W see my own post?
Sounds like you can see into the future.  Would you mind reading the
Wall Street journal and reporting back to us?
-rje
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: Cary's Book - new topic

Actually, something must be wrong with my feed. There are several gaps in 
the messages judging from the quoted original, which I never saw, in a 
response.

At 03:24 PM 10/7/2003, you wrote:
I'm using Method R to post.
On Tue, 2003-10-07 at 15:04, Wolfgang Breitling wrote:
 A totally different point: How come I see your response before I see my 
own
 post?

 At 12:39 PM 10/7/2003, you wrote:

 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
--
Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain 
confidential, proprietary or legally privileged information.  No 
confidentiality or privilege is waived or lost by any mistransmission.  If 
you receive this message in error, please immediately delete it and all 
copies of it from your system, destroy any hard copies of it and notify 
the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the 
intended recipient. Wang Trading LLC and any of its subsidiaries each 
reserve the right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, 
except where the message states otherwise and the sender is authorized to 
state them to be the views of any such entity.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
  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 send mail from windows command prompt?

2003-10-03 Thread Wolfgang Breitling

 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: Table not reusing deleted space

2003-10-02 Thread Wolfgang Breitling

What is the block size of the database. Also, is the AVG_ROW_LEN of almost 
5000 bytes evenly distributed, i.e. are ALL rows more or less 5000 bytes 
long, or are there many rows that are a lot smaller and then some that are 
really big?

At 11:49 PM 10/1/2003, you wrote:
Here are the stats if you're interested. I can't run dbms_space on the 
table because it will lock up the application. This table is accessed ALL 
the time. It grabbed another 100M today! Extent management is LOCAL with 
UNIFORM SIZE of 100M.

24th Sep 2003

OWNE 
SEGMENT_NAME  TABLESPACE_NAMEKB 
NEXTKB EXT[MAX]  % Inc
 -   
  --
CCA  CONNECT_TASK[T]   CC_TASK_TABS3,891,200 
   102,400 38[2147483645]0
CCA  CONNECT_TASK_CONNECTID[I] CC_TASK_IDXS  102,400 
   102,400 1[2147483645] 0
CCA  CONNECT_TASK_CRN[I]   CC_TASK_IDXS  102,400 
   102,400 1[2147483645] 0
CCA  PK_CONNECT_TASK[I]CC_TASK_IDXS  102,400 
   102,400 1[2147483645] 0

OWNE TABLE_NAME   NUM_ROWS AVG_ROW_LEN BLOCKS 
EMPTY_BLOCKS  CHAIN_CNT
 -- -- --- -- 
 --
CCA  CONNECT_TASK   1855834898 484189 
   2210   1445



2nd Oct 2003

OWNERSEGMENT_NAME  TABLESPACE_NAME 
   KB   NEXTKB EXT[MAX]
 - 
   
CCA  CONNECT_TASK[T]   CC_TASK_TABS 
4,198,400  102,400 41[2147483645]
CCA  CONNECT_TASK_CONNECTID[I] CC_TASK_IDXS 
  102,400  102,400 1[2147483645]
CCA  CONNECT_TASK_CRN[I]   CC_TASK_IDXS 
  102,400  102,400 1[2147483645]
CCA  PK_CONNECT_TASK[I]CC_TASK_IDXS 
  102,400  102,400 1[2147483645]

OWNE TABLE_NAME   NUM_ROWS AVG_ROW_LEN BLOCKS 
EMPTY_BLOCKS  CHAIN_CNT
 -- -- --- -- 
 --
CCA  CONNECT_TASK   1841134958 512699 
  12100   1528




 From: Sinardy Xing [EMAIL PROTECTED]
 Date: Wed, 1 Oct 2003 14:51:40 +0800
 Subject: RE: Table not reusing deleted space
Hi Kaing,

Have you check the degree of fragmentation?

have you check your extent size?

Sinardy

--
Leng Kaing
Email: [EMAIL PROTECTED]
Phone: +61-3-9203-7589
Mobile: +61-417-371-348
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kaing, Leng
  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: Seems odd to me....(bug?)

2003-10-02 Thread Wolfgang Breitling

scott @connect system/manager

system create table employee_role (a number);

Table created.

system grant all on employee_role to scott;

Grant succeeded.

system @connect scott/tiger
Connected.
scott create synonym employee_role for system.employee_role;

Synonym created.

scott select * from employee_role;

no rows selected

scott @connect system/manager
Connected.
system revoke all on employee_role from scott;

Revoke succeeded.

system  @connect scott/tiger
Connected.
scott INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL);
INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)
*
ERROR at line 1:
ORA-00942: table or view does not exist
scott

Do a select * from user_objects where object_name = 'EMPLOYEE_ROLE' 
At 04:39 PM 10/2/2003, you wrote:
Oracle EE 8.1.7.2
HP-UX 11
Can anyone explain this?

1* INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)

SQL /

INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

  1* select count(*) from user_tables where table_name='EMPLOYEE_ROLE'
SQL /
  COUNT(*)
--
 1
SQL CREATE TABLE EMPLOYEE_ROLE AS SELECT * FROM USER_TABLES;

CREATE TABLE EMPLOYEE_ROLE AS SELECT * FROM USER_TABLES

 *

ERROR at line 1:

ORA-00955: name is already used by an existing object



Any ideas?

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chris Stephens
  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: Huge optimization costs with 9.2

2003-10-01 Thread Wolfgang Breitling

One of the undocumented init.ora parameters that changed from 8 to 9 is 
_UNNEST_SUBQUERY  (from false to true). You could try if that is the 
culprit. Of course, since it is an undocumented parameter, get the blessing 
from Oracle support before using it in a production database.

At 10:09 AM 10/1/2003, you wrote:
Joan, what is the difference in the plans? What specific feature
made the difference? Are the values of
optimizer_index_cost_adj and optimizer_index_caching same on both
versions? How about histograms? What is with
db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
everything same as in 8i? May be setting of those parameters can be
tweaked to your benefit?
On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
 Kirti,

 I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
 performance is good. After upgrade, one query run time from 2 min to 12
 hours. Of course, I re-analyzed all tables and indexes. The explain plan
 changed from hash join to nested-loop. All the parameters are same. So I
 have to put optimized_feature_enable=8.1.7 to make run normal as usual.
 I hate to disable the new feature, but no choose.
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: STAT from trace

2003-10-01 Thread Wolfgang Breitling

,tim=930530

At 08:54 AM 10/1/2003, you wrote:
Hi!

This is the problem, that everything else was identical. If you executed
exactly the same query again, it didn't get hard parsed anymore, thus no
STAT lines were generated. Either flush shared pool or just add some bogus
comment using /* */ into your query to get parsing and STAT lines.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 4:34 PM
 I was creating some trace files yesterday and came across one of these
 problems that shows up occasionally (then I forget about it).

 When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus;
 8.1.7.4), I got the STAT line in the trace and the associated 'row source'
 information after running tkprof.

 When I ran my query using ALTER SESSION SET EVENTS '10046 trace name
context
 forever, level 8' (or with dbms_system.set_ev), there were no STAT entries
 for my query. There were some for some of the recursive queries.

 Everything else was identical.

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: STAT from trace

2003-10-01 Thread Wolfgang Breitling

Do you get the PARSE, EXEC and FETCH entries for the sql?

You gave us your Oracle version, but what is your platform?
Would you care to try the following: since sql_trace = true corresponds to 
event 10046, level 1, why not set event 10046 at level 9. Maybe that'll 
do the trick.

At 12:59 PM 10/1/2003, you wrote:
Tried that. Also queried on open_cursors in a parallel session.

Henry

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of 
Daniel Fink
Sent: Wednesday, October 01, 2003 1:59 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: STAT from trace

Henry,

What happens if you issue another query after the query of interest? 
(something like select 1 from dual;) STAT should be emitted when the 
cursor is closed.

Daniel

Henry Poras wrote:
 Thanks. I tried both disabling the trace and quitting from the session. 
No luck with 10046, just sql_trace.Henry
-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Behalf Of 
Jamadagni, Rajendra
Sent: Wednesday, October 01, 2003 10:25 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: STAT from trace

I get STAT lines no matter how I enable the trace. Make sure you wither 
close the session or stop the trace so that all pending STAT lines will 
be written to the file.

Raj

-Original Message-
From: Henry Poras [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L
Subject: STAT from trace
I was creating some trace files yesterday and came across one of these
problems that shows up occasionally (then I forget about it).
When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus;
8.1.7.4), I got the STAT line in the trace and the associated 'row source'
information after running tkprof.
When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context
forever, level 8' (or with dbms_system.set_ev), there were no STAT entries
for my query. There were some for some of the recursive queries.
Everything else was identical.

What's up?

Henry
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: locally managed autoallocate (was: Separate Indexes and

 are determined by
figuring out the space allocated to the newly created object taking into
account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters. So
the
object might start off with 1M extents instead of starting off with 64K
extents. The algorithm is similar to the one outlined above but it is more
complicated. The NEXT and PCTINCREASE seem to be ignored after the object is
created.
e.g.
create table ... tablespace locally_managed_autoallocate
  storage (initial 1M next 512K minextents 15 pctincrease 0) ... ;
Initial allocation will be 1M + (15 - 1) * 512K = 8M
When you create the table, you will see eight extents, each of one megabyte.
There are additional wrinkles, but I don't think the algorithm has bugs.
I don't think that there really is fragmentation in the sense that an
unused extent will remain unused forever. All extents will be in one of the
4
sizes mentioned above, and all are subject to reuse at some point.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
  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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 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: TEMP Tablespace problem

What is the Oracle version?

At 06:24 PM 9/30/2003 -0800, you wrote:
Hi!!
I am trying to change the size of my tablespace TEMP, I am not an 
Administrator but we really need to make this tablespace smaller.
Already the size is 13214 Mgs, and this tablaspace is on a disk that is 
full, so if we can not make it smaller we are going to be in a serius 
trouble ( our Administrator is not here until Monday).
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: locally managed autoallocate (was: Separate Indexes and

At 06:29 PM 9/30/2003 -0800, I wrote:


So the 1M initial extent allocation is not due to a 5 block minimum 
allocation rule but due to the fact that automatic space management 
requires 3 blocks plus 1 block for the segment header plus 1 block for 
actual data = 5 blocks, which lifts the request above the 64K threshold 
for a tablespace with 16K extents.
The which lifts the request above the 64K threshold for a tablespace with 
16K extents is poorly worded. What I meant to say is For a tablespace 
with a 16K block size those 5 blocks come to 80K which is more than the 64K 
first tier for autoallocated extents and you thus get 1 extent of the next 
tier size: 1M


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).
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: TEMP Tablespace problem

If the TEMP tablespace is a temporary tablespace, i.e. made of temp files 
rather than datafiles, then you can't offline it. It would have to be 
dropped and rebuilt.

At 07:34 PM 9/30/2003 -0800, you wrote:
Maybe you can create another temp tablespace (called temp_new) on another
disk, assign all users to temp_new, then offline the old temp tablespace,
drop the old temp tablespace, and finally remove the old temp datafiles
from OS.
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: locally managed autoallocate (was: Separate Indexes and