当前位置: 首页 > BASIC > 正文

分区表的增量统计信息:自动作业收集

下面是测试自动作业分析统计信息时,怎么处理表的增量模式的统计信息:

1 版本

www.htz.pw > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

2,脚本

— cre_db_stats.sql begin —

  — gather DB stats

  set echo on

  prompt * * * Hit ENTER! * * *

  PAUSE

 exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

— cre_db_stats.sql end —

 

— d_stats_ex.sql begin —

  — Query stats

  set echo on

 

  select partition_name,sample_size,num_rows,  LAST_ANALYZED

   from dba_tab_partitions

  where table_owner=user

  and table_name=’PARTTABLE’

  order by partition_name;

 

  select table_name,sample_size,num_rows,  LAST_ANALYZED

  from dba_tables

  where owner=user

  and table_name=’PARTTABLE’;

 

  select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

 , SAMPLE_SIZE, AVG_COL_LEN

  from dba_tab_columns

  where owner=user

  and table_name=’PARTTABLE’

  order by column_id;

— d_stats_ex.sql end —

— ex_auto_inc1.sql begin —

        set echo on

        alter session set nls_date_format=’DD-MM-YYYY HH24:MI:SS’;

        drop table PARTTABLE purge;

        CREATE TABLE PARTTABLE

            (    col1        DATE,

                 col2        number,

                 col3        number,

                 constraint   pk_parttable primary key (col1,col2)

            )

                PARTITION BY RANGE (col1)

                (PARTITION PARTTABLE_1995 VALUES LESS THAN (TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

                 PARTITION PARTTABLE_1996 VALUES LESS THAN (TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

                 PARTITION PARTTABLE_1997 VALUES LESS THAN (TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

                 PARTITION PARTTABLE_1998 VALUES LESS THAN (TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

                 PARTITION PARTTABLE_1999 VALUES LESS THAN (TO_DATE(’01-JAN-2000′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’))

                );

 

        — 配置表增量统计信息的前提,下面4个条件,决一不同。

        begin

        dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘ESTIMATE_PERCENT’, DBMS_STATS.AUTO_SAMPLE_SIZE);

        dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘INCREMENTAL’, ‘TRUE’);

        dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘PUBLISH’, ‘TRUE’);

        dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘GRANULARITY’, ‘AUTO’);

        end;

        /

 

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum,rownum*2 from dba_objects where rownum<101);

 

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);

 

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);

 

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum*rownum,rownum*3 from dba_objects where rownum<101);

        commit;

        exec dbms_stats.flush_database_monitoring_info;

        –查看表上面更行的行数统计

        @table_modification.sql

        — 删除表上的统计信息

        exec dbms_stats.delete_table_stats(ownname=>user, tabname=>’PARTTABLE’, cascade_columns=>true, cascade_indexes=>true);

        — 查看表与列的统计信息

        start d_stats_ex

        — 自动作业收集统计信息

        start cre_db_stats

        start d_stats_ex

        — dba_tab_modification记录已经被清空

        @table_modification.sql

        — 向表空间插入数据,但是小于表总行数的10%

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<40);

        commit;

        exec dbms_stats.flush_database_monitoring_info;

        — 这里可以看到表与1998分区上分别INSERT39条记录

        @table_modification.sql

        –收集统计信息,

        start cre_db_stats

        — 这里可以发现1998分区的统计信息发生了更改,表与其它分区的统计信息并没有发生变化。

        start d_stats_ex

       

        –查看表上面更行的行数统计,这里应该能发现表上面变化是39

        @table_modification.sql

        –分区1997插入10行小于此分区的10%1996分区插入1行,表上更改大于10%

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+101,rownum*2 from dba_objects where rownum<2);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+200,rownum*2 from dba_objects where rownum<11);

        commit;

        @table_modification.sql

        start cre_db_stats

        –这里应该是可以看见2个分区与的统计信息发生变化,其它的分区信息没有变化

        start d_stats_ex   

        –这里应该是空

        @table_modification.sql

        –下面是每一个分区插入一行数据,总修改行小于10%

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*2 from dba_objects where rownum<2);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);

        commit;

        @table_modification.sql

        start cre_db_stats

        –这里应该是所有的分区都不收集。

        start d_stats_ex 

        –每个分区变化为1,除1998分区

        @table_modification.sql

        –向分区分别插入行,总插入行数大于表的10%,其中一个分区的插入行数大于10%,一个分区不插入数据,其它分区小于10%

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<50);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);

        insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);

        commit;

        @table_modification.sql

        start cre_db_stats

        — 这里可以发现除1998分区外,表与分区的统计信息都发生了变化。说明当表的更改行大于10%的时候,表会更新表的统计信息,并更新有发生了dml操作的分区的统计信息。

        start d_stats_ex

