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   unlock user   expired user  
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   tnsping   network_acl  
parallel_query   sort_usage  
setup sid using oraenv   init.ora   Opatch   redo logfile member and group   rename 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.

-- 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;
/

-- Modifying snapshot setting
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;
/

-- 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