Oracle 11g 统计信息收集脚本

在 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1985462/