定制统计信息收集策略

优化器在计算执行计划的成本时依赖于统计信息，如果没有收集统计信息，或者是统计信息过期了，那么优化器就会出现严重偏差，从而导致性能问题。因此要确保统计信息准确性。虽然数据库自带有 JOB 每天晚上会定时收集数据库中所有表的统计信息，但是如果数据库特别大，自带的 JOB 无法完成全库统计信息收集。一些资深的 DBA 会关闭数据库自带的统计信息收集 JOB，根据实际情况自己定制收集统计信息策略。

下面脚本用于收集 SCOTT 账户下统计信息过期了或者是从没收集过统计信息的表的统计信息，采样率也根据表的段大小做出了相应调整。

declare
  cursor stale_table is
    select owner,
           segment_name,
           case
             when segment_size < 1 then
              100
             when segment_size >= 1 and segment_size <= 5 then
              50
             when segment_size > 5 then
              30
           end as percent,
           6 as degree
      from (select owner,
                   segment_name,
                   sum(bytes / 1024 / 1024 / 1024) segment_size
              from DBA_SEGMENTS
             where owner = 'SCOTT'
               and segment_name in
                   (select table_name
                      from DBA_TAB_STATISTICS
                     where (last_analyzed is null or stale_stats = 'YES')
                       and owner = 'SCOTT')
             group by owner, segment_name);
begin
  dbms_stats.flush_database_monitoring_info;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => stale.owner,
                                  tabname          => stale.segment_name,
                                  estimate_percent => stale.percent,
                                  method_opt       => 'for all columns size repeat',
                                  degree           => stale.degree,
                                  cascade          => true);
  end loop;
end;

在实际工作中，我们可以根据自身数据库中实际情况，对以上脚本进行修改。

全局临时表无法收集统计信息，我们可以抓出系统中的全局临时表，抓出系统中使用到全局临时表的 SQL，然后根据实际情况，对全局临时表进行动态采样，或者是人工对全局临时表设置统计信息（DBMS_STATS.SET_TABLE_STATS）。

下面脚本抓出系统中使用到全局临时表的 SQL。

select b.object_owner, b.object_name, a.temporary, sql_text
  from dba_tables a, v$sql_plan b, v$sql c
 where a.owner = b.object_owner
   and a.temporary = 'Y'
   and a.table_name = b.object_name
   and b.sql_id = c.sql_id;