Re: Should we stop analyzing?

Dunno how he does it. But I'd settle for my replies from my ISP to make it here... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - How do you know they're nodding if they call you on the phone? Distinct rattling sound? :-) -- Please see the official ORACLE-L FAQ:

RE: Should we stop analyzing?

My explanation is. How is network connectivity priced? By bandwidth or latency. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wolfgang Breitling Sent: 12 January 2004 21:35 To: Multiple recipients of list ORACLE-L Subject: RE: Should we

RE: Should we stop analyzing?

We have the occasional network issue from Perth to Port Hedland (both in Western Australia, with Port Hedland being a couple of thousand km's north of Perth). When management phone up, I always reply with: Have you looked at a map? See how far north Port Hedland is...that's all uphill you know!

Re: Should we stop analyzing?

How do you know they're nodding if they call you on the phone? Distinct rattling sound? :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:59 PM We have the occasional network issue from Perth to Port Hedland (both

Re: Should we stop analyzing?

And all the +RULE queries you listed, where data dictionary queries anyway (which is designed for RBO). Tanel. [TG]: I can't even spell 10g, so I'll take your word for it... The OraApps 11i assertion did not sound right, so to verify I queried both the V$SQLAREA view as well as the

Re: Should we stop analyzing?

Don, Comments inline... Yes! IME, there ARE still problems in the CBO, especially with complex subqueries. I have more than a dozen systems where management insists on staying with the RBO! [TG]: With all due respect, what does management know about this stuff anyway? They do not

RE: Should we stop analyzing?

Hi Richard Strangely, I've also never been to a management meeting where the reason for my attendance was to enquire as to how the instance efficiency statistics were this month. On the other hand when management reports take 3 days not 3 hours they're the first to complain. I wonder since we

RE: Should we stop analyzing?

My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to

Re: Should we stop analyzing?

I'm sure that buffer cache hit ratio is still a big hit with the damagement. You should also compile dictionary cache hit ratio (v$rowcache) and library cache hit ratio. Damagement usually loves statistics, the more meaningless it is, the more they love it. On 01/12/2004 04:19:34 PM, Niall

Re: Should we stop analyzing?

Don, Comments inline... Yes! IME, there ARE still problems in the CBO, especially with complex subqueries. I have more than a dozen systems where management insists on staying with the RBO! [TG]: With all due respect, what does management know about this stuff anyway? They do not work

RE: Should we stop analyzing?

exactly the same with steve trying to log a bug about x$ksmlru frits -Original Message- Sent: donderdag 8 januari 2004 20:59 To: Multiple recipients of list ORACLE-L Comment in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who

Re: Should we stop analyzing?

- Original Message - Wouldn't it be nice if dbms_stats could do an incremental refresh, tracking ONLY stats changes that might make a difference to execution plan: I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. I know which of

Re: Should we stop analyzing?

Yahwoll, mein herr! Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 6:09 AM Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it

Re: Should we stop analyzing?

Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC -

Re: Should we stop analyzing?

Thanks. Sounds SUPER! Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. snip Available in Oracle 10g - lock stats. -- Please see the official ORACLE-L FAQ:

RE: Should we stop analyzing?

Nice. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jonathan Lewis Sent: 09 January 2004 14:04 To: Multiple recipients of list ORACLE-L Subject: Re: Should we stop analyzing? Note in-line. Regards Jonathan Lewis http

Re: Should we stop analyzing?

Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial

Re: Should we stop analyzing?

- Original Message - The issue here is that very few people understand how the CBO works, or what the statistics do, or how to use them properly. And a seriously *big* component of that problem is that Oracle keeps changing/patching/modifying the CBO and how it reacts to certain

Re: Should we stop analyzing?

Hi Nuno, Do they clone the production database, change the statistics, prove that the system can complete it's batch job in 8 hours, then install ? I doubt it. Exactly my point above. Not feasible. So, what's the alternative? You hit the Nail on the head here, Nuno. The central

Re: Should we stop analyzing?

2- the CBO like any other piece of code, is sometimes buggy? Always, not sometimes Tanel. -- 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,

RE: Should we stop analyzing?

Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. Still it tries to make a better judgment .. Hey it is a whale lot better than those RDBMS

RE: Should we stop analyzing?

Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. At this point it would seem beneficial to differentiate between a bug and a logic error.

RE: Should we stop analyzing?

Right Jared, But this is where the new 'learning CBO' comes into picture isn't it? in 10g CBO looks at the history and then modified the execution plans. This is all from Oracle 10g propaganda sheets, I'll agree when I see it in action. Raj

RE: Should we stop analyzing?

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

RE: Should we stop analyzing?

Yeah, copped a copy of 10g new features from somewhere, but it was sorely lacking in detail. I seem to have missed the CBO bit. Jared On Thu, 2004-01-08 at 10:49, Jamadagni, Rajendra wrote: Right Jared, But this is where the new 'learning CBO' comes into picture isn't it? in 10g CBO looks

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

RE: Should we stop analyzing?

Of course! 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 -Original Message- Sent: Friday, 9 January 2004 6:29 AM To: Multiple recipients of list ORACLE-L And if

RE: Should we stop analyzing?

At 11:09 8-1-04 -0800, you wrote: 'Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it does! ' Says Pat -Al-Shahaf- Sherman, so it must be the truth, the whole truth and nothing but the truth Regards, Carel-Jan === If you think education is

Re: Should we stop analyzing?

Comment in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte

RE: Should we stop analyzing?

Then ask for the bug to be filed against the spec! ;-) Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Sent:

