RE: dbms_stats.gather_table_stats
- From: <genegurevich@xxxxxxxxxxxx>
- To: ineyman@xxxxxxxxxxxxxx
- Date: Fri, 5 Sep 2008 14:42:08 -0500
Igor,
I ran this query:
select table_name, partition_name, last_analyzed from dba_tab_partitions
where table_name=
It did show every partition as being analyzed when this command -
dbms_stats.gather_table_stats( 'schema', 'table',null, 25, degree=>8,
cascade=>true ); - ran the last time
SO I'm pretty sure that it does go over every partition.
thank you
Gene Gurevich
"Igor Neyman"
<ineyman@perceptr
on.com> To
Sent by: <genegurevich@xxxxxxxxxxxx>
oracle-l-bounce@f cc
reelists.org <oracle-l@xxxxxxxxxxxxx>
Subject
RE: dbms_stats.gather_table_stats
09/05/2008 02:31
PM
Please respond to
ineyman@perceptro
n.com
Gene,
>collects stats for the whole table.
That's what I meant. Did you check, if separate partitions have their
statistics collected with this command? Probably not.
Analyzing table (as a whole) and analyzing individual partitions is not
the same.
Look into USER_TAB_PARTITIONS and see if individual partitions have
statistics collected for them with your first command.
Igor
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxx
Sent: Friday, September 05, 2008 3:20 PM
To: Igor Neyman
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: dbms_stats.gather_table_stats
Igor,
My first command - dbms_stats.gather_table_stats( 'schema', 'table',
null, 25, degree=>8, cascade=>true ); - collects stats for the whole
table.
I have replaced it with these two commands
-
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 );
- to only collect the stats on 2 partitions that may have been updated
since the last run (instead of analyzing all 7 partitions).
The first command ran in about 24 min. Two bottom commands ran in 26
minutes combined. What I don't understand is why analyzing the whole
table took the same time as analyzing 2 partitions (out of 7)
thank you
Gene Gurevich
"Igor Neyman"
<ineyman@perceptr
on.com>
To
<genegurevich@xxxxxxxxxxxx>,
09/05/2008 02:14 <oracle-l@xxxxxxxxxxxxx>
PM
cc
Subject
RE: dbms_stats.gather_table_stats
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxx
Sent: Friday, September 05, 2008 3:01 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
