Oracle 11g 统计信息收集脚本

小鱼儿 2024-04-17 23:50 40阅读 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/，如需转载，请注明出处，否则将追究法律责任。

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