Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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! :)

About these ads

Written by Dion Cho

February 12, 2009 at 5:44 am

Posted in Optimizer

Tagged with ,

14 Responses

Subscribe to comments with RSS.

  1. – 4 steps copy partition stats workaorund before 10.2.0.4

    – 1. create a table to hold stats
    exec sys.dbms_stats.create_stat_table(‘SYSADM’,’TABLESTATS’,’D_SYSADM_M’);

    – 2. partition stats export
    exec sys.dbms_stats.export_table_stats(‘SYSADM’,’ORDERHDR_ALL’,’ORDERHDR_ALL_P88′,’TABLESTATS’);

    – 3. manual copy step :)
    update TABLESTATS set c2=replace(c2,’88′,’90′);
    commit;

    – 4. partition stats import
    exec sys.dbms_stats.import_table_stats(‘SYSADM’,’ORDERHDR_ALL’,’ORDERHDR_ALL_P90′,’TABLESTATS’);

    H.Tonguç Yılmaz

    April 16, 2009 at 1:45 pm

  2. Toungc.

    Yes, one of my collegue has pointed it out too.

    Great way!

    Dion Cho

    April 16, 2009 at 9:25 pm

  3. not so great because of histograms, but it may be another workaround for some situations.

    H.Tonguç Yılmaz

    April 17, 2009 at 4:34 am

  4. One of the problems of copying partition stat is how to handle min/max value and histogram.

    I know how to manually set min/max value and histogram using dbms_stats package(as demostrated in the post), but have no knowledge of how to do it on stats table. More research in neeed.

    Dion Cho

    April 17, 2009 at 4:45 am

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

    Additionally, I am afraid this procedure is “supported” before 11g, here is a comment from spec;

    – THE FOLLOWING PROCEDURE IS FOR INTERNAL USE ONLY.
    – copy stats for one partition to another
    procedure copy_table_stats(
    ownname varchar2,
    tabname varchar2,
    srcpartname varchar2,
    dstpartname varchar2,
    flags number DEFAULT null,
    force boolean DEFAULT FALSE);

    H.Tonguç Yılmaz

    April 21, 2009 at 3:09 pm

  6. @Toungc.

    Thanks for the info.

    I’ve checked my local databases and found that we have dbms_stats.copy_table_stats(internally) even from 10gR1.

    It seems that it is officially documented and published as of 10.2.0.4.

    What a shame I didn’t know it.

    Dion Cho

    April 21, 2009 at 11:10 pm

  7. [...] of dbms_stats.copy_table_stats and dbms_stats.set_table_stats and dbms_stats.set_column_stats (see Dion Cho’s excellent post for details and example code – and also this post on the Optimizermagic Blog for further info). I [...]

  8. After creating table and adding partition p4 if I try to copy stats from partition p1 to p4 it doesn’t work unless I collect stats at partition level.

    Thanks
    Rajeev

    Rajeev

    May 28, 2009 at 6:36 pm

  9. Thanks for the post…It was useful..Googling took me here. Just wanted a small thing. Is there a column in any of the dictionary views or otherwise to know if the statistics on a partition is by mere copy or being gathered.

    TIA.
    —Raj

    raj

    December 1, 2010 at 8:44 am

    • Hi Raj,

      I searched docs and view defintions but, looks like we don’t have any column for that.

      Dion Cho

      December 2, 2010 at 1:14 am

  10. Thanks much for checking.
    regards
    —Raj

    raj

    December 2, 2010 at 3:13 am

  11. Hi ,

    Is it taking care of high value and low value by it self.?

    Or do we have to set it mannualy.
    Thanks & Regards,
    Prasad

    Prasad

    December 15, 2010 at 3:30 pm

    • High value and low value are automatically adjusted by the procedure, but it’s also known that some versions have bugs failing at that.

      You would need to check it yourself on your platform.

      Dion Cho

      December 16, 2010 at 12:21 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: