ITPub博客

首页 > 数据库 > Oracle > Oracle 11g 统计信息收集脚本

Oracle 11g 统计信息收集脚本

原创 Oracle 作者:feelpurple 时间:2016-02-02 09:42:32 0 删除 编辑
在 Oracle 11g,系统自带的 JOB 里面有收集统计信息的任务,但并不一定每天每天都会收集。
在 DBMS_STATS 包中有一个 STALE_PERCENT 参数,这个参数的意义是:当表中的数据量修改超过总数据量的一定比例数,会再次触发统计信息收集,默认值为10%。
这样对于大表来说就有一个问题,收集的时间不会很及时,可能会隔好几天才会收集一次,这样会导致表的统计信息不准确,对数据库的 SQL 解析产生影响。

大表通常都是分区表,下面的收集方案主要是针对分区表,也涵盖了普通表。

--方案一

对于非分区表,使用默认的统计信息采集方法;对于分区表,使用增量采集方法,只对数据有变动的分区做收集。

BEGIN
  --采集非分区表的统计信息
  FOR i IN (select s.TABLE_NAME, s.num_rows
              from dba_tables s
             where s.OWNER = 'ACCT'
               and not exists
             (select distinct TABLE_NAME
                      from dba_tab_partitions p
                     where table_owner = 'ACCT'
                       and p.table_name = s.TABLE_NAME)) LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  i.TABLE_NAME,
                                  DEGREE       => 5,
                                  CASCADE      => TRUE);
  END LOOP;
  --采集分区表的统计信息  
  FOR j IN (select distinct TABLE_NAME
              from dba_tab_partitions p
             where table_owner = 'ACCT') LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  tabname          => j.table_name,
                                  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                  GRANULARITY      => 'AUTO',
                                  DEGREE           => 5,
                                  CASCADE          => TRUE);
  END LOOP;
END;

--对于分区表,设置 INCREMENTAL 参数只对数据有变动的分区做收集

在对分区表收集统计信息的时候,如果想让 Oracle 只扫描发生数据改变的分区,避免全表扫描,需要满足以下条件:
(1) 指定分区表的 INCREMENTAL 参数设置为 TRUE
(2) 指定分区表的 PUBLISH 设置为 TRUE
(3) ESTIMATE_PERCENT 参数设为 AUTO_SAMPLE_SIZE,GRANULARITY 参数设为 AUTO

--统计信息收集设置选项

CASCADE 索引的统计信息是否和表一起收集。
DEGREE 收集的并行度。
ESTIMATE_PERCENT 决定收集的比例,范围是[0.000001,100]。可以通过DBMS_STATS.AUTO_SAMPLE_SIZE来让oracle自动决定收集的比例。这是默认值。
METHOD_OPT 控制列和柱状图的收集方法。
GRANULARITY 收集的粒度。
PUBLISH 当统计信息收集JOB执行完毕后,是否更新新的统计信息。
INCREMENTAL 决定是否维持分区表的全局统计信息,而不做全表扫描。当开启这个参数的时候,Oracle仅需扫描分区表中有数据变化的分区来更新分区表的统计信息,而无需扫描整个分区表。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数,再次触发统计信息收集,默认值为10%。

--查询相关参数

select dbms_stats.get_prefs('PUBLISH') from dual;

select dbms_stats.get_prefs('method_opt') from dual;

select dbms_stats.get_prefs('GRANULARITY') from dual;

select dbms_stats.get_prefs('INCREMENTAL') from dual;

--查看分区表INCREMENTAL、PUBLISH的值

select dbms_stats.get_prefs('INCREMENTAL','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;

select dbms_stats.get_prefs('PUBLISH','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;

--exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');

--只收集数据变动的分区

BEGIN
  FOR j IN (select distinct TABLE_NAME
              from dba_tab_partitions p
             where table_owner = 'ACCT') LOOP
    DBMS_STATS.SET_TABLE_PREFS('ACCT', j.table_name, 'INCREMENTAL', 'TRUE');
  END LOOP;
END;

--方案二

只修改 STALE_PERCENT 参数,不创建 JOB。

STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数,再次触发统计信息收集,默认值为10%。

select dbms_stats.get_prefs('STALE_PERCENT') from dual;

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
END;

--方案三

对于非分区表,使用默认的统计信息采集方法;对于分区表,由于现有分区每天会产生一个新的分区,在保证所有分区的统计信息都收集的基础上,每天只对新生成的分区做收集。

BEGIN
  --采集非分区表的统计信息
  FOR i IN (select s.TABLE_NAME, s.num_rows
              from dba_tables s
             where s.OWNER = 'ACCT'
               and not exists
             (select distinct TABLE_NAME
                      from dba_tab_partitions p
                     where table_owner = 'ACCT'
                       and p.table_name = s.TABLE_NAME)) LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  i.TABLE_NAME,
                                  DEGREE       => 5,
                                  CASCADE      => TRUE);
  END LOOP;
  --采集分区表的统计信息  
  FOR j IN (select distinct table_name,
                            last_value(partition_name) over(partition by table_name order by partition_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) partition_name
              from dba_tab_partitions p
             where table_owner = 'ACCT') LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  tabname          => j.table_name,
                                  partname         => j.partition_name,
                                  estimate_percent => 0.000001,
                                  DEGREE           => 5,
                                  CASCADE          => TRUE);
  END LOOP;
END;

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

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

注册时间:2014-08-01

  • 博文量
    404
  • 访问量
    1181175