下面是测试手动收集配置增量统计的表的统计信息。
1，版本信息
|
www.htz.pw > select * from v$version;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production |
2，脚本内容
|
— cre_db_stats.sql begin — — gather DB stats set echo on prompt * * * Hit ENTER! * * * PAUSE exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true); — cre_db_stats.sql end —
— d_stats_ex.sql begin — — Query stats set echo on
select partition_name,sample_size,num_rows, LAST_ANALYZED from dba_tab_partitions where table_owner=user and table_name=’PARTTABLE’ order by partition_name;
select table_name,sample_size,num_rows, LAST_ANALYZED from dba_tables where owner=user and table_name=’PARTTABLE’;
select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED , SAMPLE_SIZE, AVG_COL_LEN from dba_tab_columns where owner=user and table_name=’PARTTABLE’ order by column_id; — d_stats_ex.sql end — — ex_auto_inc1.sql begin — set echo on alter session set nls_date_format=’DD-MM-YYYY HH24:MI:SS’; drop table PARTTABLE purge; CREATE TABLE PARTTABLE ( col1 DATE, col2 number, col3 number, constraint pk_parttable primary key (col1,col2) ) PARTITION BY RANGE (col1) (PARTITION PARTTABLE_1995 VALUES LESS THAN (TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1996 VALUES LESS THAN (TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1997 VALUES LESS THAN (TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1998 VALUES LESS THAN (TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1999 VALUES LESS THAN (TO_DATE(’01-JAN-2000′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)) );
— 配置表增量统计信息的前提，下面4个条件，决一不同。 begin dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘ESTIMATE_PERCENT’, DBMS_STATS.AUTO_SAMPLE_SIZE); dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘INCREMENTAL’, ‘TRUE’); dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘PUBLISH’, ‘TRUE’); dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘GRANULARITY’, ‘AUTO’); end; /
insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum,rownum*2 from dba_objects where rownum<101);
insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);
insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);
insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum*rownum,rownum*3 from dba_objects where rownum<101); commit; exec dbms_stats.flush_database_monitoring_info; –查看表上面更行的行数统计 @table_modification.sql — 删除表上的统计信息 exec dbms_stats.delete_table_stats(ownname=>user, tabname=>’PARTTABLE’, cascade_columns=>true, cascade_indexes=>true); — 查看表与列的统计信息 start d_stats_ex — 自动作业收集统计信息 start cre_db_stats start d_stats_ex — dba_tab_modification记录已经被清空 @table_modification.sql exec dbms_lock.sleep(2); — 向表空间插入数据，但是小于表总行数的10% insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<40); commit; exec dbms_stats.flush_database_monitoring_info; — 这里可以看到表与1998分区上分别INSERT了39条记录 @table_modification.sql –收集统计信息， start cre_db_stats — 这里可以看到表与1998分区的统计信息发生了变化，其它的分区并没有变化。 start d_stats_ex
–还回空行 @table_modification.sql –分区1997插入10行小于此分区的10%，1996分区插入1行，表上小于10% exec dbms_lock.sleep(2); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+101,rownum*2 from dba_objects where rownum<2); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+200,rownum*2 from dba_objects where rownum<11); commit; @table_modification.sql start cre_db_stats –这里应该是可以看见2个分区与的统计信息发生变化，其它的分区信息没有变化 。 start d_stats_ex –这里应该是空 @table_modification.sql –下面是每一个分区(除1998)插入一行数据，总修改行小于10%， exec dbms_lock.sleep(2); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*2 from dba_objects where rownum<2); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2); commit; @table_modification.sql start cre_db_stats –除1998分区外的所有的分区与表的的统计信息变化了变化，说明所有发生DML操作的分区都发生了统计信息变化，不管是否满足10% start d_stats_ex –每个分区变化为1，除1998分区 @table_modification.sql exec dbms_lock.sleep(2); –向分区分别插入行，总插入行数大于表的10%，其中一个分区的插入行数大于10%，一个分区不插入数据，其它分区小于10%， insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<50); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9); insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9); commit; @table_modification.sql start cre_db_stats — 这里可以发现除1998分区外，表与分区的统计信息都发生了变化。 start d_stats_ex — ex_auto_inc1.sql end — |
3，脚本结果
|
www.htz.pw > @/tmp/ex_auto_inc1.sql www.htz.pw > — ex_auto_inc1.sql begin — www.htz.pw > set echo on www.htz.pw > alter session set nls_date_format=’DD-MM-YYYY HH24:MI:SS’;
Session altered.
www.htz.pw > drop table PARTTABLE purge;
Table dropped.
www.htz.pw > CREATE TABLE PARTTABLE 2 ( col1 DATE, 3 col2 number, 4 col3 number, 5 constraint pk_parttable primary key (col1,col2) 6 ) 7 PARTITION BY RANGE (col1) 8 (PARTITION PARTTABLE_1995 VALUES LESS THAN (TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), 9 PARTITION PARTTABLE_1996 VALUES LESS THAN (TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), 10 PARTITION PARTTABLE_1997 VALUES LESS THAN (TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), 11 PARTITION PARTTABLE_1998 VALUES LESS THAN (TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), 12 PARTITION PARTTABLE_1999 VALUES LESS THAN (TO_DATE(’01-JAN-2000′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)) 13 );
Table created.
www.htz.pw > — 配置表增量统计信息的前提，下面4个条件，决一不同。 www.htz.pw > begin 2 dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘ESTIMATE_PERCENT’, DBMS_STATS.AUTO_SAMPLE_SIZE); 3 dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘INCREMENTAL’, ‘TRUE’); 4 dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘PUBLISH’, ‘TRUE’); 5 dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘GRANULARITY’, ‘AUTO’); 6 end; 7 /
PL/SQL procedure successfully completed.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum,rownum*2 from dba_objects where rownum<101);
100 rows created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);
100 rows created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);
100 rows created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum*rownum,rownum*3 from dba_objects where rownum<101);
100 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
www.htz.pw > –查看表上面更行的行数统计 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
OWNER TABLE_NAME PART SUBPART INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————- SCOTT PARTTABLE 400 0 0 2015-02-09 12:47:55 NO 0 SCOTT PARTTABLE PARTTABLE_1995 100 0 0 2015-02-09 12:47:55 NO 0 SCOTT PARTTABLE PARTTABLE_1996 100 0 0 2015-02-09 12:47:55 NO 0 SCOTT PARTTABLE PARTTABLE_1997 100 0 0 2015-02-09 12:47:55 NO 0 SCOTT PARTTABLE PARTTABLE_1999 100 0 0 2015-02-09 12:47:55 NO 0
www.htz.pw > — 删除表上的统计信息 www.htz.pw > exec dbms_stats.delete_table_stats(ownname=>user, tabname=>’PARTTABLE’, cascade_columns=>true, cascade_indexes=>true);
PL/SQL procedure successfully completed.
www.htz.pw > — 查看表与列的统计信息 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 PARTTABLE_1996 PARTTABLE_1997 PARTTABLE_1998 PARTTABLE_1999
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 COL2 COL3
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 自动作业收集统计信息 www.htz.pw > start cre_db_stats www.htz.pw > — cre_db_stats.sql begin — www.htz.pw > — gather DB stats www.htz.pw > set echo on www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 12:47:55 PARTTABLE_1996 100 100 2015-02-09 12:47:55 PARTTABLE_1997 100 100 2015-02-09 12:47:55 PARTTABLE_1998 0 2015-02-09 12:47:55 PARTTABLE_1999 100 100 2015-02-09 12:47:55
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE 400 400 2015-02-09 12:47:55
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 400 .0025 0 2015-02-09 12:47:55 400 8 COL2 257 .003891051 0 2015-02-09 12:47:55 400 10 COL3 167 .005988024 0 2015-02-09 12:47:55 400 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — dba_tab_modification记录已经被清空 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > — 向表空间插入数据，但是小于表总行数的10% www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<40);
39 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
www.htz.pw > — 这里可以看到表与1998分区上分别INSERT了39条记录 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
OWNER TABLE_NAME PART SUBPART INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————- SCOTT PARTTABLE PARTTABLE_1998 39 0 0 2015-02-09 12:47:57 NO 0 SCOTT PARTTABLE 39 0 0 2015-02-09 12:47:57 NO 0
www.htz.pw > –收集统计信息 www.htz.pw > start cre_db_stats www.htz.pw > — cre_db_stats.sql begin — www.htz.pw > — gather DB stats www.htz.pw > set echo on www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — www.htz.pw > — 这里可以看到表与1998分区的统计信息发生了变化，其它的分区并没有变化。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 12:47:55 PARTTABLE_1996 100 100 2015-02-09 12:47:55 PARTTABLE_1997 100 100 2015-02-09 12:47:55 PARTTABLE_1998 39 39 2015-02-09 12:47:57 PARTTABLE_1999 100 100 2015-02-09 12:47:55
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE 439 439 2015-02-09 12:47:58
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 439 .002277904 0 2015-02-09 12:47:58 439 8 COL2 257 .003891051 0 2015-02-09 12:47:58 439 11 COL3 167 .005988024 0 2015-02-09 12:47:58 439 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > –还回空 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > –分区1997插入10行小于此分区的10%，1996分区插入1行，表上小于10% www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+101,rownum*2 from dba_objects where rownum<2);
1 row created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+200,rownum*2 from dba_objects where rownum<11);
10 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > start cre_db_stats www.htz.pw > — cre_db_stats.sql begin — www.htz.pw > — gather DB stats www.htz.pw > set echo on www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — www.htz.pw > –这里应该是可以看见2个分区与的统计信息发生变化，其它的分区信息没有变化 。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 12:47:55 PARTTABLE_1996 101 101 2015-02-09 12:48:00 PARTTABLE_1997 110 110 2015-02-09 12:48:00 PARTTABLE_1998 39 39 2015-02-09 12:47:57 PARTTABLE_1999 100 100 2015-02-09 12:47:55
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE 450 450 2015-02-09 12:48:00
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 439 .002277904 0 2015-02-09 12:48:00 450 8 COL2 268 .003731343 0 2015-02-09 12:48:00 450 11 COL3 167 .005988024 0 2015-02-09 12:48:00 450 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > –这里应该是空 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > –下面是每一个分区(除1998)插入一行数据，总修改行小于10% www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*2 from dba_objects where rownum<2);
1 row created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);
1 row created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);
1 row created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);
1 row created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > start cre_db_stats www.htz.pw > — cre_db_stats.sql begin — www.htz.pw > — gather DB stats www.htz.pw > set echo on www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — www.htz.pw > –除1998分区外的所有的分区与表的的统计信息变化了变化，说明所有发生DML操作的分区都发生了统计信息变化，不管是否满足10% www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 101 101 2015-02-09 12:48:02 PARTTABLE_1996 102 102 2015-02-09 12:48:02 PARTTABLE_1997 111 111 2015-02-09 12:48:02 PARTTABLE_1998 39 39 2015-02-09 12:47:57 PARTTABLE_1999 101 101 2015-02-09 12:48:02
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE 454 454 2015-02-09 12:48:02
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 439 .002277904 0 2015-02-09 12:48:02 454 8 COL2 269 .003717472 0 2015-02-09 12:48:02 454 11 COL3 167 .005988024 0 2015-02-09 12:48:02 454 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > –每个分区变化为1，除1998分区 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > –向分区分别插入行，总插入行数大于表的10%，其中一个分区的插入行数大于10%，一个分区不插入数据，其它分区小于10% www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<50);
49 rows created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);
8 rows created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);
8 rows created.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);
8 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > start cre_db_stats www.htz.pw > — cre_db_stats.sql begin — www.htz.pw > — gather DB stats www.htz.pw > set echo on www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — www.htz.pw > — 这里可以发现除1998分区外，表与分区的统计信息都发生了变化。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 109 109 2015-02-09 12:48:04 PARTTABLE_1996 151 151 2015-02-09 12:48:04 PARTTABLE_1997 119 119 2015-02-09 12:48:04 PARTTABLE_1998 39 39 2015-02-09 12:47:57 PARTTABLE_1999 109 109 2015-02-09 12:48:04
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE 527 527 2015-02-09 12:48:04
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 439 .002277904 0 2015-02-09 12:48:04 527 8 COL2 316 .003164557 0 2015-02-09 12:48:04 527 10 COL3 167 .005988024 0 2015-02-09 12:48:04 527 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — ex_auto_inc1.sql end — |
4 总结
4.1 表开启增量统计信息后，不管表与分区修改比例是多少，手动收集表统计信息时都会收集修改的分区的统计信息与更新表的统计信息。
分区表的增量统计信息:手动收集表：等您坐沙发呢！