— ex_auto_inc1.sql end —

3,脚本执行结果

www.htz.pw > @/tmp/ex_auto_inc1.sql

www.htz.pw > — ex_auto_inc1.sql begin —

www.htz.pw >         set echo on

www.htz.pw >         alter session set nls_date_format=’DD-MM-YYYY HH24:MI:SS’;

 

Session altered.

 

www.htz.pw >         drop table PARTTABLE purge;

 

Table dropped.

 

www.htz.pw >         CREATE TABLE PARTTABLE

  2              (    col1        DATE,

  3                   col2        number,

  4                   col3        number,

  5                   constraint   pk_parttable primary key (col1,col2)

  6              )

  7                  PARTITION BY RANGE (col1)

  8                  (PARTITION PARTTABLE_1995 VALUES LESS THAN (TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

  9                   PARTITION PARTTABLE_1996 VALUES LESS THAN (TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

 10                   PARTITION PARTTABLE_1997 VALUES LESS THAN (TO_DATE(’01-JAN-1998′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

 11                   PARTITION PARTTABLE_1998 VALUES LESS THAN (TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’)),

 12                   PARTITION PARTTABLE_1999 VALUES LESS THAN (TO_DATE(’01-JAN-2000′,’DD-MON-YYYY’,’NLS_DATE_LANGUAGE = American’))

 13                  );

 

Table created.

 

www.htz.pw >

www.htz.pw >         — 配置表增量统计信息的前提,下面4个条件,决一不同。

www.htz.pw >         begin

  2          dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘ESTIMATE_PERCENT’, DBMS_STATS.AUTO_SAMPLE_SIZE);

  3          dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘INCREMENTAL’, ‘TRUE’);

  4          dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘PUBLISH’, ‘TRUE’);

  5          dbms_stats.set_table_prefs(user, ‘PARTTABLE’, ‘GRANULARITY’, ‘AUTO’);

  6          end;

  7          /

 

PL/SQL procedure successfully completed.

 

www.htz.pw >

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum,rownum*2 from dba_objects where rownum<101);

 

100 rows created.

 

www.htz.pw >

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);

 

100 rows created.

 

www.htz.pw >

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum/3,rownum*3 from dba_objects where rownum<101);

 

100 rows created.

 

www.htz.pw >

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum*rownum,rownum*3 from dba_objects where rownum<101);

 

100 rows created.

 

www.htz.pw >         commit;

 

Commit complete.

 

www.htz.pw >         exec dbms_stats.flush_database_monitoring_info;

 

PL/SQL procedure successfully completed.

 

www.htz.pw >         –查看表上面更行的行数统计

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

OWNER           TABLE_NAME                PART            SUBPART            INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED  DROP_SEGMENTS

————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————-

SCOTT           PARTTABLE                                                        400          0          0 2015-02-09 11:57:11 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1995                         100          0          0 2015-02-09 11:57:11 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1996                         100          0          0 2015-02-09 11:57:11 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1997                         100          0          0 2015-02-09 11:57:11 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1999                         100          0          0 2015-02-09 11:57:11 NO                     0

 

www.htz.pw >         — 删除表上的统计信息

www.htz.pw >         exec dbms_stats.delete_table_stats(ownname=>user, tabname=>’PARTTABLE’, cascade_columns=>true, cascade_indexes=>true);

 

PL/SQL procedure successfully completed.

 

www.htz.pw >         — 查看表与列的统计信息

www.htz.pw >         start d_stats_ex

www.htz.pw > — d_stats_ex.sql begin —

www.htz.pw >   — Query stats

www.htz.pw >   set echo on

www.htz.pw >

