Posts Tagged ‘dbms_stats’
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! :)
Dion Cho - Oracle Performance Storyteller 