Oracle AWR ADDM Report

AWR

addmrpt
awrrpt
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql -- in rac, select a single instance

Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues (Doc ID 1301503.1) 

-- get snapshot error
select * from wrm$_snap_error where dbid = (select dbid from v$database) order by snap_id; 

-- get snapshot interval
-- 1):
select snap_id,snap_level,
to_char(begin_interval_time, 'mm/dd/yyyy hh24:mi:ss') begin,
begin_interval_time 
from dba_hist_snapshot 
order by begin_interval_time desc;

DBA_HIST_SNAPSHOT 

-- 2): get snapshot flush time
-- check collection time issues
col instance_number format 999 heading 'Inst'
col startup_time format a15 heading 'Startup'
col begin_interval_time format a15 heading 'Begin snap'
col end_interval_time format a15 heading 'End Snap'
col flush_elapsed format a20 heading 'flush elapsed'
col error_count format 9999 heading 'Err#'

SELECT *
FROM
  (SELECT instance_number, startup_time, begin_interval_time,
    end_interval_time, flush_elapsed, error_count
  FROM dba_hist_snapshot
  ORDER BY begin_interval_time DESC
  )
WHERE rownum < 5; 


-- 3): recent snapshot time and current snapshot interval 
col systimestamp form a35
col most_recent_snap_time form a25
col snap_interval form a17
select systimestamp, most_recent_snap_time, snap_interval 
from wrm$_wr_control where dbid = (select dbid from v$database); 


V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

-- snapshot interval and retention setting

select * from dba_hist_wr_control;

select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

-- Modifying snapshot setting
execute dbms_workload_repository.modify_snapshot_settings ( interval => 30 );

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 1576800);

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

begin
dbms_workload_repository.modify_snapshot_settings(
retention => 7200,
interval =>60 ,
topnsql =>10 ,
dbid => 123661118);
end;
/

awr snapshot setting 

-- manually create a snapshot
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
EXEC dbms_workload_repository.create_snapshot;

-- manually drop a range of snapshots
BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22, 
    high_snap_id => 32);
END;
/

-- show AWR settings
select * from DBA_HIST_WR_CONTROL;
@@$ORACLE_HOME/rdbms/admin/addmrpt.sql
-- Windows
@@%ORACLE_HOME\rdbms\admin\addmrpt.sql

ADDM Report
addmrpt.sql

-- DBIO_EXPECTED, usually 5000 to 20000 microsoconds
EXECUTE DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);

rem --------------------------------------------
BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
    task_name         => '100_120_AWR_SNAPSHOT',
    task_desc         => 'Advisor for snapshots 100 to 120.');

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => '100_120_AWR_SNAPSHOT',
    parameter => 'START_SNAPSHOT',
    value     => 100);

  DBMS_ADVISOR.set_task_parameter (
    task_name => '100_120_AWR_SNAPSHOT',
    parameter => 'END_SNAPSHOT',
    value     => 120);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => '100_120_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('100_120_AWR_SNAPSHOT') AS report
FROM   dual;

SET PAGESIZE 24