oracle统计信息搜集

Oracle执行计划不准往往跟统计信息不准有关，如何搜集呢

常用命令

创建一个分析表，保存之前的分析值：
dbms_stats.create_stat_table(ownname=<'TEST',stattab=<'STAT_TABLE');
分析表 
dbms_stats.gather_table_stats(ownname=<'TEST',tabname=<'T1');
导出表分析信息到stat_table中
dbms_stats.export_table_stats(ownname=<'TEST',tabname=<'T1',stattab=<'STAT_TABLE');
删除分析信息
dbms_stats.delete_table_stats(ownname=<'TEST',tabname=<'T1');
导入分析信息
dbms_stats.import_table_stats(ownname=<'TEST',tabname=<'T1',stattab=<'STAT_TABLE');
查询分析数据
SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner = 'TEST' AND table_name = 'T1';

如果数据库自动搜集任务关闭，可添加一个定时任务定期执行，：

--owner 根据实际情况修改
DECLARE
  CURSOR STALE_TABLE IS
    SELECT OWNER,
           '"'||SEGMENT_NAME||'"' SEGMENT_NAME,
           CASE
             WHEN SIZE_GB < 0.5 THEN
              30
             WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN
              20
             WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN
              10
             WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN
              5
             WHEN SIZE_GB >= 10 THEN
              1
           END AS PERCENT,
           8 AS DEGREE
      FROM (SELECT OWNER,
                   SEGMENT_NAME,
                   SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB
              FROM DBA_SEGMENTS
             WHERE OWNER = 'XXX'
               AND SEGMENT_NAME IN
                   (SELECT /*+ UNNEST */ DISTINCT TABLE_NAME
                      FROM DBA_TAB_STATISTICS
                     WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')
                       AND OWNER = 'XXX')
             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           => 8,
                                  GRANULARITY      => 'ALL',
                                  CASCADE          => TRUE);
  END LOOP;
END;
/

DBMS_STATS.GATHER_TABLE_STATS的语法如下:

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER,   block_sample BOOLEAN,  method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2,  statid VARCHAR2,   statown VARCHAR2,   no_invalidate BOOLEAN,  force BOOLEAN);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:决定并行度.默认值为null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascace:是收集索引的信息.默认为falase.

stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.