sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】自动作业收集Oracle分区表的增量统计信息

时间:2016-11-04 09:28   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库收集统计信息的笔记,该文章详细记录了如何用自动作业收集分区表的增量统计信息的方法。
下面是测试自动作业分析统计信息时,怎么处理表的增量模式的统计信息:

1 版本

oracleplus.net> 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,脚本执行结果

oracleplus.net> @/tmp/ex_auto_inc1.sql
oracleplus.net> — ex_auto_inc1.sql begin —
oracleplus.net> set echo on
oracleplus.net> alter session set nls_date_format=’DD-MM-YYYY HH24:MI:SS’;

Session altered.

oracleplus.net> drop table PARTTABLE purge;

Table dropped.

oracleplus.net> 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.

oracleplus.net>
oracleplus.net> — 配置表增量统计信息的前提,下面4个条件,决一不同。
oracleplus.net> 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.

oracleplus.net>
oracleplus.net> 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.

oracleplus.net>
oracleplus.net> 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.

oracleplus.net>
oracleplus.net> 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.

oracleplus.net>
oracleplus.net> 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);
Oracle о
100 rows created.

oracleplus.net> commit;

Commit complete.

oracleplus.net> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

oracleplus.net> –查看表上面更行的行数统计
oracleplus.net> @table_modification.sql
oracleplus.net> 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

oracleplus.net> — 删除表上的统计信息
oracleplus.net> exec dbms_stats.delete_table_stats(ownname=>user, tabname=>’PARTTABLE’, cascade_columns=>true, cascade_indexes=>true);

PL/SQL procedure successfully completed.

oracleplus.net> — 查看表与列的统计信息
oracleplus.net> start d_stats_ex
oracleplus.net> — d_stats_ex.sql begin —
oracleplus.net> — Query stats
oracleplus.net> set echo on
oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net> — d_stats_ex.sql end —
oracleplus.net>
oracleplus.net>
oracleplus.net> — 自动作业收集统计信息
oracleplus.net> start cre_db_stats
oracleplus.net> — cre_db_stats.sql begin —
oracleplus.net> — gather DB stats
oracleplus.net> set echo on
oracleplus.net> exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

PL/SQL procedure successfully completed.

oracleplus.net> — cre_db_stat.sql end —
oracleplus.net> start d_stats_ex
oracleplus.net> — d_stats_ex.sql begin —
oracleplus.net> — Query stats
oracleplus.net> set echo on
oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net> — d_stats_ex.sql end —
oracleplus.net>
oracleplus.net>
oracleplus.net> — dba_tab_modification记录已经被清空
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off

Session altered.

exec dbms_stats.flush_database_monitoring_info

no rows selected

oracleplus.net> — 向表空间插入数据,但是小于表总行数的10%
oracleplus.net> 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.

oracleplus.net> commit;

Commit complete.

oracleplus.net> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

oracleplus.net> — 这里可以看到表与1998分区上分别INSERT了39条记录
oracleplus.net> @table_modification.sql
oracleplus.net> 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

oracleplus.net> –收集统计信息
oracleplus.net> start cre_db_stats
oracleplus.net> — cre_db_stats.sql begin —
oracleplus.net> — gather DB stats
oracleplus.net> set echo on
oracleplus.net> exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

PL/SQL procedure successfully completed.

oracleplus.net> — cre_db_stat.sql end —
oracleplus.net> — 这里可以发现1998分区的统计信息发生了更改,表与其它分区的统计信息并没有发生变化。
oracleplus.net> start d_stats_ex
oracleplus.net> — d_stats_ex.sql begin —
oracleplus.net> — Query stats
oracleplus.net> set echo on
oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net> — d_stats_ex.sql end —
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net> –查看表上面更行的行数统计,这里应该能发现表上面变化是39
oracleplus.net> @table_modification.sql
oracleplus.net> 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

oracleplus.net> –分区1997插入10行小于此分区的10%,1996分区插入1行,表上更改大于10%
oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> commit;

Commit complete.

