Oracle - Getting an ADDM Report Manually - Not Using OEM

Getting an ADDM Report Manually – Not Using OEM

Basically, generating an ADDM report requires that a ADDM task was created and executed. And creating an ADDM task requires that at least two “AWR snapshots” exist and are identified.

To run the report only – if ADDM generation is already complete – query for the task name, then use DBMS_ADDM.GET_REPORT with the queried task_name as follows:

SQL > select task_name, status from dba_advisor_tasks where task_id = (select max( task_id) from dba_advisor_tasks where status =’ COMPLETED’ );

ADDM: 4227381283_3_99999 COMPLETED

SQL > SET LONG 1000000 PAGESIZE 0;

SQL > SELECT DBMS_ADDM.GET_REPORT(‘ ADDM: 4227381283_3_99999’) from dual;

If the query above returns no rows, you’ll need to identify or create an ADDM task. If no ADDM task exists. You’ll need to create that from two AWR snapshots.

If you need to take a snapshot or two manually here is the PL/SQL procedure call to do that:

EXEC dbms_workload_repository.create_snapshot;

And if you want to query your snapshots:

set lines 200 pages 999
select
snap_id, snap_level, to_char(begin_interval_time, ‘dd/mm/yy hh24:mi:ss’), to_char(end_interval_time, ‘dd/mm/yy hh24:mi:ss’) end
from dba_hist_snapshot
order by snap_id;
See MOSC note 1301503.1: “Troubleshooting: AWR Snapshot Collection issues”

Manually running ADDM task adds one extra step. Create the ADDM task:

BEGIN
— Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => ‘ADDM’,
task_name => ‘540_541_AWR_SNAPSHOT’,
task_desc => ‘Advisor for snapshots 540 to 541.’);
— Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => ‘540_541_AWR_SNAPSHOT’,
parameter => ‘START_SNAPSHOT’,
value => 540);
DBMS_ADVISOR.set_task_parameter (
task_name => ‘540_541_AWR_SNAPSHOT’,
parameter => ‘END_SNAPSHOT’,
value => 541);
— Execute the task.
DBMS_ADVISOR.execute_task(task_name => ‘540_541_AWR_SNAPSHOT’);
END;
/
— Display the report.
SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF
SELECT DBMS_ADVISOR.get_task_report(‘540_541_AWR_SNAPSHOT’) AS report
FROM dual;
SET PAGESIZE 24

Post Views: 7