sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

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

【学习笔记】Oracle设置参数granularity来测试分区表统计信息案例

时间:2016-11-03 21:27   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于收集Oracle数据库统计信息的笔记,详细记录了如何设置参数granularity来测试分区表统计信息案例笔记。
下面是测试分区表的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

Oracle研究中心

关键词:

Oracle分区表

如何收集Oracle分区表的统计信息

granularity

分区表统计信息