Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘dbms_stats

How to copy partition stats? – We got easier way!

with 14 comments

This post from Oracle optimizer team blog made me realize that Oracle now provides very easy way to copy partition stats.

dbms_stats.copy_table_stats

This procedure is supported from 10.2.0.4 and of course 11g.

Why is this procedure so important? Imagine following situation.

  1. You add new partition(range) every week.
  2. You load massive amount of data to that partition.
  3. Then you gather partition statistics for this new partition.

Without step 3, you don’t have up-to-date partition statistics. But this step is sometimes quite heavy that many DBAs try to avoid it. All they want is just to copy all the basic statistics(like distinct count, density, …) from previous(last week) statistics and update some of the crucial values like minimum and maximum value. The default minimum and maximum value would be the range of partiton key.

What is the easies way to achieve this? Here we have dbms_stats.copy_table_stats.

Simple test case:

1. Create objects – range partitioned table with 3 partitions.

UKJA@ukja116> create table t1(c1, c2)
  2  partition by range (c1) (
  3    partition p1 values less than (1000),
  4    partition p2 values less than (2000),
  5    partition p3 values less than (3000)
  6  )
  7  as
  8  select level-1, level
  9  from dual
 10  connect by level <= 3000
 11  ;

Table created.

UKJA@ukja116>
UKJA@ukja116> exec dbms_stats.gather_table_stats(user, 't1', granularity=>'all');

PL/SQL procedure successfully completed.

It has up-to-date partition statistics.

PARTITION_NAME                : P3                                                                 
COLUMN_NAME                   : C1                                                                 
NUM_DISTINCT                  : 1000                                                               
DENSITY                       : .001                                                               
LOW_VALUE                     : C215                                                               
HIGH_VALUE                    : C21E64                                                             
HISTOGRAM                     : NONE                                                               
-----------------                                                                                   

PARTITION_NAME       COLUMN_NAME          BUCKET_NUMBER                                            
-------------------- -------------------- -------------                                            
ENDPOINT_VALUE                                                                                     
----------------------------------------------------------------------------------------------------
P3                   C1                               0                                            
2000()                                                                                              

P3                   C1                               1                                            
2999()

2. Now we add partition 4 whose range is 3000~3009.

                                                                                                                                                                                                   
UKJA@ukja116> alter table t1
  2  add partition p4 values less than (4000)
  3  ;

Table altered.

UKJA@ukja116>
UKJA@ukja116> insert into t1
  2  select level+3000-1, level
  3  from dual
  4  connect by level <= 1000
  5  ;

1000 rows created.

Since we didn’t gather partition statistics for partition 4, we have no up-to-date statistics. This is a problem, right?

PARTITION_NAME                : P4                                                                 
NUM_ROWS                      :                                                                    
BLOCKS                        :                                                                    
EMPTY_BLOCKS                  :                                                                    
SAMPLE_SIZE                   :                                                                    
LAST_ANAL                     :                                                                    
-----------------                                                                                   

PARTITION_NAME                : P4                                                                 
COLUMN_NAME                   : C1                                                                 
NUM_DISTINCT                  :                                                                    
DENSITY                       :                                                                    
LOW_VALUE                     :                                                                    
HIGH_VALUE                    :                                                                    
HISTOGRAM                     : NONE                                                               
-----------------

3. The only way would be to gather statistics on partition 4.

UKJA@ukja116> exec dbms_stats.gather_table_stats(user, 't1', 'p4');

PARTITION_NAME                : P4                                                                 
NUM_ROWS                      : 1000                                                               
BLOCKS                        : 5                                                                  
EMPTY_BLOCKS                  : 0                                                                  
SAMPLE_SIZE                   : 1000                                                               
LAST_ANAL                     : 2009/02/12 13:47:58                                                
-----------------                                                                                   

PARTITION_NAME                : P4                                                                 
COLUMN_NAME                   : C1                                                                 
NUM_DISTINCT                  : 1000                                                               
DENSITY                       : .001                                                               
LOW_VALUE                     : C21F                                                               
HIGH_VALUE                    : C22864                                                             
HISTOGRAM                     : NONE                                                               
-----------------                                                                                   

PARTITION_NAME       COLUMN_NAME          BUCKET_NUMBER                                            
-------------------- -------------------- -------------                                            
ENDPOINT_VALUE                                                                                     
----------------------------------------------------------------------------------------------------
P4                   C1                               0                                            
3000()                                                                                              

P4                   C1                               1                                            
3999()

4. Now let’s consider another easy and light way – dbms_stats.copy_table_stats

UKJA@ukja116> alter table t1
  2  add partition p5 values less than (5000)
  3  ;

Table altered.

