How to copy partition stats? – We got easier way!
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.
- You add new partition(range) every week.
- You load massive amount of data to that partition.
- 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! :)
– 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
Toungc.
Yes, one of my collegue has pointed it out too.
Great way!
Dion Cho
April 16, 2009 at 9:25 pm
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
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
> 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
@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
[...] 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 [...]
Kerry Osborne’s Oracle Blog » Blog Archive I’ll Gladly Pay You Tuesday for a Hamburger Today - Kerry Osborne’s Oracle Blog
May 21, 2009 at 12:30 am
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
[...] http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/ [...]
dbms_stats.copy_table_stats does not alter low/high value « AskDba.org Weblog
June 24, 2009 at 2:39 pm
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
Thanks much for checking.
regards
—Raj
raj
December 2, 2010 at 3:13 am
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