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).
