当前位置：Oracle研究中心 > 运维DBA >
【学习笔记】Oracle设置参数granularity来测试分区表统计信息案例
时间:2016-11-03 21:27 来源:Oracle研究中心 作者:HTZ 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于收集Oracle数据库统计信息的笔记，详细记录了如何设置参数granularity来测试分区表统计信息案例笔记。
下面是测试分区表的granularity的参数配置。
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
-- 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_NULOracle???????oracleplus.netLS, 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'))
);
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
-- 自动作业收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',cascade=>true);
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
--收集统计信息，
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',cascade=>true);
-- 这里发现表与表上所有的分区的统计信息都发生了变化，因为没有增加partname参数，所有会收集分区表及每一个分区的统计信息。
start d_stats_ex
-- 无记录
@table_modification.sql
下面只收集一个分区
-- 向1998分区中插入9行数据，但是小于表总行数的10%，大于分区的10%
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+39,rownum*2 from dba_objects where rownum<10);
commit;
exec dbms_stats.flush_database_monitoring_info;
-- 这里可以看到在表上，1998分区上分别INSERT了9条记录
@table_modification.sql
--收集统计信息，
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true)
-- 这里发现表与1998分区的统计信息都发生了变化，其它的分区信息没有变化。默认值是auto，所以会导致分区的信息更新了，但是表的统计信息没有更新。
start d_stats_ex
-- 无记录
@table_modification.sql
--行1998分区插入9行数据，大于10%
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+48,rownum*2 from dba_objects where rownum<10);
commit;
exec dbms_stats.flush_database_monitoring_info;
-- 这里可以看到在表上，1998分区上分别INSERT了9条记录
@table_modification.sql
--收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'PARTITION');
-- 这里可以发现只更新了分区的统计信息，并没有更新表的统计信息
start d_stats_ex
-- 此时可以看到表上更改9行
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+57,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL');
--此时可以看到更新了表的全局信息信息，但是1998分区的信息并没有更新。
start d_stats_ex
-- 表的信息已经删除，但是1998分区上面的dml记录没有删除。
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+66,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'AUTO');
--此时可以看到更新了表的全局信息与1998分区的信息发生了更新。
start d_stats_ex
-- 表的信息已经删除，但是1998分区上面的dml记录没有删除。
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+75,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'ALL');
--此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition
start d_stats_ex
-- 表的信息已经删除，1998分区的信息也删除
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+84,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'APPROX_GLOBAL AND PARTITION');
--此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition
start d_stats_ex
--分区的信息已经删除，但是表上面的dml记录没有删除。
@table_modification.sql
exec dbms_lock.sleep(2);
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+100,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL AND PARTITION');
PL/SQL procedure successfully completed.
--分区与表的统计信息发生变化
start d_stats_ex
@table_modification.sql
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 15:30:42 NO 0
SCOTT PARTTABLE 39 0 0 2015-02-09 15:30:42 NO 0
oracleplus.net> --收集统计信息
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',cascade=>true);
PL/SQL procedure successfully completed.
oracleplus.net> -- 这里发现表与表上所有的分区的统计信息都发生了变化，因为没有增加partname参数，所有会收集分区表及每一个分区的统计信息。
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 39 39 2015-02-09 15:30:42
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 439 439 2015-02-09 15:30:42
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 15:30:42 439 8
COL2 257 .003891051 0 2015-02-09 15:30:42 439 11
COL3 167 .005988024 0 2015-02-09 15:30:42 439 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> --------------------
oracleplus.net> --下面只收集一个分区
oracleplus.net> --------------------
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 向1998分区中插入9行数据，但是小于表总行数的10%，大于分区的10%
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+39,rownum*2 from dba_objects where rownum<10);
9 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了9条记录
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 9 0 0 2015-02-09 15:30:45 NO 0
SCOTT PARTTABLE 9 0 0 2015-02-09 15:30:45 NO 0
oracleplus.net> --收集统计信息
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true)
PL/SQL procedure successfully completed.
oracleplus.net> -- 这里发现表与1998分区的统计信息都发生了变化，其它的分区信息没有变化。默认值是auto，所以会导致分区的信息更新了，但是表的统计信息没有更新。
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 48 48 2015-02-09 15:30:45
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 448 448 2015-02-09 15:30:45
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 15:30:45 448 8
COL2 257 .003891051 0 2015-02-09 15:30:45 448 10
COL3 167 .005988024 0 2015-02-09 15:30:45 448 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> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net> --行1998分区插入9行数据，大于10%
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+48,rownum*2 from dba_objects where rownum<10);
9 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了9条记录
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 9 0 0 2015-02-09 15:30:47 NO 0
SCOTT PARTTABLE 9 0 0 2015-02-09 15:30:47 NO 0
oracleplus.net> --收集统计信息
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'PARTITION');
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 100 100 2015-02-09 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 57 57 2015-02-09 15:30:47
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 448 448 2015-02-09 15:30:45
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 15:30:45 448 8
COL2 257 .003891051 0 2015-02-09 15:30:45 448 10
COL3 167 .005988024 0 2015-02-09 15:30:45 448 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 此时可以看到表上更改9
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 9 0 0 2015-02-09 15:30:47 NO 0
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net> -- 向表中插入9行数据。
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+57,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL');
PL/SQL procedure successfully completed.
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 57 57 2015-02-09 15:30:47
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 466 466 2015-02-09 15:30:49
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 15:30:49 466 8
COL2 257 .003891051 0 2015-02-09 15:30:49 466 10
COL3 167 .005988024 0 2015-02-09 15:30:49 466 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 表的信息已经删除，但是1998分区上面的dml记录没有删除。
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 9 0 0 2015-02-09 15:30:49 NO 0
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+66,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'AUTO');
PL/SQL procedure successfully completed.
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 75 75 2015-02-09 15:30:51
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 475 475 2015-02-09 15:30:51
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 15:30:51 475 8
COL2 257 .003891051 0 2015-02-09 15:30:51 475 10
COL3 167 .005988024 0 2015-02-09 15:30:51 475 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 表与分区1998的信息已经删除
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+75,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'ALL');
PL/SQL procedure successfully completed.
oracleplus.net> --此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 84 84 2015-02-09 15:30:53
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 484 484 2015-02-09 15:30:53
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 15:30:53 484 8
COL2 257 .003891051 0 2015-02-09 15:30:53 484 10
COL3 167 .005988024 0 2015-02-09 15:30:53 484 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 表的信息已经删除，1998分区的信息也删除
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+84,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'APPROX_GLOBAL AND PARTITION');
PL/SQL procedure successfully completed.
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 93 93 2015-02-09 15:30:55
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 484 493 2015-02-09 15:30:55
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 15:30:55 493 8
COL2 257 .003891051 0 2015-02-09 15:30:55 493 10
COL3 167 .005988024 0 2015-02-09 15:30:55 493 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> --分区的信息已经删除，但是表上面的dml记录没有删除。
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 9 0 0 2015-02-09 15:30:55 NO 0
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+100,rownum*2 from dba_objects where rownum<10);
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL AND PARTITION');
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 100 100 2015-02-09 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 102 102 2015-02-09 15:42:18
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 502 502 2015-02-09 15:42:18
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 15:42:18 502 8
COL2 266 .003759398 0 2015-02-09 15:42:18 502 10
COL3 167 .005988024 0 2015-02-09 15:42:18 502 4
oracleplus.net> --表与分区的记录存在
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
本文固定链接: http://www.htz.pw/2015/02/09/%e5%88%86%e5%8c%ba%e8%a1%a8%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%ef%bc%9agranularity%e6%b5%8b%e8%af%95.html | 认真就输
Oracle研究中心
下面是测试分区表的granularity的参数配置。
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_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_NULOracle???????oracleplus.netLS, 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'))
);
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
-- 自动作业收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',cascade=>true);
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
--收集统计信息，
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',cascade=>true);
-- 这里发现表与表上所有的分区的统计信息都发生了变化，因为没有增加partname参数，所有会收集分区表及每一个分区的统计信息。
start d_stats_ex
-- 无记录
@table_modification.sql
下面只收集一个分区
-- 向1998分区中插入9行数据，但是小于表总行数的10%，大于分区的10%
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+39,rownum*2 from dba_objects where rownum<10);
commit;
exec dbms_stats.flush_database_monitoring_info;
-- 这里可以看到在表上，1998分区上分别INSERT了9条记录
@table_modification.sql
--收集统计信息，
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true)
-- 这里发现表与1998分区的统计信息都发生了变化，其它的分区信息没有变化。默认值是auto，所以会导致分区的信息更新了，但是表的统计信息没有更新。
start d_stats_ex
-- 无记录
@table_modification.sql
--行1998分区插入9行数据，大于10%
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+48,rownum*2 from dba_objects where rownum<10);
commit;
exec dbms_stats.flush_database_monitoring_info;
-- 这里可以看到在表上，1998分区上分别INSERT了9条记录
@table_modification.sql
--收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'PARTITION');
-- 这里可以发现只更新了分区的统计信息，并没有更新表的统计信息
start d_stats_ex
-- 此时可以看到表上更改9行
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+57,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL');
--此时可以看到更新了表的全局信息信息，但是1998分区的信息并没有更新。
start d_stats_ex
-- 表的信息已经删除，但是1998分区上面的dml记录没有删除。
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+66,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'AUTO');
--此时可以看到更新了表的全局信息与1998分区的信息发生了更新。
start d_stats_ex
-- 表的信息已经删除，但是1998分区上面的dml记录没有删除。
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+75,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'ALL');
--此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition
start d_stats_ex
-- 表的信息已经删除，1998分区的信息也删除
@table_modification.sql
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+84,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'APPROX_GLOBAL AND PARTITION');
--此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition
start d_stats_ex
--分区的信息已经删除，但是表上面的dml记录没有删除。
@table_modification.sql
exec dbms_lock.sleep(2);
insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+100,rownum*2 from dba_objects where rownum<10);
commit;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL AND PARTITION');
PL/SQL procedure successfully completed.
--分区与表的统计信息发生变化
start d_stats_ex
@table_modification.sql
3，执行结果
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 15:30:42 NO 0
SCOTT PARTTABLE 39 0 0 2015-02-09 15:30:42 NO 0
oracleplus.net> --收集统计信息
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',cascade=>true);
PL/SQL procedure successfully completed.
oracleplus.net> -- 这里发现表与表上所有的分区的统计信息都发生了变化，因为没有增加partname参数，所有会收集分区表及每一个分区的统计信息。
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 39 39 2015-02-09 15:30:42
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 439 439 2015-02-09 15:30:42
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 15:30:42 439 8
COL2 257 .003891051 0 2015-02-09 15:30:42 439 11
COL3 167 .005988024 0 2015-02-09 15:30:42 439 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> --------------------
oracleplus.net> --下面只收集一个分区
oracleplus.net> --------------------
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 向1998分区中插入9行数据，但是小于表总行数的10%，大于分区的10%
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+39,rownum*2 from dba_objects where rownum<10);
9 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了9条记录
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 9 0 0 2015-02-09 15:30:45 NO 0
SCOTT PARTTABLE 9 0 0 2015-02-09 15:30:45 NO 0
oracleplus.net> --收集统计信息
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true)
PL/SQL procedure successfully completed.
oracleplus.net> -- 这里发现表与1998分区的统计信息都发生了变化，其它的分区信息没有变化。默认值是auto，所以会导致分区的信息更新了，但是表的统计信息没有更新。
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 48 48 2015-02-09 15:30:45
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 448 448 2015-02-09 15:30:45
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 15:30:45 448 8
COL2 257 .003891051 0 2015-02-09 15:30:45 448 10
COL3 167 .005988024 0 2015-02-09 15:30:45 448 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> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net> --行1998分区插入9行数据，大于10%
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+48,rownum*2 from dba_objects where rownum<10);
9 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了9条记录
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 9 0 0 2015-02-09 15:30:47 NO 0
SCOTT PARTTABLE 9 0 0 2015-02-09 15:30:47 NO 0
oracleplus.net> --收集统计信息
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'PARTITION');
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 100 100 2015-02-09 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 57 57 2015-02-09 15:30:47
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 448 448 2015-02-09 15:30:45
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 15:30:45 448 8
COL2 257 .003891051 0 2015-02-09 15:30:45 448 10
COL3 167 .005988024 0 2015-02-09 15:30:45 448 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 此时可以看到表上更改9
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 9 0 0 2015-02-09 15:30:47 NO 0
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net> -- 向表中插入9行数据。
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+57,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL');
PL/SQL procedure successfully completed.
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 57 57 2015-02-09 15:30:47
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 466 466 2015-02-09 15:30:49
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 15:30:49 466 8
COL2 257 .003891051 0 2015-02-09 15:30:49 466 10
COL3 167 .005988024 0 2015-02-09 15:30:49 466 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 表的信息已经删除，但是1998分区上面的dml记录没有删除。
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 9 0 0 2015-02-09 15:30:49 NO 0
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+66,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'AUTO');
PL/SQL procedure successfully completed.
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 75 75 2015-02-09 15:30:51
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 475 475 2015-02-09 15:30:51
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 15:30:51 475 8
COL2 257 .003891051 0 2015-02-09 15:30:51 475 10
COL3 167 .005988024 0 2015-02-09 15:30:51 475 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 表与分区1998的信息已经删除
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+75,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'ALL');
PL/SQL procedure successfully completed.
oracleplus.net> --此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 84 84 2015-02-09 15:30:53
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 484 484 2015-02-09 15:30:53
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 15:30:53 484 8
COL2 257 .003891051 0 2015-02-09 15:30:53 484 10
COL3 167 .005988024 0 2015-02-09 15:30:53 484 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> -- 表的信息已经删除，1998分区的信息也删除
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net>
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+84,rownum*2 from dba_objects where rownum<10);
9 rows created.
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'APPROX_GLOBAL AND PARTITION');
PL/SQL procedure successfully completed.
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 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 93 93 2015-02-09 15:30:55
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 484 493 2015-02-09 15:30:55
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 15:30:55 493 8
COL2 257 .003891051 0 2015-02-09 15:30:55 493 10
COL3 167 .005988024 0 2015-02-09 15:30:55 493 4
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net>
oracleplus.net>
oracleplus.net> --分区的信息已经删除，但是表上面的dml记录没有删除。
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 9 0 0 2015-02-09 15:30:55 NO 0
oracleplus.net> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
oracleplus.net> insert into PARTTABLE select * from (select TO_DATE('01-JAN-1998','DD-MON-YYYY')+rownum, rownum+100,rownum*2 from dba_objects where rownum<10);
oracleplus.net> commit;
Commit complete.
oracleplus.net> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'PARTTABLE',partname=>'PARTTABLE_1998',cascade=>true,granularity=>'GLOBAL AND PARTITION');
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 100 100 2015-02-09 15:30:42
PARTTABLE_1996 100 100 2015-02-09 15:30:42
PARTTABLE_1997 100 100 2015-02-09 15:30:42
PARTTABLE_1998 102 102 2015-02-09 15:42:18
PARTTABLE_1999 100 100 2015-02-09 15:30:42
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 502 502 2015-02-09 15:42:18
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 15:42:18 502 8
COL2 266 .003759398 0 2015-02-09 15:42:18 502 10
COL3 167 .005988024 0 2015-02-09 15:42:18 502 4
oracleplus.net> --表与分区的记录存在
oracleplus.net> -- d_stats_ex.sql end --
oracleplus.net> @table_modification.sql
oracleplus.net> set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
本文固定链接: http://www.htz.pw/2015/02/09/%e5%88%86%e5%8c%ba%e8%a1%a8%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%ef%bc%9agranularity%e6%b5%8b%e8%af%95.html | 认真就输
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle设置参数granularity来测试分区表统计信息案例
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1158.html