www.htz.pw >   select partition_name,sample_size,num_rows,  LAST_ANALYZED

  2     from dba_tab_partitions

  3    where table_owner=user

  4    and table_name=’PARTTABLE’

  5    order by partition_name;

 

PART            SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————— ———– ———- ——————-

PARTTABLE_1995

PARTTABLE_1996

PARTTABLE_1997

PARTTABLE_1998

PARTTABLE_1999

 

www.htz.pw >

www.htz.pw >   select table_name,sample_size,num_rows,  LAST_ANALYZED

  2    from dba_tables

  3    where owner=user

  4    and table_name=’PARTTABLE’;

 

TABLE_NAME                SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————————- ———– ———- ——————-

PARTTABLE

 

www.htz.pw >

www.htz.pw >   select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

  2   , SAMPLE_SIZE, AVG_COL_LEN

  3    from dba_tab_columns

  4    where owner=user

  5    and table_name=’PARTTABLE’

  6    order by column_id;

 

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS LAST_ANALYZED       SAMPLE_SIZE AVG_COL_LEN

—————————— ———— ———- ———- ——————- ———– ———–

COL1

COL2

COL3

 

www.htz.pw > — d_stats_ex.sql end —

www.htz.pw >

www.htz.pw >

www.htz.pw >         — 自动作业收集统计信息

www.htz.pw >         start cre_db_stats

www.htz.pw > — cre_db_stats.sql begin —

www.htz.pw >   — gather DB stats

www.htz.pw >   set echo on

www.htz.pw > exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

 

PL/SQL procedure successfully completed.

 

www.htz.pw > — cre_db_stat.sql end —

www.htz.pw >         start d_stats_ex

www.htz.pw > — d_stats_ex.sql begin —

www.htz.pw >   — Query stats

www.htz.pw >   set echo on

www.htz.pw >

www.htz.pw >   select partition_name,sample_size,num_rows,  LAST_ANALYZED

  2     from dba_tab_partitions

  3    where table_owner=user

  4    and table_name=’PARTTABLE’

  5    order by partition_name;

 

PART            SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————— ———– ———- ——————-

PARTTABLE_1995          100        100 2015-02-09 11:57:11

PARTTABLE_1996          100        100 2015-02-09 11:57:11

PARTTABLE_1997          100        100 2015-02-09 11:57:11

PARTTABLE_1998                       0 2015-02-09 11:57:11

PARTTABLE_1999          100        100 2015-02-09 11:57:11

 

www.htz.pw >

www.htz.pw >   select table_name,sample_size,num_rows,  LAST_ANALYZED

  2    from dba_tables

  3    where owner=user

  4    and table_name=’PARTTABLE’;

 

TABLE_NAME                SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————————- ———– ———- ——————-

PARTTABLE                         400        400 2015-02-09 11:57:11

 

www.htz.pw >

www.htz.pw >   select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

  2   , SAMPLE_SIZE, AVG_COL_LEN

  3    from dba_tab_columns

  4    where owner=user

  5    and table_name=’PARTTABLE’

  6    order by column_id;

 

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS LAST_ANALYZED       SAMPLE_SIZE AVG_COL_LEN

—————————— ———— ———- ———- ——————- ———– ———–

COL1                                    400      .0025          0 2015-02-09 11:57:11         400           8

COL2                                    257 .003891051          0 2015-02-09 11:57:11         400          10

COL3                                    167 .005988024          0 2015-02-09 11:57:11         400           4

 

www.htz.pw > — d_stats_ex.sql end —

www.htz.pw >

www.htz.pw >

www.htz.pw >         — dba_tab_modification记录已经被清空

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

no rows selected

 

www.htz.pw >         — 向表空间插入数据,但是小于表总行数的10%

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分区上分别INSERT39条记录

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

OWNER           TABLE_NAME                PART            SUBPART            INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED  DROP_SEGMENTS

————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————-

SCOTT           PARTTABLE                 PARTTABLE_1998                          39          0          0 2015-02-09 11:57:12 NO                     0

SCOTT           PARTTABLE                                                         39          0          0 2015-02-09 11:57:12 NO                     0

 

www.htz.pw >         –收集统计信息

www.htz.pw >             start cre_db_stats

www.htz.pw > — cre_db_stats.sql begin —

www.htz.pw >   — gather DB stats

