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/ "Fallacies of the Cost Based Optimizer"). For example, if two fields within a table, or across two tables are dependent, the optimizer won't know or use this information. What the CBO thinks is the best path based on estimated cardinalities can be way off. By accident, an inefficient execution plan (as seen by the CBO) might actually be more efficient than the CBO's optimal choice. Analyzing can change these plans even if nothing is broken.

Henry

-----Original Message-----
Jared Still
Sent: Wednesday, December 31, 2003 12:29 AM To: Multiple recipients of list ORACLE-L

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 substantially different than at the time of usage. eg. stats gathered on a table when it has a few blocks allocated during a batch job, but later grows to few hundred thousand blocks prior to users hitting the system.

Granted, it may be unnecessary continually analyze: as I stated earlier, this is the first time I've seen this discussed, so maybe I'm missing some important point about it that hasn't sunk in yet.

MetaLink document 44961.1 supports infrequent analyzing, though it conveniently fail to define 'frequent'.

If my data changes, and I analyze it, CBO should still find reasonable execution paths for the current data.

If my data does not change, and I analyze it, CBO should have the same set of statistics as it did previously.

Is that not true, or is there some other piece missing here?

Jared

On Tue, 2003-12-30 at 15:44, Wolfgang Breitling wrote:
>
> 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: breitliw_at_centrexcc.com
>
> 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: ListGuru_at_fatcity.com (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: Jared Still
  INET: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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: Henry_Poras_at_dfci.harvard.edu

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: ListGuru_at_fatcity.com (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).