Oracle AWR Report

AWR Objects
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to and version 18.0 is going to be available soon. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Warning AWR is licensed as part of the diagnostic pack. Be sure you are licensed before you use this in a production environment.
Data Dictionary Objects
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
awrgrpt.sql AWR global report
awrgrpti.sql WR RAC 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
awrupd12.sql Updates AWR data to version 12c. Only modifies AWR data that has been imported using awrload.sql, or data from before changing the database DBID
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) INST_START,
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
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
SQL> @?/rdbms/admin/awrrpt.sql

Related Topics
Active Session History
Built-in Functions
Built-in Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved