Oracle自动收集统计信息JOB失败解决
Oracle自动收集统计信息JOB总是失败，原因大都因为数据量较大,在预计的时间窗口内统计信息未收集完毕。
1、查看JOB运行状态
SQL> SELECT LOG_ID, JOB_NAME, STATUS, ADDITIONAL_INFO
2 FROM DBA_SCHEDULER_JOB_LOG
3 WHERE JOB_NAME = ‘GATHER_STATS_JOB’
4 AND STATUS <> ‘SUCCEEDED’
5 ORDER BY 1
6 /
LOG_ID JOB_NAME STATUS ADDITIONAL_INFO
———- —————- ———- —————————————————————–
6207 GATHER_STATS_JOB STOPPED REASON=”Stop job called because associated window was closed
”
6228 GATHER_STATS_JOB STOPPED REASON=”Stop job called because associated window was closed
”
6231 GATHER_STATS_JOB STOPPED REASON=”Stop job called because associated window was closed
”
6305 GATHER_STATS_JOB STOPPED REASON=”Stop job called because associated window was closed
”
SQL>
可见ADDITIONAL_INFO原因基本为：REASON=”Stop job called because associated window was closed”
2、查看下dbms_stats默认的参数设定
SQL> SELECT SNAME AS PARAMETER, NVL(SPARE4, SVAL1) AS DEFAULT_VALUE
2 FROM SYS.OPTSTAT_HIST_CONTROL$
3 /
PARAMETER DEFAULT_VALUE
—————————— ——————————
SKIP_TIME
STATS_RETENTION 31
TRACE 0
DEBUG 0
SYS_FLAGS 1
CASCADE DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE NULL
METHOD_OPT FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
GRANULARITY AUTO
AUTOSTATS_TARGET AUTO
12 rows selected.
SQL>
3、用如下命令可以调整一下参数设定
SQL> execute dbms_stats.set_param(pname => ‘DEGREE’, pval =>2);
SQL> execute dbms_stats.set_param(pname => ‘ESTIMATE_PERCENT’, pval =>2);
调整后结果如下：
SQL> SELECT SNAME AS PARAMETER, NVL(SPARE4, SVAL1) AS DEFAULT_VALUE
2 FROM SYS.OPTSTAT_HIST_CONTROL$
3 /
PARAMETER DEFAULT_VALUE
—————————— ——————————
SKIP_TIME
STATS_RETENTION 31
TRACE 0
DEBUG 0
SYS_FLAGS 1
CASCADE DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT 1
DEGREE 2
METHOD_OPT FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
GRANULARITY AUTO
AUTOSTATS_TARGET AUTO
12 rows selected.
SQL>
4、其它说明
GATHER_STATS_JOB默认ESTIMATE_PERCENT是AUTO,由ORACLE自动选取大小，在实际中这个选取的基本是100%,如果数据量大，在规定的时间窗口自然收集不完。经过上面的参数调整实际观测可以很好解决问题。
近期评论