RE: DBMS_STATS

Thanks to Tim Johnston and Jonathan Lewis. They were exactly right!

Tom

-----Original Message-----
Sent: Tuesday, February 25, 2003 7:49 PM To: Multiple recipients of list ORACLE-L

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" <ORACLE-L_at_fatcity.com> 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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Terrian, Tom (Contractor) (DAASC)
  INET: Tom.Terrian_at_dla.mil

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