在Oracle 数据库中,除了默认的3个自动运行的任务之外,还有一个定期执行的后台任务ADDM job:
SQL> select client_name ,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------- ----------------
auto optimizer stats collection ENABLED
auto space advisor DISABLED
sql tuning advisor DISABLED
Oracle 对这个ADDM job 的说明如下:
The ADDM auto job is automated job and is performed every time AWR snapshot is taken and the results are saved in database. For Oracle systems, the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis or snapshots, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system。
也就就是说,在每次生成AWR快照的时候,都会执行一次ADDM job,用来分析AWR的数据。 正常情况下,ADDM JOB 会在awr快照生成后执行并会短时间内结束。 但也可能出现意外情况,ADDM job 会一直执行(executing 状态),不会结束。
select owner, task_name, last_execution, execution_start, status
from dba_advisor_tasks
where execution_end is null
order by execution_start desc;
OWNER TASK_NAME LAST_EXECUTION EXECUTION STATUS
------------------------------ ------------------------------ ------------------------------ --------- -----------
SYS ADDM:51442193_1_18875 EXEC_31428 09-JAN-13 EXECUTING
SYS SYS_AUTO_SPCADV_44281408012013 EXEC_31427 08-JAN-13 EXECUTING
SYS ADDM:51442193_1_18851 EXEC_31417 08-JAN-13 EXECUTING
SYS ADDM:51442193_1_18827 EXEC_31381 07-JAN-13 EXECUTING
SYS SYS_AUTO_SPCADV_32261506012013 EXEC_31380 06-JAN-13 EXECUTING
SYS ADDM:51442193_1_18803 EXEC_31365 06-JAN-13 EXECUTING
Oracle 也没有找到这个问题的原因,只说研发在分析。 不过Oracle 提供了2个解决方法。
解决方法1: 禁止在每次AWR快照后运行ADDM 任务
ALTER SYSTEM set “_addm_auto_enable”=false;
解决方法2: 删除异常的TASK
删除单个task:
Exec DBMS_ADVISOR.DELETE_TASK (‘ADDM:147879890_3_25823’)
删除多个task:
Exec DBMS_ADVISOR.DELETE_TASK (‘ADDM:%’);
参考:
ADDM Jobs are in Status Executing or Running for a Long Time (Doc ID 1557550.1)
版权声明:本文为博主原创文章,未经博主允许不得转载。