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
>>> [EMAIL PROTECTED] 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
----- 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johnston, Tim
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).
**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: [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).
