Oracle AWR ADDM Report
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