www.htz.pw >   set echo on

www.htz.pw > exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

 

PL/SQL procedure successfully completed.

 

www.htz.pw > — cre_db_stat.sql end —

www.htz.pw >         — 这里可以发现1998分区的统计信息发生了更改,表与其它分区的统计信息并没有发生变化。

www.htz.pw >         start d_stats_ex

www.htz.pw > — d_stats_ex.sql begin —

www.htz.pw >   — Query stats

www.htz.pw >   set echo on

www.htz.pw >

www.htz.pw >   select partition_name,sample_size,num_rows,  LAST_ANALYZED

  2     from dba_tab_partitions

  3    where table_owner=user

  4    and table_name=’PARTTABLE’

  5    order by partition_name;

 

PART            SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————— ———– ———- ——————-

PARTTABLE_1995          100        100 2015-02-09 11:57:11

PARTTABLE_1996          100        100 2015-02-09 11:57:11

PARTTABLE_1997          100        100 2015-02-09 11:57:11

PARTTABLE_1998           39         39 2015-02-09 11:57:12

PARTTABLE_1999          100        100 2015-02-09 11:57:11

 

www.htz.pw >

www.htz.pw >   select table_name,sample_size,num_rows,  LAST_ANALYZED

  2    from dba_tables

  3    where owner=user

  4    and table_name=’PARTTABLE’;

 

TABLE_NAME                SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————————- ———– ———- ——————-

PARTTABLE                         400        400 2015-02-09 11:57:11

 

www.htz.pw >

www.htz.pw >   select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

  2   , SAMPLE_SIZE, AVG_COL_LEN

  3    from dba_tab_columns

  4    where owner=user

  5    and table_name=’PARTTABLE’

  6    order by column_id;

 

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS LAST_ANALYZED       SAMPLE_SIZE AVG_COL_LEN

—————————— ———— ———- ———- ——————- ———– ———–

COL1                                    400      .0025          0 2015-02-09 11:57:11         400           8

COL2                                    257 .003891051          0 2015-02-09 11:57:11         400          10

COL3                                    167 .005988024          0 2015-02-09 11:57:11         400           4

 

www.htz.pw > — d_stats_ex.sql end —

www.htz.pw >

www.htz.pw >

www.htz.pw >

www.htz.pw >         –查看表上面更行的行数统计,这里应该能发现表上面变化是39

www.htz.pw >           @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

OWNER           TABLE_NAME                PART            SUBPART            INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED  DROP_SEGMENTS

————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————-

SCOTT           PARTTABLE                                                         39          0          0 2015-02-09 11:57:12 NO                     0

 

www.htz.pw >         –分区1997插入10行小于此分区的10%1996分区插入1行,表上更改大于10%

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+101,rownum*2 from dba_objects where rownum<2);

 

1 row created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+200,rownum*2 from dba_objects where rownum<11);

 

10 rows created.

 

www.htz.pw >         commit;

 

Commit complete.

 

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

OWNER           TABLE_NAME                PART            SUBPART            INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED  DROP_SEGMENTS

————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————-

SCOTT           PARTTABLE                                                         39          0          0 2015-02-09 11:57:12 NO                     0

 

www.htz.pw >         start cre_db_stats

www.htz.pw > — cre_db_stats.sql begin —

www.htz.pw >   — gather DB stats

www.htz.pw >   set echo on

www.htz.pw > exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

 

PL/SQL procedure successfully completed.

 

www.htz.pw > — cre_db_stat.sql end —

www.htz.pw >         –这里应该是可以看见2个分区与的统计信息发生变化,其它的分区信息没有变化

www.htz.pw >         start d_stats_ex

www.htz.pw > — d_stats_ex.sql begin —

www.htz.pw >   — Query stats

www.htz.pw >   set echo on

www.htz.pw >

www.htz.pw >   select partition_name,sample_size,num_rows,  LAST_ANALYZED

  2     from dba_tab_partitions

  3    where table_owner=user

  4    and table_name=’PARTTABLE’

  5    order by partition_name;

 

PART            SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————— ———– ———- ——————-

PARTTABLE_1995          100        100 2015-02-09 11:57:11

PARTTABLE_1996          101        101 2015-02-09 11:57:13

