| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS
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
>>> TJohnston_at_quallaby.com 02/25/03 07:49PM >>>
To expand on this, the action level is controlled by the granularity
parameter...
Granularity of statistics to collect (only pertinent if the table is partitioned).
DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather subpartition-level statistics.
PARTITION: Gather partition-level statistics.
GLOBAL: Gather global statistics.
So, in you case, run the following...
execute dbms_stats.gather_table_stats(ownname => 'LDGADMIN', -
tabname => 'LOG_TRANS', -
partname => 'LOG_TRANS_20030102',
estimate_percent => 5,
granularity => 'PARTITION');
See the supplied package reference for more details...
Tim
-----Original Message-----
Sent: Tuesday, February 25, 2003 4:50 PM
To: Multiple recipients of list ORACLE-L
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
> 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnston, Tim INET: TJohnston_at_quallaby.com 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). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: jhostetter_at_decommunications.com 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).Received on Wed Feb 26 2003 - 07:23:46 CST
 |
 |