Re: dbms_stats.gather_table_stats
Date: Sat, 6 Sep 2008 11:12:18 -0400 (EDT)
Message-Id: <200809052007.m85K7Tje029283@mail95c0.megamailservers.com>
Read the docs.
gather_table_stats has a parameter "granularity" which in 10g
defaults to "AUTO" - unless you've changed the default with the
set_param procedure. Auto does essentially the same as the 9i default
of "DEFAULT' did: If you don't specify a partition name it gathers
statistics for the entire table, global statistics and all partition
statistics. If you specify a partition it gathers global statistics
and statistics for the named partition. If you want to gather
statistics for only the named partition you have to specify
"granularity=>'PARTITION' "
It's the gathering of the global statistics which takes the bulk of
the time of the gather_table_stats. You don't save much if you
specify just a partition name without also choosing
granularity=>'partition' - as you found out.
At 01:00 PM 9/5/2008, genegurevich_at_discover.com wrote:
>Hi everybody:
>
>I am running oracle 10.2.0.1. Once a week we have a analyze process run to
>gather stats on all the
>tables in the schema via commands like this one exec
>dbms_stats.gather_table_stats( 'schema', 'table', null, 25,
>degree=>8, cascade=>true );
>
>Some of the tables are partitioned and we have 6 or 7 historical partitions
>which are not being updated anymore
>so I thought I would save time by restricting this command to only last two
>partitions:
>
>exec dbms_stats.gather_table_stats( 'owner', 'table', 'part1', 25,
>degree=>8, cascade=>true );
>exec dbms_stats.gather_table_stats( 'owner', 'table', 'part2', 25,
>degree=>8, cascade=>true );
>
>The partitions are roughly the same size. The indices are globally
>partitioned. The first command (analyze
>the whole table) took 25 minutes. Last two command combined - 27 min.
>Considering that there are 5 more patitions
>in the table, the math does not add up. What am I missing?
>
>
>thank you
>
>Gene Gurevich
>
>
>--
>http://www.freelists.org/webpage/oracle-l
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 06 2008 - 10:12:18 CDT