PARTTABLE_1997          110        110 2015-02-09 11:57:13

PARTTABLE_1998           39         39 2015-02-09 11:57:12

PARTTABLE_1999          100        100 2015-02-09 11:57:11

 

www.htz.pw >

www.htz.pw >   select table_name,sample_size,num_rows,  LAST_ANALYZED

  2    from dba_tables

  3    where owner=user

  4    and table_name=’PARTTABLE’;

 

TABLE_NAME                SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————————- ———– ———- ——————-

PARTTABLE                         450        450 2015-02-09 11:57:13

 

www.htz.pw >

www.htz.pw >   select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

  2   , SAMPLE_SIZE, AVG_COL_LEN

  3    from dba_tab_columns

  4    where owner=user

  5    and table_name=’PARTTABLE’

  6    order by column_id;

 

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS LAST_ANALYZED       SAMPLE_SIZE AVG_COL_LEN

—————————— ———— ———- ———- ——————- ———– ———–

COL1                                    439 .002277904          0 2015-02-09 11:57:13         450           8

COL2                                    268 .003731343          0 2015-02-09 11:57:13         450          11

COL3                                    167 .005988024          0 2015-02-09 11:57:13         450           4

 

www.htz.pw > — d_stats_ex.sql end —

www.htz.pw >

www.htz.pw >

www.htz.pw >         –这里应该是空

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

no rows selected

 

www.htz.pw >         –下面是每一个分区插入一行数据,总修改行小于10%

www.htz.pw >            insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*2 from dba_objects where rownum<2);

 

1 row created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);

 

1 row created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);

 

1 row created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+103,rownum*3 from dba_objects where rownum<2);

 

1 row created.

 

www.htz.pw >         commit;

 

Commit complete.

 

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

no rows selected

 

www.htz.pw >         start cre_db_stats

www.htz.pw > — cre_db_stats.sql begin —

www.htz.pw >   — gather DB stats

www.htz.pw >   set echo on

www.htz.pw > exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

 

PL/SQL procedure successfully completed.

 

www.htz.pw > — cre_db_stat.sql end —

www.htz.pw >         –这里应该是所有的分区都不收集。

www.htz.pw >         start d_stats_ex

www.htz.pw > — d_stats_ex.sql begin —

www.htz.pw >   — Query stats

www.htz.pw >   set echo on

www.htz.pw >

www.htz.pw >   select partition_name,sample_size,num_rows,  LAST_ANALYZED

  2     from dba_tab_partitions

  3    where table_owner=user

  4    and table_name=’PARTTABLE’

  5    order by partition_name;

 

PART            SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————— ———– ———- ——————-

PARTTABLE_1995          100        100 2015-02-09 11:57:11

PARTTABLE_1996          101        101 2015-02-09 11:57:13

PARTTABLE_1997          110        110 2015-02-09 11:57:13

PARTTABLE_1998           39         39 2015-02-09 11:57:12

PARTTABLE_1999          100        100 2015-02-09 11:57:11

 

www.htz.pw >

www.htz.pw >   select table_name,sample_size,num_rows,  LAST_ANALYZED

  2    from dba_tables

  3    where owner=user

  4    and table_name=’PARTTABLE’;

 

TABLE_NAME                SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————————- ———– ———- ——————-

PARTTABLE                         450        450 2015-02-09 11:57:13

 

www.htz.pw >

www.htz.pw >   select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

  2   , SAMPLE_SIZE, AVG_COL_LEN

  3    from dba_tab_columns

  4    where owner=user

  5    and table_name=’PARTTABLE’

  6    order by column_id;

 

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS LAST_ANALYZED       SAMPLE_SIZE AVG_COL_LEN

—————————— ———— ———- ———- ——————- ———– ———–

COL1                                    439 .002277904          0 2015-02-09 11:57:13         450           8

COL2                                    268 .003731343          0 2015-02-09 11:57:13         450          11

COL3                                    167 .005988024          0 2015-02-09 11:57:13         450           4

 

www.htz.pw > — d_stats_ex.sql end —

www.htz.pw >

www.htz.pw >

www.htz.pw >         –每个分区变化为1,除1998分区

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

OWNER           TABLE_NAME                PART            SUBPART            INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED  DROP_SEGMENTS

