10G新特性系列：禁止在AWR後自動執行ADDM

kewin發表於2009-12-04
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章