RE: dbms_stats.gather_table_stats

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_at_perceptr                                             
             on.com>                                                    To 
             Sent by:                  <genegurevich_at_discover.com>         
             oracle-l-bounce_at_f                                          cc 
             reelists.org              <oracle-l_at_freelists.org>            
                                                                   Subject 
                                       RE: dbms_stats.gather_table_stats   
             09/05/2008 02:31                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             ineyman_at_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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Friday, September 05, 2008 3:20 PM To: Igor Neyman
Cc: oracle-l_at_freelists.org
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_at_perceptr

             on.com>
To

                                       <genegurevich_at_discover.com>,

             09/05/2008 02:14          <oracle-l_at_freelists.org>

             PM

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_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



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






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