oracleplus.net> @table_modification.sql
oracleplus.net> 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

oracleplus.net> start cre_db_stats
oracleplus.net> — cre_db_stats.sql begin —
oracleplus.net> — gather DB stats
oracleplus.net> set echo on
oracleplus.net> exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

PL/SQL procedure successfully completed.

oracleplus.net> — cre_db_stat.sql end —
oracleplus.net> –这里应该是可以看见2个分区与的统计信息发生变化,其它的分区信息没有变化 。
oracleplus.net> start d_stats_ex
oracleplus.net> — d_stats_ex.sql begin —
oracleplus.net> — Query stats
oracleplus.net> set echo on
oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net> — d_stats_ex.sql end —
oracleplus.net>
oracleplus.net>
oracleplus.net> –这里应该是空
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off

Session altered.

exec dbms_stats.flush_database_monitoring_info

no rows selected

oracleplus.net> –下面是每一个分区插入一行数据,总修改行小于10%
oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> commit;

Commit complete.

oracleplus.net> @table_modification.sql
oracleplus.net> set echo off

Session altered.

exec dbms_stats.flush_database_monitoring_info

no rows selected

oracleplus.net> start cre_db_stats
oracleplus.net> — cre_db_stats.sql begin —
oracleplus.net> — gather DB stats
oracleplus.net> set echo on
oracleplus.net> exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

PL/SQL procedure successfully completed.

oracleplus.net> — cre_db_stat.sql end —
oracleplus.net> –这里应该是所有的分区都不收集。
oracleplus.net> start d_stats_ex
oracleplus.net> — d_stats_ex.sql begin —
oracleplus.net> — Query stats
oracleplus.net> set echo on
oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net> — d_stats_ex.sql end —
oracleplus.net>
oracleplus.net>
oracleplus.net> –每个分区变化为1,除1998分区
oracleplus.net> @table_modification.sql
oracleplus.net> 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

oracleplus.net> –向分区分别插入行,总插入行数大于表的10%,其中一个分区的插入行数大于10%,一个分区不插入数据,其它分区小于10%
oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> 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.

oracleplus.net> commit;

Commit complete.

oracleplus.net> @table_modification.sql
oracleplus.net> 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

oracleplus.net> start cre_db_stats
oracleplus.net> — cre_db_stats.sql begin —
oracleplus.net> — gather DB stats
oracleplus.net> set echo on
oracleplus.net> exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

PL/SQL procedure successfully completed.

oracleplus.net> — cre_db_stat.sql end —
oracleplus.net> — 这里可以发现除1998分区外,表与分区的统计信息都发生了变化。说明当表的更改行大于10%的时候,表会更新表的统计信息,并更新有发生了dml操作的分区的统计信息。
oracleplus.net> start d_stats_ex
oracleplus.net> — d_stats_ex.sql begin —
oracleplus.net> — Query stats
oracleplus.net> set echo on
oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net>
oracleplus.net> 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

oracleplus.net> — d_stats_ex.sql end —
oracleplus.net>
oracleplus.net>
oracleplus.net> — ex_auto_inc1.sql end —

4,总结

4.1 当表的修改大于10%时,不管每个分区更改情况,会收集发生更改的所有分区并更新表的统计信息
4.2 当表的修改小于10%时,表分区修改大于10%,,会收集所有更改的分区的统计信息,但是不会更新表的统计信息。
4.3 当表的修改小于10%时,分区的修改小于10%,不会收集所有分区与表的统计信息。

本文固定链接: http://www.htz.pw/2015/02/09/%e5%88%86%e5%8c%ba%e8%a1%a8%e7%9a%84%e5%a2%9e%e9%87%8f%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%e8%87%aa%e5%8a%a8%e4%bd%9c%e4%b8%9a%e6%94%b6%e9%9b%86.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】自动作业收集Oracle分区表的增量统计信息

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1162.html

Oracle研究中心

关键词:

Oracle自动作业收集

Oracle自动作业分析统计信息

Oracle自动作业分析处理表的增量模式的统计信息