下面是测试自动作业分析统计信息时，怎么处理表的增量模式的统计信息:
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_DATABASE_STATS(OPTIONS=>’GATHER AUTO’); — 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 — 向表空间插入数据，但是小于表总行数的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
–查看表上面更行的行数统计，这里应该能发现表上面变化是39行 @table_modification.sql –分区1997插入10行小于此分区的10%，1996分区插入1行，表上更改大于10% 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 –下面是每一个分区插入一行数据，总修改行小于10%， 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 –这里应该是所有的分区都不收集。 start d_stats_ex –每个分区变化为1，除1998分区 @table_modification.sql –向分区分别插入行，总插入行数大于表的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分区外，表与分区的统计信息都发生了变化。说明当表的更改行大于10%的时候，表会更新表的统计信息，并更新有发生了dml操作的分区的统计信息。 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 11:57:11 NO 0 SCOTT PARTTABLE PARTTABLE_1995 100 0 0 2015-02-09 11:57:11 NO 0 SCOTT PARTTABLE PARTTABLE_1996 100 0 0 2015-02-09 11:57:11 NO 0 SCOTT PARTTABLE PARTTABLE_1997 100 0 0 2015-02-09 11:57:11 NO 0 SCOTT PARTTABLE PARTTABLE_1999 100 0 0 2015-02-09 11:57:11 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_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);
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 11:57:11 PARTTABLE_1996 100 100 2015-02-09 11:57:11 PARTTABLE_1997 100 100 2015-02-09 11:57:11 PARTTABLE_1998 0 2015-02-09 11:57:11 PARTTABLE_1999 100 100 2015-02-09 11:57:11
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 11:57:11
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 11:57:11 400 8 COL2 257 .003891051 0 2015-02-09 11:57:11 400 10 COL3 167 .005988024 0 2015-02-09 11:57:11 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 > — 向表空间插入数据，但是小于表总行数的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 11:57:12 NO 0 SCOTT PARTTABLE 39 0 0 2015-02-09 11:57:12 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_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);
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 11:57:11 PARTTABLE_1996 100 100 2015-02-09 11:57:11 PARTTABLE_1997 100 100 2015-02-09 11:57:11 PARTTABLE_1998 39 39 2015-02-09 11:57:12 PARTTABLE_1999 100 100 2015-02-09 11:57:11
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 11:57:11
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 11:57:11 400 8 COL2 257 .003891051 0 2015-02-09 11:57:11 400 10 COL3 167 .005988024 0 2015-02-09 11:57:11 400 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > –查看表上面更行的行数统计，这里应该能发现表上面变化是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 39 0 0 2015-02-09 11:57:12 NO 0
www.htz.pw > –分区1997插入10行小于此分区的10%，1996分区插入1行，表上更改大于10% 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
OWNER TABLE_NAME PART SUBPART INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————- SCOTT PARTTABLE 39 0 0 2015-02-09 11:57:12 NO 0
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_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);
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 11:57:11 PARTTABLE_1996 101 101 2015-02-09 11:57:13 PARTTABLE_1997 110 110 2015-02-09 11:57:13 PARTTABLE_1998 39 39 2015-02-09 11:57:12 PARTTABLE_1999 100 100 2015-02-09 11:57:11
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 11:57:13
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 11:57:13 450 8 COL2 268 .003731343 0 2015-02-09 11:57:13 450 11 COL3 167 .005988024 0 2015-02-09 11:57:13 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 > –下面是每一个分区插入一行数据，总修改行小于10% 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_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — 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 100 100 2015-02-09 11:57:11 PARTTABLE_1996 101 101 2015-02-09 11:57:13 PARTTABLE_1997 110 110 2015-02-09 11:57:13 PARTTABLE_1998 39 39 2015-02-09 11:57:12 PARTTABLE_1999 100 100 2015-02-09 11:57:11
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 11:57:13
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 11:57:13 450 8 COL2 268 .003731343 0 2015-02-09 11:57:13 450 11 COL3 167 .005988024 0 2015-02-09 11:57:13 450 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
OWNER TABLE_NAME PART SUBPART INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————- SCOTT PARTTABLE 4 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1995 1 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1996 1 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1997 1 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1999 1 0 0 2015-02-09 11:57:13 NO 0
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
OWNER TABLE_NAME PART SUBPART INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————- SCOTT PARTTABLE 4 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1995 1 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1996 1 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1997 1 0 0 2015-02-09 11:57:13 NO 0 SCOTT PARTTABLE PARTTABLE_1999 1 0 0 2015-02-09 11:57:13 NO 0
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_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);
PL/SQL procedure successfully completed.
www.htz.pw > — cre_db_stat.sql end — www.htz.pw > — 这里可以发现除1998分区外，表与分区的统计信息都发生了变化。说明当表的更改行大于10%的时候，表会更新表的统计信息，并更新有发生了dml操作的分区的统计信息。 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 11:57:13 PARTTABLE_1996 151 151 2015-02-09 11:57:13 PARTTABLE_1997 119 119 2015-02-09 11:57:13 PARTTABLE_1998 39 39 2015-02-09 11:57:12 PARTTABLE_1999 109 109 2015-02-09 11:57:13
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 11:57:13
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 11:57:13 527 8 COL2 316 .003164557 0 2015-02-09 11:57:13 527 10 COL3 167 .005988024 0 2015-02-09 11:57:13 527 4
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — ex_auto_inc1.sql end — |
4，总结
4.1 当表的修改大于10%时，不管每个分区更改情况，会收集发生更改的所有分区并更新表的统计信息
4.2 当表的修改小于10%时，表分区修改大于10%，，会收集所有更改的分区的统计信息，但是不会更新表的统计信息。
4.3 当表的修改小于10%时，分区的修改小于10%，不会收集所有分区与表的统计信息。
分区表的增量统计信息:自动作业收集：等您坐沙发呢！