Oracle AWR Report
Version 12.1.0.1

AWR Objects
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Warning The 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
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) 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
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

Related Topics
Active Session History
AWRINFO_UTIL
DBMS_AWR_REPORT_LAYOUT
DBMS_WORKLOAD_REPOSITORY

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-2014 Daniel A. Morgan All Rights Reserved