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

最新推荐文章于 2018-05-07 08:17:00 发布

ctest011501

最新推荐文章于 2018-05-07 08:17:00 发布

阅读量135

点赞数

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博客 ” ，链接：http://blog.itpub.net/40239/viewspace-621701/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-621701/