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


DBA_HIST_SNAPSHOT 


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