Oracle AWR Report

Oracle AWR Report Version 11.2.0.3

AWR Objects

Note: The AWR is licensed as part of the diagnostic pack. Be sure you are licensed before you use this in production. Those scripts that are highlighted in blue have bug fixes in release 11.2.0.3.

Data Dictionary Objects

AW$AWREPORT	AWRRPT_TEXT_TYPE_TABLE	DBMS_FEATURE_AWR
AWRRPT_HTML_TYPE_TABLE	DBA_HIST_SNAPSHOT	DBMS_WORKLOAD_REPOSITORY
AWRRPT_INSTANCE_LIST_TYPE	DBMS_AWR_REPORT_LAYOUT

Source Files

Located at {ORACLE_HOME}/rdbms/admin/

Location / Name	Description
awrddrpi.sql	Reports on differences between snapshot pairs
awrddrpt.sq	Defaults the dbid and instance number
awrextr.sql	Extracts AWR data into a .DMP file
awrgdrpi.sql	WR global compare period report
awrgdrpt.sql	AWR global difference report
awrgrpti.sql	WR RAC global report
awrgrpt.sql	AWR global report
awrinfo.sql	Output general AWR information
awrload.sql	Supports loading .DMP file data into AWR
awrrpt.sql	Defaults to current instance
awrrpti.sql	Select dbid and instance
awrsqrpi.sql	Difference report between two snapshots
awrsqrpt.sql	Same as awrsqrpi but defaults dbid & instance

Query of Available Repository Snapshots

Available Snapshots Query

col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT, s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

Change Statistics Collection

Statistics Level

STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}

SELECT name, value
FROM gv$parameter
WHERE name like 'stat%';

-- modifyable with ALTER SYSTEM and ALTER SESSION

Produce Report

Create AWR Report

-- note full listing of source files at the top of the page
{$ORACLE_HOME}/rdbms/admin/awrrpt.sql

SQL> @?/rdbms/admin/awrrpt.sql