Oracle AWR Report
Version 18.1.0.0

AWR Objects
Library Note Morgan's Library Page Header
Are you prepared for the release of Oracle Database 18c ... the first autonomous database? We are here at the Library. It is time for DBAs to stop fighting robots with their fingers and losing ... time to start using our intelligence and winning.
Warning AWR is licensed as part of the diagnostic pack. Be sure you are licensed before you use this or you may create a substantial financial liability for your organization.
Data Dictionary Objects SELECT UNIQUE owner, object_name, object_type
FROM dba_objects
WHERE object_name LIKE '%AWR%'
AND object_type NOT LIKE '%SYNONYM%'
AND object_type NOT LIKE '%PARTITION%'
AND object_type NOT LIKE '%BODY%'
ORDER BY 2,1;

-- in version 18.0.0.0 this statement retrieves 498 objects
Source Files

Located at {ORACLE_HOME}/rdbms/admin/
Location / Name Description
awrddrpi.sql SQL*Plus command file to report on differences between differences between values recorded in two pairs of snapshots.
awrddrpt.sql This script defaults the dbid and instance number to the current instance connected-to, then calls awrddrpi.sql to produce an AWR Compare Periods report.
awrextr.sql SQL/Plus script to extract data from the AWR data into a dump (.dmp) file. Use in conjuntion with awrload.sql.
awrgdrpi.sql RAC version of the AWR Compare Period Report.
awrgdrpt.sql Defaults the dbid to the connected instance, defaults the instance list to all available instances and then calls awrgdrpi.sql to produce an AWR RAC Compare Periods report.
awrgrpt.sql Defaults the dbid to that of the current instance then calls awrgrpti.sql to produce the Workload Repository RAC report.
awrgrpti.sql SQL*Plus command file to report on RAC-wide differences between values recorded in two snapshots.
awrinfo.sql Output general AWR information such as the size, data distribution, etc. in SYSAUX. The intended use of this script is for diagnosing abnormalities in AWR and not for diagnosing issues in the database.
awrload.sql SQL/Plus script to load data into the AWR from a dump (.dmp) file. Use in cnjunction with awrextr.sql.
awrmacro.sql Utility that defines macros for SQL*Plus scripts. Macros are implemented as substitution variables. The caller needs to pass 2 required arguments, followed by any number of optional arguments.
awrrptidc.sql SQL*Plus script that implements the logic of reading the container DB Id (con_dbid) for an AWR report.
awrrpti.sql SQL*Plus command file to report on differences between values recorded in two snapshots.
awrrpt.sql defaults the dbid and instance number to that of the current instance and calls awrrpti.sql to produce the Workload Repository report.
awrsqrpi.sql SQL*Plus command file to report on differences between values recorded in two snapshot.
awrsqrpt.sql Defaults the dbid and instance number to that of the current intance and calls awrsqrpi.sql produce a Workload report for a sql statement.
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.
catawrcdbvw.sql Catalog script for AWR CDB views.
catawrpdbvw.sql Catalog script for AWR PDB views used to create the Workload Repository schema.
catawrpd.sql Creates AWR views with package dependencies.
catawrrtvw.sql Catalog script for AWR Root views.
catawr.sql Driver script that calls other scripts that creates tables, views, package for AWR.
catawrtb.sql Catalog script for AWR tables used to create the Workload Repository schema.
catawrtv.sql Catalog script for AWR tables and views.
catawrvw.sql Catalog script for AWR Views. Used to create the Workload Repository schema.
catnoawr.sql Catalog script used to drop the Workload Repository schema.
spawrio.sql AWR IO Intensity Report.
spawrrac.sql Server Performance AWR RAC report
 
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 Basic 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
Built-in Functions
Built-in Packages
DBMS_AWR_REPORT_LAYOUT
DBMS_MANAGEMENT_BOOTSTRAP
DBMS_MANAGEMENT_PACKS
DBMS_WORKLOAD_REPOSITORY
StatsPack
What's New In 12cR2
What's New In 18cR1

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