Oracle | AWR Report

Oracle AWR Report Version 21c

AWR Objects

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 21.3 this statement retrieves 517 objects

Source Files

Located at {ORACLE_HOME}/rdbms/admin/

Location / Name	Description
awrblmig.sql	Migrates the AWR Baseline data from Rem the renamed BL tables back to the base tables.
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.
awrgdinp.sql	AWR Glopal Compare Period Report Input variables
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.
awrginp.sql	Code used for AWR RAC report. Rem This script gets the dbid,eid,filename,etc from the user.
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.
awrinput.sql	Common code used for SWRF reports and ADDM. This script gets the dbid,eid,filename,etc from the user for both components to use.
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.
awrrpt.sql	defaults the dbid and instance number to that of the current instance and calls awrrpti.sql to produce the Workload Repository report.
awrrpti.sql	SQL*Plus command file to report on differences between values recorded in two snapshots.
awrrptidc.sql	SQL*Plus script implements reading the container DB Id (con_dbid) for an AWR 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.
catawr.sql	Driver script that calls other scripts that creates tables, views, package for AWR.
catawrcdbvw.sql	Catalog script for AWR CDB views.
catawrpd.sql	Creates AWR views with package dependencies.
catawrpdbvw.sql	Catalog script for AWR PDB views used to create the Workload Repository schema.
catawrrtvw.sql	Catalog script for AWR Root views.
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%';

      

      NAME                           
      VALUE

      ------------------------------ ------------------------------

      statistics_level               
      TYPICAL

      

       -- modifiable with ALTER SYSTEM and ALTER SESSION

Produce Report

Create Basic AWR Report only which is of limited, if any, value -- note full listing of source files at the top of the page {$ORACLE_HOME}/rdbms/admin/awrrpt.sql

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