VMCD.ORG » Incremental Statistics for Partitioned Tables in 11g

这篇文档是我们前leader wwf 写的分析文档,指出了incremental statistic针对partition table 的一些用法,并且指出了一些bug,看不了slideshare的TX可以直接下载Inc_Stat_For_PT.doc

Incremental statistics for partitioned tables in 11 g from Louis liu

针对这几个bug问题在11gR2下的表现对这篇文档做了一个补充

第一个问题:It is only sensitive for newly load data partition

SQL> create table test_inc(a number, b date, c varchar2(30), d varchar2(100), e varchar2(100), partition_key number)
  2   partition by range(partition_key)
 (
    partition p00 values less than (1),
    partition p01 values less than (2),
    partition p02 values less than (3),
    partition p03 values less than (4),
    partition p04 values less than (5),
    partition p05 values less than (6),
    partition p06 values less than (7),
    partition p07 values less than (8),
    partition p08 values less than (9),
    partition p09 values less than (10),
    partition p10 values less than (11),
    partition p11 values less than (12),
    partition p12 values less than (13),
    partition p13 values less than (14),
    partition p14 values less than (15),
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19      partition p15 values less than (16),
    partition p16 values less than (17),
    partition p17 values less than (18),
    partition p18 values less than (19),
    partition p19 values less than (20),
    partition p20 values less than (21),
    partition p21 values less than (22),
    partition p22 values less than (23),
    partition p23 values less than (24),
    partition p24 values less than (25),
    partition p25 values less than (26),
    partition p26 values less than (27),
    partition p27 values less than (28),
    partition p28 values less than (29),
    partition p29 values less than (30),
    partition p30 values less than (31),
 20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35      partition pmax values less than(maxvalue)
 ) ;
 36  
Table created.

SQL> 
SQL> create table test_inc_bak(a number, b date, c varchar2(30), d varchar2(100), e varchar2(100), partition_key number)
  2     partition by range(partition_key)
   (
      partition p00 values less than (1),
      partition p01 values less than (2),
      partition p02 values less than (3),
      partition p03 values less than (4),
      partition p04 values less than (5),
      partition p05 values less than (6),
      partition p06 values less than (7),
      partition p07 values less than (8),
      partition p08 values less than (9),
      partition p09 values less than (10),
      partition p10 values less than (11),
      partition p11 values less than (12),
      partition p12 values less than (13),
      partition p13 values less than (14),
      partition p14 values less than (15),
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19        partition p15 values less than (16),
      partition p16 values less than (17),
      partition p17 values less than (18),
      partition p18 values less than (19),
      partition p19 values less than (20),
      partition p20 values less than (21),
      partition p21 values less than (22),
      partition p22 values less than (23),
      partition p23 values less than (24),
      partition p24 values less than (25),
      partition p25 values less than (26),
      partition p26 values less than (27),
      partition p27 values less than (28),
      partition p28 values less than (29),
      partition p29 values less than (30),
      partition p30 values less than (31),
 20   21   22   23   24   25   26   27   28        partition pmax values less than(maxvalue)
   );
 29   30   31   32   33   34   35   36  
Table created.

SQL>  select DBMS_STATS.get_PREFS('incremental', 'liu', 'test_inc') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','TEST_INC')
--------------------------------------------------------------------------------
FALSE

