这篇文档是我们前leader wwf 写的分析文档,指出了incremental statistic针对partition table 的一些用法,并且指出了一些bug,看不了slideshare的TX可以直接下载Inc_Stat_For_PT.doc
针对这几个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中所有的问题均已解决
