oracle stalestats_Oracle教程：DBA任务---确保统计信息准确性

最近忙得不可开交，项目进入了cut over阶段，压力之大，前所未有。我的任务就是，负责优化long running的SQL，让其可以在3小时以内完成。昨天就出现一个Long running 的SQL，它跑了16小时，经过2小时的奋斗，终于把它优化到了2小时10分钟。

虽然那个Long running SQL 与统计信息无关，但是我还是提出要确保统计信息的准确性。作为DBA，我必须定制出收集统计信息的策略，以及相关脚本，下面就是一个关于确保统计信息准确性的脚本，拿出来分享一下。

注意：该脚本适用于数据仓库，Oracle11g，如果你是OLTP，Oracle10g，请自己修改某些条件。

DECLARE

CURSOR STALE_TABLE IS

SELECT OWNER,

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 = 'ADWU_OPTIMA_AP11'

AND SEGMENT_NAME IN

(SELECT /*+ UNNEST */ DISTINCT TABLE_NAME

FROM DBA_TAB_STATISTICS

WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')

AND OWNER = 'ADWU_OPTIMA_AP11')

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 auto',

DEGREE           => 8,

GRANULARITY      => 'ALL',

CASCADE          => TRUE);

END LOOP;

END;

/

根据实际情况，可以选择每天晚上在数据库不繁忙的时候运行上述脚本。