Using DBMS_ADDM Package

The dbms_addm package eases the addm management.  It consists of the following procedures and functions:

• ANALYZE_DB:  creates an addm task for analyzing the database globally
• ANALYZE_INST:  Create an addm task for analyzing a local instance
• ANALYZE_PARTIAL:  Create an ADDM task for analyzing a subset of instances
• DELETE:  Deletes a created ADDM task( of any kind)
• GET_REPORT:  Get the default text report of an executed ADDM task
• Parameters 1000,1001:  start and end snapshot

  declare

  tname varchar2(30) :='My Task';

  begin

  dbms_addm.analyze_db(tname, 3000,3001);

  dbms_output.put_line(tname);

  end;

SQL> /

My Task

PL/SQL procedure successfully completed.

SQL> select execution_name, finding_name, parent, impact

  2  from dba_addm_findings

  3  where task_name = 'My Task';

EXECUTION_NAME  FINDING_NAME                   PARENT       IMPACT

--------------- ------------------------------ ------ ------------

EXEC_8808       normal, successful completion       0            0

SQL> select execution_name, finding_name,message

  2  from dba_addm_findings

  3  where task_name = 'My Task';

EXECUTION_NAME  FINDING_NAME                   MESSAGE

--------------- ------------------------------ ------------------------------------------------------------

EXEC_8808       normal, successful completion  There was no significant database activity to run the ADDM.

select dbms_addm.get_report('My Task') from dual;

select * from dba_advisor_finding_names

begin

dbms_addm.insert_finding_directive (NULL,

'MY undersized SGA directive 2',

'Undersized SGA',

2,

10);

:tname :='my instance ADDM task';

dbms_addm.analyze_inst(:tname, 3000,3002);

END;

/

SQL> SELECT task_name, directive_name, description

  2  FROM dba_addm_task_directives;

TASK_NAME                      DIRECTIVE_NAME

------------------------------ ------------------------------

DESCRIPTION

-------------------------------------------------------------------------------------------------------------------------

my instance ADDM task          Finding Filter

Filter findings of type "Undersized SGA" if the impact is less than 2 active sessions or the impact is less than 10 percent of database time for the analysis period.

SQL> exec dbms_addm.delete('my instance ADDM task');

PL/SQL procedure successfully completed.

select task_name, message from dba_advisor_actions

ADVISOR VIEWS

DBA_ADVISOR_FINDINGS         USER_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMENDATIONS   USER_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_ACTIONS          USER_ADVISOR_ACTIONS