Oracle AWR ADDM Report

I am in the process of putting my notes into web pages, please keep checking in later for more contents

quick link

sql one liner   random password   unlock user   expired user   User's Last Password Change Date  
oracle job   restricted session   locked_object   kill session   Oracle Deployment Hanging   Sort Usage  
kill_session   active_session   session_blocker   session_wait?  
sqlplus   Flashback Database   Archive Log  
Oracle Startup   find_objects   find_object_source   table index   index rebuild   Chained Rows   Materialized Views   seg space  
Oracle Basics   External Links  
get dblink   create_db_link   sqlnet   tnsping   network_acl  
parallel_query   sort_usage  
setup sid using oraenv   init.ora   Opatch   redo logfile member and group   rename database file   Cleanup Database File  

top   prev   next  

top   prev   next  

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