UKJA@ukja116>
UKJA@ukja116> exec dbms_stats.copy_table_stats(user, 't1', -
>   srcpartname=>'p4', dstpartname=>'p5');

PL/SQL procedure successfully completed.

PARTITION_NAME                : P5                                                                 
NUM_ROWS                      : 1000                                                               
BLOCKS                        : 5                                                                  
EMPTY_BLOCKS                  : 0                                                                  
SAMPLE_SIZE                   : 1000                                                               
LAST_ANAL                     : 2009/02/12 13:47:58                                                
-----------------                                                                                   

PL/SQL procedure successfully completed.

PARTITION_NAME                : P5                                                                 
COLUMN_NAME                   : C1                                                                 
NUM_DISTINCT                  : 1000                                                               
DENSITY                       : .001                                                               
LOW_VALUE                     : C229                                                               
HIGH_VALUE                    : C233                                                               
HISTOGRAM                     : NONE                                                               
-----------------                                                                                   

PARTITION_NAME       COLUMN_NAME          BUCKET_NUMBER                                            
-------------------- -------------------- -------------                                            
ENDPOINT_VALUE                                                                                     
----------------------------------------------------------------------------------------------------
P5                   C1                               0                                            
4000()                                                                                              

P5                   C1                               1                                            
5000()

Very cool. Just calling a simple procedure relieved us from the heavy burden of statistics gathering. Love it!

5. What if we have Oracle < 10.2.0.4? Manual copying is another way – looks like complex but is actually beautiful!

UKJA@ukja116> alter table t1
  2  add partition p6 values less than (6000)
  3  ;

Table altered.

UKJA@ukja116>
UKJA@ukja116>
UKJA@ukja116> declare
  2    v_srec     dbms_stats.statrec;
  3    v_numrows number;
  4    v_numblks number;
  5    v_avgrlen number;
  6    v_density number;
  7    v_distcnt number;
  8    v_nullcnt number;
  9    v_avgclen number;
 10  begin
 11 
 12    dbms_stats.get_table_stats(
 13       ownname=>user,
 14       tabname=>'t1',
 15       partname=>'p5',
 16       numrows=>v_numrows,
 17       numblks=>v_numblks,
 18       avgrlen=>v_avgrlen
 19    );
 20 
 21    dbms_stats.set_table_stats(
 22       ownname=>user,
 23       tabname=>'t1',
 24       partname=>'p6',
 25       numrows=>v_numrows,
 26       numblks=>v_numblks,
 27       avgrlen=>v_avgrlen
 28    );
 29 
 30    dbms_stats.get_column_stats(
 31       ownname=>user,
 32       tabname=>'t1',
 33       colname=>'c1',
 34       partname=>'p5',
 35       distcnt=>v_distcnt,
 36       density=>v_density,
 37       nullcnt=>v_nullcnt,
 38       srec=>v_srec,
 39       avgclen=>v_avgclen
 40    );
 41 
 42    v_srec.minval := utl_raw.cast_from_number(5000);
 43    v_srec.maxval := utl_raw.cast_from_number(5999);
 44 
 45    dbms_stats.set_column_stats(
 46       ownname=>user,
 47       tabname=>'t1',
 48       partname=>'p6',
 49       colname=>'c1',
 50       distcnt=>v_distcnt,
 51       density=>v_density,
 52       nullcnt=>v_nullcnt,
 53       avgclen=>v_avgclen,
 54       srec=>v_srec
 55    );
 56  end;
 57  /

PL/SQL procedure successfully completed.

PARTITION_NAME                : P6                                                                 
NUM_ROWS                      : 1000                                                               
BLOCKS                        : 5                                                                  
EMPTY_BLOCKS                  : 0                                                                  
SAMPLE_SIZE                   : 2000                                                               
LAST_ANAL                     : 2009/02/12 13:47:58                                                
-----------------                                                                                   

PARTITION_NAME                : P6                                                                 
COLUMN_NAME                   : C1                                                                 
NUM_DISTINCT                  : 1000                                                               
DENSITY                       : .001                                                               
LOW_VALUE                     : C233                                                               
HIGH_VALUE                    : C23C64                                                             
HISTOGRAM                     : NONE                                                               
-----------------                                                                                   

PARTITION_NAME       COLUMN_NAME          BUCKET_NUMBER                                            
-------------------- -------------------- -------------                                            
ENDPOINT_VALUE                                                                                     
----------------------------------------------------------------------------------------------------
P6                   C1                               0                                            
4000()                                                                                              

P6                   C1                               1                                            
5000()

Thank you the developers of Oracle for providing this brand-new powerful procedure – copy_table_stats! God bless you! :)

Written by Dion Cho

February 12, 2009 at 5:44 am

Posted in Optimizer

Tagged with ,

Follow

Get every new post delivered to your Inbox.

Join 58 other followers