下面是测试分区表的granularity的参数配置。
1，版本
|
www.htz.pw > select * from v$version;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production |
2，测试脚本
|
— cre_db_stats.sql begin — — gather DB stats set echo on prompt * * * Hit ENTER! * * * PAUSE exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true); — cre_db_stats.sql end —
— d_stats_ex.sql begin — — Query stats set echo on
select partition_name,sample_size,num_rows, LAST_ANALYZED from dba_tab_partitions where table_owner=user and table_name=’PARTTABLE’ order by partition_name;
select table_name,sample_size,num_rows, LAST_ANALYZED from dba_tables where owner=user and table_name=’PARTTABLE’;
select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED , SAMPLE_SIZE, AVG_COL_LEN from dba_tab_columns where owner=user and table_name=’PARTTABLE’ order by column_id; — d_stats_ex.sql end —
— ex_auto_inc1.sql begin — set echo on alter session set nls_date_format=’DD-MM-YYYY HH24:MI:SS’; drop table PARTTABLE purge; CREATE TABLE PARTTABLE ( col1 DATE, col2 number, col3 number, constraint pk_parttable primary key (col1,col2) ) PARTITION BY RANGE (col1) (PARTITION PARTTABLE_1995 VALUES LESS THAN (TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1996 VALUES LESS THAN (TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1997 VALUES LESS THAN (TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1998 VALUES LESS THAN (TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)), PARTITION PARTTABLE_1999 VALUES LESS THAN (TO_DATE(’01-JAN-2000′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)) );
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，执行结果
|
www.htz.pw > — 向表空间插入数据，但是小于表总行数的10% www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<40);
39 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
www.htz.pw > — 这里可以看到在表上，1998分区上分别INSERT了39条记录 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
OWNER TABLE_NAME PART SUBPART INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————- SCOTT PARTTABLE PARTTABLE_1998 39 0 0 2015-02-09 15:30:42 NO 0 SCOTT PARTTABLE 39 0 0 2015-02-09 15:30:42 NO 0
www.htz.pw > –收集统计信息 www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,cascade=>true);
PL/SQL procedure successfully completed.
www.htz.pw > –– 这里发现表与表上所有的分区的统计信息都发生了变化，因为没有增加partname参数，所有会收集分区表及每一个分区的统计信息。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 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
www.htz.pw > select table_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tables 3 where owner=user 4 and table_name=’PARTTABLE’;
TABLE_NAME SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————————- ———– ———- ——————- PARTTABLE 439 439 2015-02-09 15:30:42
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 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 无记录 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > ——————– www.htz.pw > –下面只收集一个分区 www.htz.pw > ——————– www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > — 向1998分区中插入9行数据，但是小于表总行数的10%，大于分区的10% www.htz.pw > 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.
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了9条记录 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 9 0 0 2015-02-09 15:30:45 NO 0 SCOTT PARTTABLE 9 0 0 2015-02-09 15:30:45 NO 0
www.htz.pw > –收集统计信息 www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,partname=>’PARTTABLE_1998′,cascade=>true)
PL/SQL procedure successfully completed.
www.htz.pw > –– 这里发现表与1998分区的统计信息都发生了变化，其它的分区信息没有变化。默认值是auto，所以会导致分区的信息更新了，但是表的统计信息没有更新。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 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
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 448 448 2015-02-09 15:30:45
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 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 无记录 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > –行1998分区插入9行数据，大于10% www.htz.pw > 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.
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了9条记录 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 9 0 0 2015-02-09 15:30:47 NO 0 SCOTT PARTTABLE 9 0 0 2015-02-09 15:30:47 NO 0
www.htz.pw > –收集统计信息 www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,partname=>’PARTTABLE_1998′,cascade=>true,granularity=>’PARTITION’);
PL/SQL procedure successfully completed.
www.htz.pw > — 这里可以发现只更新了分区的统计信息，并没有更新表的统计信息 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 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
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 448 448 2015-02-09 15:30:45
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 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 此时可以看到表上更改9 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 9 0 0 2015-02-09 15:30:47 NO 0
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > — 向表中插入9行数据。 www.htz.pw > 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.
www.htz.pw > commit;
Commit complete.
www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,partname=>’PARTTABLE_1998′,cascade=>true,granularity=>’GLOBAL’);
PL/SQL procedure successfully completed.
www.htz.pw > –此时可以看到更新了表的全局信息信息，但是1998分区的信息并没有更新。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 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
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 466 466 2015-02-09 15:30:49
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 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 表的信息已经删除，但是1998分区上面的dml记录没有删除。 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 9 0 0 2015-02-09 15:30:49 NO 0
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’)+rownum, rownum+66,rownum*2 from dba_objects where rownum<10);
9 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,partname=>’PARTTABLE_1998′,cascade=>true,granularity=>’AUTO’);
PL/SQL procedure successfully completed.
www.htz.pw > –此时可以看到更新了表的全局信息与1998分区的信息发生了更新。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 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
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 475 475 2015-02-09 15:30:51
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 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 表与分区1998的信息已经删除 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > 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.
www.htz.pw > commit;
Commit complete.
www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,partname=>’PARTTABLE_1998′,cascade=>true,granularity=>’ALL’);
PL/SQL procedure successfully completed.
www.htz.pw > –此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，相当于AUTO与global and partition www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 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
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 484 484 2015-02-09 15:30:53
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 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > — 表的信息已经删除，1998分区的信息也删除 www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected
www.htz.pw > exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
www.htz.pw > 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.
www.htz.pw > commit;
Commit complete.
www.htz.pw > 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.
www.htz.pw > –此时可以看到更新了表的全局信息与1998分区的信息发生了更新。其它的分区没有发生变化，但是表的修改记录还在。 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 100 100 2015-02-09 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
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 484 493 2015-02-09 15:30:55
www.htz.pw > select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, LAST_ANALYZED 2 , SAMPLE_SIZE, AVG_COL_LEN 3 from dba_tab_columns 4 where owner=user 5 and table_name=’PARTTABLE’ 6 order by column_id;
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN —————————— ———— ———- ———- ——————- ———– ———– COL1 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
www.htz.pw > — d_stats_ex.sql end — www.htz.pw > –分区的信息已经删除，但是表上面的dml记录没有删除。 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 9 0 0 2015-02-09 15:30:55 NO 0
www.htz.pw > exec dbms_lock.sleep(2); PL/SQL procedure successfully completed. www.htz.pw > insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’)+rownum, rownum+100,rownum*2 from dba_objects where rownum<10); www.htz.pw > commit;
Commit complete.
www.htz.pw > exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>’PARTTABLE’,partname=>’PARTTABLE_1998′,cascade=>true,granularity=>’GLOBAL AND PARTITION’); PL/SQL procedure successfully completed. www.htz.pw > —分区与表的统计信息发生变化 www.htz.pw > start d_stats_ex www.htz.pw > — d_stats_ex.sql begin — www.htz.pw > — Query stats www.htz.pw > set echo on www.htz.pw > select partition_name,sample_size,num_rows, LAST_ANALYZED 2 from dba_tab_partitions 3 where table_owner=user 4 and table_name=’PARTTABLE’ 5 order by partition_name;
PART SAMPLE_SIZE NUM_ROWS LAST_ANALYZED ————— ———– ———- ——————- PARTTABLE_1995 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
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 502 502 2015-02-09 15:42:18
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 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
www.htz.pw > —表与分区的记录存在 www.htz.pw > — d_stats_ex.sql end — www.htz.pw > @table_modification.sql www.htz.pw > set echo off
Session altered.
exec dbms_stats.flush_database_monitoring_info
no rows selected |
分区表统计信息：granularity测试：等您坐沙发呢！