Re: Should we stop analyzing?

This opens a whole new can of worms. design bug specification bug 'get it out the door, now!' bug 'had a few too many porters when I wrote that bit' bug .. Jared On Thu, 2004-01-08 at 11:59, Jonathan Lewis wrote: - Original Message - Roughly, a bug would seem to be code that

Re: Should we stop analyzing?

ay, December 31, 2003 1:34 AM Subject: Re: Should we stop analyzing? This makes Oracle's position with 10g interesting, given that the default behaviour is to collect statistics all over the place automatically. If it's built in by the supplier, does it count as a change ? Jared's point is

RE: Should we stop analyzing?

Jared, I think your conclusions must have assumptions: 1) The SQL was written correctly. 2) The data structures wrer designed and layed out properly. Here, we can AssUMe neither. :) Food for thought... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED]

Re: Should we stop analyzing?

Wolfgang, First off, sorry for mangling your name in the previous post. I too will make notes inline. On Tue, 2003-12-30 at 22:14, Wolfgang Breitling wrote: 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

Re: Should we stop analyzing?

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

RE: Should we stop analyzing?

-Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 30, 2003 5:29 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Should we stop analyzing? If just 2, then from a users perspective, it would seem most appropriate to have

RE: Should we stop analyzing?

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

RE: Should we stop analyzing?

Jared, One problem is that the CBO sometimes CAN'T come up with the optimal execution plan. This could happen because it doesn't have all of the necessary data (i.e. histograms). There are also some types of data distribution that it ignores (see Wolfgang's paper at http://www.centrexcc.com/

Re: Should we stop analyzing?

Yes, it does say when dynamic sampling is evaluated or executed. 1st example is where dyn sampling is used: - SINGLE TABLE ACCESS PATH *** 2003-12-31 17:25:07.521 ** Performing dynamic sampling initial checks. ** ** Dynamic sampling initial checks returning TRUE (level = 4).

RE: Should we stop analyzing?

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

RE: Should we stop analyzing?

Mogens 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

Should we stop analyzing?

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

Re: Should we stop analyzing?

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

Re: Should we stop analyzing?

: Tuesday, December 30, 2003 5:34 AM 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

RE: Should we stop analyzing?

of list ORACLE-L 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

Re: Should we stop analyzing?

- Original Message - 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? As a regular thing, yes. Unless there is a clear case for doing it often: highly variable tables. And even then, I want

Re: Should we stop analyzing?

://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:34 AM 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

RE: Should we stop analyzing?

Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any

RE: Should we stop analyzing?

On one of our OLTP databases (designed in the dark ages, made-for-rbo database design), we have seen time and again that if we skip statistics collection for a day, queries go to the town. So, reluctantly we have to analyze (a 10% keeps the developer/CBO/Query trio happy). Raj

RE: Should we stop analyzing?

I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used

RE: Should we stop analyzing?

Mogens Nørgaard scribbled on the wall in glitter crayon: I'd like to know what practical and philosofical ideas you guys have on this topic. i think a lot of this depends on the optimizer. i know the cost biased one has improved dramatically since it was introduced. and i thought that the

Re: Should we stop analyzing?

however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. Or update HIVAL and DISTCNT and ROWCNT statistics using dbms_stats regularly... Tanel. -- Please see the

Re: Should we stop analyzing?

we are using dbms_stats, gather auto, for all indexed columns and estimate 15% Now if my other DBA would just show up for work, I can ask him about this. Sometimes being the early bird has disadvantages. I do know that when the analyze is not done, we have performance problems. Or at least the

RE: Should we stop analyzing?

I strongly suspect I'm missing something here, but I don't see a problem with gathering stale many times a day, every hour say. If your tables aren't subject to much DML activity then they won't be analysed anyway. On Tue, 2003-12-30 at 12:59, Rachel Carmichael wrote: I have you beat one

Re: Should we stop analyzing?

Hehehe! You rat! :D Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Or update HIVAL and DISTCNT and ROWCNT statistics using dbms_stats regularly... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat

RE: Should we stop analyzing?

Makes sense, BUT... If the data changes A LOT you should of course re-analyze. is assuming you know when that happens. You are assuming communication between users, developers, and DBAs. Communication is my New Year's Resolution. I would at least suggest exporting stats before changing them.

RE: Should we stop analyzing?

This is slightly OT ... Talking about exporting stats ... I do that and about 30 seconds ago finished writing a SQL that looks at a history of exported stats and displays a 7 day pattern of 1. rowcount changes 2. average row length change 3. allocated blocks changes basic treand analysis

RE: Should we stop analyzing?

Title: RE: Should we stop analyzing? I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say

Re: Should we stop analyzing?

Title: RE: Should we stop analyzing? In 9i you could use optimizer_dynamic_sampling for such "work" tables Tanel. - Original Message - From: Whittle Jerome Contr NCI To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 30, 2003 6:09 PM

RE: Should we stop analyzing?

I fold :) --- Whittle Jerome Contr NCI [EMAIL PROTECTED] wrote: I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when