————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————-

SCOTT           PARTTABLE                                                          4          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1995                           1          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1996                           1          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1997                           1          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1999                           1          0          0 2015-02-09 11:57:13 NO                     0

 

www.htz.pw >         –向分区分别插入行,总插入行数大于表的10%,其中一个分区的插入行数大于10%,一个分区不插入数据,其它分区小于10%

www.htz.pw >              insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1996′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<50);

 

49 rows created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1997′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);

 

8 rows created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);

 

8 rows created.

 

www.htz.pw >         insert into PARTTABLE select * from (select TO_DATE(’01-JAN-1999′,’DD-MON-YYYY’)+rownum, rownum+105,rownum*2 from dba_objects where rownum<9);

 

8 rows created.

 

www.htz.pw >         commit;

 

Commit complete.

 

www.htz.pw >         @table_modification.sql

www.htz.pw > set echo off

 

Session altered.

 

exec dbms_stats.flush_database_monitoring_info

 

OWNER           TABLE_NAME                PART            SUBPART            INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED  DROP_SEGMENTS

————— ————————- ————— ————— ———- ———- ———- ——————- ———- ————-

SCOTT           PARTTABLE                                                          4          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1995                           1          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1996                           1          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1997                           1          0          0 2015-02-09 11:57:13 NO                     0

SCOTT           PARTTABLE                 PARTTABLE_1999                           1          0          0 2015-02-09 11:57:13 NO                     0

 

www.htz.pw >         start cre_db_stats

www.htz.pw > — cre_db_stats.sql begin —

www.htz.pw >   — gather DB stats

www.htz.pw >   set echo on

www.htz.pw > exec DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>’GATHER AUTO’);

 

PL/SQL procedure successfully completed.

 

www.htz.pw > — cre_db_stat.sql end —

www.htz.pw >         — 这里可以发现除1998分区外,表与分区的统计信息都发生了变化。说明当表的更改行大于10%的时候,表会更新表的统计信息,并更新有发生了dml操作的分区的统计信息。

www.htz.pw >         start d_stats_ex

www.htz.pw > — d_stats_ex.sql begin —

www.htz.pw >   — Query stats

www.htz.pw >   set echo on

www.htz.pw >

www.htz.pw >   select partition_name,sample_size,num_rows,  LAST_ANALYZED

  2     from dba_tab_partitions

  3    where table_owner=user

  4    and table_name=’PARTTABLE’

  5    order by partition_name;

 

PART            SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————— ———– ———- ——————-

PARTTABLE_1995          109        109 2015-02-09 11:57:13

PARTTABLE_1996          151        151 2015-02-09 11:57:13

PARTTABLE_1997          119        119 2015-02-09 11:57:13

PARTTABLE_1998           39         39 2015-02-09 11:57:12

PARTTABLE_1999          109        109 2015-02-09 11:57:13

 

www.htz.pw >

www.htz.pw >   select table_name,sample_size,num_rows,  LAST_ANALYZED

  2    from dba_tables

  3    where owner=user

  4    and table_name=’PARTTABLE’;

 

TABLE_NAME                SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED

————————- ———– ———- ——————-

PARTTABLE                         527        527 2015-02-09 11:57:13

 

www.htz.pw >

www.htz.pw >   select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,  LAST_ANALYZED

  2   , SAMPLE_SIZE, AVG_COL_LEN

  3    from dba_tab_columns

  4    where owner=user

  5    and table_name=’PARTTABLE’

  6    order by column_id;

 

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS LAST_ANALYZED       SAMPLE_SIZE AVG_COL_LEN

—————————— ———— ———- ———- ——————- ———– ———–

COL1                                    439 .002277904          0 2015-02-09 11:57:13         527           8

COL2                                    316 .003164557          0 2015-02-09 11:57:13         527          10

COL3                                    167 .005988024          0 2015-02-09 11:57:13         527           4

 

www.htz.pw > — d_stats_ex.sql end —

www.htz.pw >

www.htz.pw >

www.htz.pw > — ex_auto_inc1.sql end —

4,总结

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

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 | 认真就输

该日志由 huangtingzhong 于2015年02月09日发表在 BASIC 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 分区表的增量统计信息:自动作业收集 | 认真就输
关键字: