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 questions about stats changes are:

    1- How can I list the changes to execution plans after re-analysis, A Priori?

    2 - How do I justify the risk (and server expense) of re-analyzing?

Some alternatives might be:

1 - Enhance the dbms_stats "auto" option (monitoring) to make it more intelligent.

     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:

1. Changes to clustering_factor
2. Changes to column skew. Only create histograms when column is skewed AND SQL uses the column. The 10g workload tool "claims" to do some of this.
3. Changes to highest-lowest values of key indexes, etc.

   2- Devise a method where new stats can be collected, stored and compared against historical SQL (from stats$sql_summary)

1. Allow for dbms_stats to collect, store and compare changes to historical execution plans, using historical SQL from STATSPACK (or new 10g workload views)
2. Allow the DBA control about whether to implement the new statistics

>

<cough>itwouldhavehelpediftheyhadfixedthecodebackin7/8/9insteadofdelivering
>

aseriesofpatchesasnewreleasesthattotallyconfusedeverybodyaboutitsoperation<c ough>

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!
Every time we collect deep stats and histogram and switch optimizer_mode, hundreds of statements generate poor plans. It would cost these clients many thousands of dollars to have adjusted these plans, and management says "If it ain't broke, why fix it".

We need look no further than Oracle Applications to see this issue. Oracle made a big-deal about going to the CBO in 11i, yet when we look at the SQL, a significant number of statement employ the "rule" hint! Connect-the-dots and you can guess why the RBO IS NOT being removed from Oracle10g. . . .

JMHO. . . . Regards,

Donald K. Burleson
www.dba-oracle.com
www.remote-dba.net

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 08, 2004 5:19 AM

> ----- 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 combinations of information, on EVERY SINGLE point
> version! It's virtually impossible for a typical DBA to find the
> time to fully test all the combinations and find out what works where
> and how. Hence why this type of thread becomes so useful.
>
> > 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
> given that:
>
> 1- the information in Oracle's own doco is far from complete or
> covering all bases?
>
> 2- the CBO like any other piece of code, is sometimes buggy?
>
> 3- You, Don, SA, and so many others who have the time to investigate
> this may in turn not have the time to produce the detailed doco that is
> needed to understand how the CBO works in all situations?
>
>
>
> Is dynamic sampling the solution? I don't think so: if anything, it will
> enhance/increase the problems caused by 1 and 2.
>
>
> > approach, while the 10g developers are pissed that the CBO has been
getting
> > a bum-rap because of crappy statistics.
>
>

<cough>itwouldhavehelpediftheyhadfixedthecodebackin7/8/9insteadofdelivering
>

aseriesofpatchesasnewreleasesthattotallyconfusedeverybodyaboutitsoperation<c ough>
>
> Cheers
> Nuno Souto
> nsouto_at_optusnet.com.au
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Nuno Souto
> INET: nsouto_at_optusnet.com.au
>
> 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: Don Burleson
  INET: don_at_burleson.cc

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