下面是测试自动作业分析统计信息时，怎么处理表的增量模式的统计信息:
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，1996分区插入1行，表上更改大于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，其中一个分区的插入行数大于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，一个分区不插入数据，其它分区小于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}的时候，表会更新表的统计信息，并更新有发生了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 >
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 >
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 >
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 >
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 >
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 >
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 >
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 >
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 >
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 >
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 >
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 >
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 >
www.htz.pw >
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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 >
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 >
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 >
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 >
www.htz.pw >
www.htz.pw >
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，1996分区插入1行，表上更改大于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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 >
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 >
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 >
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 >
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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 >
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 >
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 >
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 >
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，其中一个分区的插入行数大于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，一个分区不插入数据，其它分区小于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}的时候，表会更新表的统计信息，并更新有发生了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 >
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 >
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 >
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 >
www.htz.pw >
www.htz.pw > -- ex_auto_inc1.sql end ---
4，总结
4.1 当表的修改大于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}时，不管每个分区更改情况，会收集发生更改的所有分区并更新表的统计信息
4.2 当表的修改小于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}时，表分区修改大于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，，会收集所有更改的分区的统计信息，但是不会更新表的统计信息。
4.3 当表的修改小于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}时，分区的修改小于10{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}，不会收集所有分区与表的统计信息。
分区表的增量统计信息:自动作业收集：等您坐沙发呢！