Re: Should we stop analyzing?

That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T.

Re: Should we stop analyzing?

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

RE: Should we stop analyzing?

to get dynamic sampling one must specify that as a hint .. right? can cbo use dynamic sampling automatically on GTTs? (Hey, it's new year time and some wishful thinking is in order). Happy New Year. Raj

RE: Should we stop analyzing?

recipients of list ORACLE-L Subject: Re: Should we stop analyzing? That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer

Re: Should we stop analyzing?

there's also an optimizer_dynamic_sampling init parameter (in addition to hint) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 7:14 PM to get dynamic sampling one must specify that as a hint .. right? can

Re: Should we stop analyzing?

There is a hint, and there is a parameter. optimizer_dynamic_sampling = 2 is probably a good way of making sure that all queries involving GTTs get a dynamic sample of 32 blocks on the GTT Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who

RE: Should we stop analyzing?

Is there an easy way to track the rate of change in a particular table? -Original Message- Sent: Tuesday, December 30, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Makes sense, BUT... If the data changes A LOT you should of course re-analyze. is assuming you know when that

RE: Should we stop analyzing?

select count(*) on the PK each day and store the results for tracking. monitor the extent usage for the table. audit the table. [EMAIL PROTECTED] 12/30/2003 12:49:33 PM Is there an easy way to track the rate of change in a particular table? -Original Message- Sent: Tuesday, December

RE: Should we stop analyzing?

Thanks Jonathan,Tanel Some more clarification ... is dynamic sampling automatically used or one must specify the hint? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are

RE: Should we stop analyzing?

Interesting! Could this account for LOADS1 on pinned objects? Damn. Almost got thru the rest of the year without learning anything new. :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original

Re: Should we stop analyzing?

Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:59 PM Thanks

RE: Should we stop analyzing?

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

Re: Should we stop analyzing?

AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Should we stop analyzing? 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

RE: Should we stop analyzing?

[EMAIL PROTECTED] [EMAIL PROTECTED]cc: ott.af.mil Subject: RE: Should we stop analyzing

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

Re: Should we stop analyzing?

dynamic sampling. Tanel. - Original Message - From: "Jamadagni, Rajendra" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 12:24 AM Subject: RE: Should we stop analyzing? Tanel, I know the values

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

Re: Should we stop analyzing?

Wolgang, What I had in mind was simple DML, no patches, etc. Whether statistic are refreshed monthly, weekly, daily or every 2 hours, it doesn't make sense that this would create statistics that would be detrimental to performance, unless the data at the time the statistics are gathered is

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

Re: Should we stop analyzing?

This makes Oracle's position with 10g interesting, given that the default behaviour is to collect statistics all over the place automatically. If it's built in by the supplier, does it count as a change ? Jared's point is valid - in theory, if you keep statistics up to date, then the CBO should