RE: dbms_stats.gather_table_stats
Date: Fri, 5 Sep 2008 15:14:38 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2036D530B@EXCHANGE.corp.perceptron.com>
Are you sure, that your first command collects statistics for separate
partitions (and not just for the whole table)? Did you check partitions
statistics?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
genegurevich_at_discover.com
Sent: Friday, September 05, 2008 3:01 PM
To: oracle-l_at_freelists.org
Subject: dbms_stats.gather_table_stats
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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 05 2008 - 14:14:38 CDT