The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition.
At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. 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: 25 February 2003 18:12 > I have never had good luck with DBMS_STATS. It seems that the old analyze runs > much faster..... > > Runs in 45 seconds: > analyze table log_trans partition (log_trans_20030104) estimate statistics > sample 5 percent; > > Takes over 2 hours: > execute dbms_stats.gather_table_stats(ownname => 'LDGADMIN', - > tabname => 'LOG_TRANS', - > partname => 'LOG_TRANS_20030102', - > estimate_percent => 5); > > Am I missing something? Aren't both commands the same? > > Thanks, > Tom > -- 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).