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" <[EMAIL PROTECTED]> 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: [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).
