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
 April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

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

>> When we consider that re-analyzing stats can cause huge changes to data
access patterns I'm continuously amazed at the number of shops >> that re-analyze on a schedule and have the "Monday Morning" syndrome.

The issue here is that very few people understand how the CBO works, or what the statistics do, or how to use them properly. If someone came to me and said:

    "I'd like to inject some random numbers into the database     every Monday morning"
I'd insist on proof of concept and rigid change control for evey set of random numbers

Is some came to me and said:

    "I need to keep the meta-data synchronised with the     data, and install a routine to adjust certain components     of the meta-data that the database cannot derive     automatically"
I'd ask for one proof of concept, and a one-off change control.

>> I have worked for shops where they must "certify" every change, no matter
how trivial. Mostly banks and medical systems.

So they have a difficult choice to make when the data changes sufficiently to
make the out of date statistics a disaster and NEED to correct the statistics.
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.

>> These "certified" shops are stuck. On one hand, they are obligated to
follow the best-practices of their vendor, yet obligated not to make any >> untested changes in production.

>> Even Oracle is schizophrenic on the issue; my contacts in the real-world
performance group are zealously in favor of the "take one deep >> sample" approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics.

Can you ask them what their approach is towards monotonic increasing values in columns, and the side-effects of the low/high basis for selectivity ?

>> Personally, I love the automatic histogram generation "skewonly" and the
"auto" option in dbms_stats, and use it for all my 9ir2 clients.
>> However, I remain skeptical about the benefits of "dynamic sampling" and
"workload analysis" automation tools for most shops.

These two statements aren't entirely compatible. the skewonly and auto options are driven by built-in dynamic sampling and workload analysis automation tools. There's no very good reason why Oracle can build the only such tools that make sense - and in fact, it is arguable that a 3rd party may have a more general view of how these types of tools need to work because Oracle Corp tends to focus at two extremes - the very tiny (lab experiment) or the huge (big companies and TPC).

>> In my experience, the vast majority of shops DO NOT benefit from
re-analysis, and I've got shops where re-analysis NEVER results in CBO >> changes.

But sometimes the re-analysis NEVER results in CBO changes because failure to re-analyze WOULD result in a detrimental CBO change. (Actually, re-analysis almost always results in CBO changes if the data has changed, but hardly anyone looks at the actual stats stored in user_table, user_indexes, user_tab_columns etc.....)

Regards,

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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