SQL>  exec DBMS_STATS.SET_TABLE_PREFS(user,'test_inc','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

SQL>  select DBMS_STATS.get_PREFS('incremental', 'liu', 'test_inc') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','TEST_INC')
--------------------------------------------------------------------------------
TRUE

SQL>  select DBMS_STATS.get_PREFS('incremental', 'liu', 'test_inc_bak') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','TEST_INC_BAK')
--------------------------------------------------------------------------------
FALSE

SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';

Session altered.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 0 from dual connect by rownum <= 300000;   2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 2 from dual connect by rownum <= 300000;    2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 3 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 4 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 5 from dual connect by rownum <= 300000;    2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc');

PL/SQL procedure successfully completed.

SQL>  select partition_name, blocks, num_rows, last_analyzed from user_tab_partitions where table_name = 'TEST_INC' and num_rows > 0;

PARTITION_NAME			   BLOCKS   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				     6928     300000 2012-11-07 23:17:42
P01				     6928     300000 2012-11-07 23:17:42
P02				     6802     300000 2012-11-07 23:17:43
P03				     7054     300000 2012-11-07 23:21:51
P04				     7054     300000 2012-11-07 23:21:50
P05				     6928     300000 2012-11-07 23:21:50

6 rows selected.

SQL> delete from test_inc partition(p05) where rownum <= 100000;

100000 rows deleted.

SQL> alter table test_inc truncate partition p04 ;

Table truncated.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
  rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC' and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				   300000	6928 2012-11-07 23:17:42
P01				   600000      14056 2012-11-07 23:22:59
P02				   300000	6802 2012-11-07 23:17:43
P03				   300000	7054 2012-11-07 23:21:51
P05				   200000	6928 2012-11-07 23:22:58

准确的判断了数据,插入同样数据到test_inc_bak;

SQL> insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
  2  rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 0 from dual connect by rownum <= 300000;  


insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  


insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 2 from dual connect by rownum <= 300000;   



insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 3 from dual connect by rownum <= 300000;   



insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 4 from dual connect by rownum <= 300000;   


insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 5 from dual connect by rownum <= 300000;     

300000 rows created.

SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL> SQL> SQL> SQL> 
SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc_bak');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC_BAK'and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				   300000	7054 2012-11-07 23:44:01
P01				   300000	7054 2012-11-07 23:44:01
P02				   300000	6928 2012-11-07 23:44:01
P03				   300000	7054 2012-11-07 23:44:02
P04				   300000	7054 2012-11-07 23:44:02
P05				   300000	7054 2012-11-07 23:44:02

6 rows selected.

SQL>  delete from test_inc_bak partition(p05) where rownum <= 100000;

100000 rows deleted.

SQL> alter table test_inc_bak truncate partition p04 ;

Table truncated.

SQL> 
SQL> insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
  rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc_bak');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC_BAK'and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				   300000	7054 2012-11-07 23:45:20
P01				   600000      14194 2012-11-07 23:45:21
P02				   300000	6928 2012-11-07 23:45:21
P03				   300000	7054 2012-11-07 23:45:22
P05				   200000	7054 2012-11-07 23:45:22

可以看到所有的分区重新被分析了一次

问题二：How about I truncate all tables? in wwf’s situation,even he truncated all table but still got no analyzed.

SQL> truncate table test_inc; 

Table truncated.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC' and num_rows > 0;

no rows selected

OK In 11gR2 这个问题已经被解决。

问题三：For subpartition table, it is totally wrong We got different result querying user_tab_subpartitions and user_tab_partitions

SQL> CREATE TABLE composite_rng_list (
  2     cust_id     NUMBER(10),
   cust_name   VARCHAR2(25),
   cust_state  VARCHAR2(2),
   time_id     DATE)
   PARTITION BY RANGE(time_id)
   SUBPARTITION BY LIST (cust_state)
   SUBPARTITION TEMPLATE(
   SUBPARTITION west VALUES ('OR', 'WA'),
   SUBPARTITION east VALUES ('NY', 'CT'),
   SUBPARTITION cent VALUES ('OK', 'TX')) (
   PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2008','DD/MM/YYYY')),
   PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
   PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY')),
   PARTITION future VALUES LESS THAN(MAXVALUE));  3    4    5    6    7    8    9   10   11   12   13   14   15  

Table created.



SQL>  exec dbms_stats.set_table_prefs('liu', 'composite_rng_list', 'incremental', 'true')  ;

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('incremental', 'liu', 'composite_rng_list') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','COMPOSITE_RNG_LIST')
--------------------------------------------------------------------------------
TRUE

SQL>  insert into composite_rng_list select rownum, 'customer'||rownum, 'OR', to_date('2007-01-01', 'yyyy-mm-dd') from dual connect by rownum <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'COMPOSITE_RNG_LIST';

TABLE_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
COMPOSITE_RNG_LIST

SQL> exec dbms_stats.gather_table_stats(user, 'COMPOSITE_RNG_LIST', granularity=>'ALL')

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'COMPOSITE_RNG_LIST';

TABLE_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
COMPOSITE_RNG_LIST		   100000	 494 2012-11-08 00:16:50

SQL> insert into composite_rng_list select rownum, 'customer'||rownum, 'WA', to_date('2007-01-01', 'yyyy-mm-dd') from dual connect by rownum <= 100000;

100000 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 'COMPOSITE_RNG_LIST', granularity=>'ALL')

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'COMPOSITE_RNG_LIST';

TABLE_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
COMPOSITE_RNG_LIST		   200000	1006 2012-11-08 00:17:24


SQL> select partition_name, SUBPARTITION_NAME, num_rows, blocks, last_analyzed from user_tab_subpartitions where table_name = 'COMPOSITE_RNG_LIST' and num_rows > 0;

PARTITION_NAME		       SUBPARTITION_NAME		NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -------------------
PER1			       PER1_WEST			  200000       1006 2012-11-08 00:17:23

SQL>  select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'COMPOSITE_RNG_LIST' and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
PER1				   200000	1006 2012-11-08 00:17:24

可以看到在11gR2中所有的问题均已解决