There is no generic answer to that question. Global Stats on partitioned tables do take a long time to collect - and you do need them to be reasonable because any query that the optimizer cannot pin down to exactly one partition at parse time is optimised according to the global stats.
The best bet is to cheat. If you know your data you can analyze the partitions, but use dbms_stats.set_table_stats et. al. to set the global stats. Run dbms_stats with sql_trace switched on to see what happens with the 'exotic' options. I think Connor has some comments on his website - www.oracledba.co.uk - but the bottom line is "don't be surprised if Oracle wastes computer resources when trying to automatic the jobs that require you to know the data". Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August 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: 26 February 2003 13:23 > When is the best time to gather Global Stats? Using the old ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats. > Oracle recommends deleting global stats on partitioned tables in 11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i? > Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time. > > Thanks, > > > Jay Hostetter > Oracle DBA > D. & E. Communications > Ephrata, PA USA > -- 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).
