ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle统计信息(三)

oracle统计信息(三)

原创 Linux操作系统 作者:安佰胜 时间:2011-02-28 13:44:17 0 删除 编辑

 

8. 收集统计信息的一些例子
例子1对表收集统计信息

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
                                 tabname => 'DEPT',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                 cascade=>TRUE
                                 );
END;
/

上面的例子收集SCOTT.DEPT表的统计信息。这里面值得关注的一个参数就是method_opt。这个参数控制是否收集列的直方图信息。通常情况下,是不会收集直方图的.

关于直方图不是三言两语可以说明白的。

它的四个选项

 

method_opt=>'for all columns size skewonly' 

ORACLE会根据数据分布收集直方图

 

method_opt=>'for all columns size repeat'

只有以前收集过直方图,才会收集直方图信息,所以一般我们会设置method_opt repeat

 

method_opt=>'for all columns size auto' 

ORACLE会根据数据分布以及列的workload来确定是否收集直方图

method_opt=>'for all columns size interger'

我们自己指定一个bucket

例子2对某一个schma收集统计信息
BEGIN

   DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',

                                  estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

                                  ptions => 'gather auto',

                                  degree  => DBMS_STATS.AUTO_DEGREE,

                                  method_opt => 'for all columns size repeat',

                                  cascade => TRUE

                                 );

END;                                

/

 

上面的例子收集SCOTT模式下所有对象的统计信息。里面值得注意的一个参数就是options。前面已经讲到过,他与表监控有关。它有四个选项

Options =>’gather’       收集所有对象的统计信息
Options =>’gather empty’
只收集还没被统计的表
Options =>’gather stale’
只收集修改量超过10%的表
Options =>’gather auto’ 
相当于empty+stale ,所以我们一般设置为AUTO

例子3 对一个分区表收集统计信息
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
                                 tabname => 'P_TEST',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,

                                 granularity => 'ALL',
                                 cascade=>TRUE
                                 );
END;
/

 

上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity,他有7个选项。

 

granularity => 'ALL'  收集分区,子分区,全局的统计信息
granularity => 'AUTO'
这个是默认的设置,ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
granularity => 'DEFAULT'
这个是过期了的
granularity => 'GLOBAL'
收集全局统计信息
granularity => 'GLOBAL AND PARTITION'
收集全局,分区统计信息,但是不收集子分区统计信息
granularity => 'PARTITION'
收集分区统计信息
granularity => 'SUBPARTITION'
收集子分区统计信息
当然我们可以指定partname,自己控制对哪个分区收集统计信息

 

9. 列出表需要收集统计信息的脚本

普通表

set serveroutput on

declare

   -----select OVER THE Change RATE TABLES---------------

   cursor overchangerate is

 select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows

                from dba_tab_modifications a, dba_tables b

               where a.table_name = b.table_name

                 and table_owner not in

                     ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                      'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                      'OUTLN', 'TSMSYS', 'MDSYS')

                 and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1

                 or a.table_name = b.table_name

                 and table_owner not in

                     ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                      'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                      'OUTLN', 'TSMSYS', 'MDSYS')

                 and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1 or

                 a.table_name = b.table_name

                 and table_owner not in

                     ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                      'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                      'OUTLN', 'TSMSYS', 'MDSYS')

                 and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ;

    ----select the unanalyzed table---------------

    cursor nullmonitor is

      select owner, table_name

        from dba_tables

       where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',

              'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',

              'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')

         and last_analyzed is null;

  begin

    dbms_output.enable(1000000);

    ----flush the monitorring information into the dba_tab_modifications

    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    ----display the unanalyzed table--------------

    dbms_output.put_line('- - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

  

    dbms_output.put_line('Unalalyzed tables:');

    for v_null in nullmonitor loop

      dbms_output.put_line(v_null.owner || '.' || v_null.table_name ||

                           ' has not been analyzed, consider gathering statistics');

    end loop;

    ----display the  information-------------------

    dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

    dbms_output.put_line('Over the Change_Rate 10%:');

    for v_topinsert in overchangerate loop

      dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

                           'till now inserted ' || v_topinsert.inserts || ' rows, updated  ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||   

                           ' rows. consider gathering statistics');

    end loop;

     dbms_output.put_line('- - - - - - - - - - - - - - - - - - -  - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

  end;

/

 

下面的是分区表
set serveroutput on

declare

   -----select OVER THE Change RATE TABLES---------------

   cursor overchangerate is

select a.table_owner,a.table_name,a.partition_name,sum(a.inserts) inserts,sum(a.updates) updates,sum(a.deletes) deletes,sum(b.num_rows) num_rows

from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name

and a.partition_name=b.partition_name and   a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS')

group by a.table_owner,a.table_name,a.partition_name

having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

or

(sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

or

(sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

order by a.table_name;

  begin

    dbms_output.enable(1000000);

    ----flush the monitorring information into the dba_tab_modifications

    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    ----display the top_n_insert information-------------------

    dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

    dbms_output.put_line('Over the Change_Rate 10%:');

    for v_topinsert in overchangerate loop

      dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name  || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

                           'till now inserted ' || v_topinsert.inserts || ' rows, updated  ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||   

                           ' rows. consider gathering statistics');

    end loop;

    dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

  end;

/

 

在此特别声明一点,在oracle11.2版本中有一个相关的BUG

Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8

BUG会导致严重的性能问题。

oracle官方申明,只有在12.1版本才解决这个问题,临时解决方案是手动关闭动态采样。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13177610/viewspace-688099/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-08-26

  • 博文量
    215
  • 访问量
    617993