Title: 禁止在AWR后自动运行ADDM
Date:2009-12-3
缺省情况下，每次运行AWR后，ADDM都会自动运行，去找出INSTANCE或者SQL可以优化的地方，并给出建议。可以通过修改隐含参数_addm_auto_enable 来禁止这种行为。

 SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');
END;
 /  2    3    4  
PL/SQL procedure successfully completed.
SQL> select max(snap_id) from DBA_HIST_SNAPSHOT ;
MAX(SNAP_ID)
------------
         625
SQL> SELECT dbms_advisor.GET_TASK_REPORT(task_name)
  2  FROM dba_advisor_tasks
WHERE task_id = (
SELECT max(t.task_id)
FROM dba_advisor_tasks t,
dba_advisor_log l
WHERE t.task_id = l.task_id AND
t.advisor_name = 'ADDM' AND
l.status = 'COMPLETED');  3    4    5    6    7    8    9  
DBMS_ADVISOR.GET_TASK_REPORT(TASK_NAME)
--------------------------------------------------------------------------------
          DETAILED ADDM REPORT FOR TASK 'ADDM:3657415769_1_625' WITH ID 666

这时INSTANCE是自动运行ADDM。修改参数_addm_auto_enable 为FALSE：

SQL> ALTER SYSTEM set "_addm_auto_enable"=false;
System altered.

再次运行AWR：

SQL> BEGIN
  2    DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');
END;
 /  3    4  
PL/SQL procedure successfully completed.
SQL>  select max(snap_id) from DBA_HIST_SNAPSHOT ;
MAX(SNAP_ID)
------------
         626
SQL> SELECT dbms_advisor.GET_TASK_REPORT(task_name)
  2  FROM dba_advisor_tasks
WHERE task_id = (
SELECT max(t.task_id)
FROM dba_advisor_tasks t,
dba_advisor_log l
WHERE t.task_id = l.task_id AND
t.advisor_name = 'ADDM' AND
l.status = 'COMPLETED');  3    4    5    6    7    8    9  
DBMS_ADVISOR.GET_TASK_REPORT(TASK_NAME)
--------------------------------------------------------------------------------
          DETAILED ADDM REPORT FOR TASK 'ADDM:3657415769_1_625' WITH ID 666

ADDM不在AWR后自动运行。
-END-

ITPub博客

10G新特性系列：禁止在AWR后自动运行ADDM

最新文章