Re: dbms_stats.gather_table_stats

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: genegurevich@xxxxxxxxxxxx
  • Date: Fri, 05 Sep 2008 14:07:26 -0600

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@xxxxxxxxxxxx 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


--
//www.freelists.org/webpage/oracle-l


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2008