How to generate AWR report from Oracle Database
Automatic Workload Repository (AWR) is a collection of persistent system performance statistics owned by the SYS user.
It resides in SYSAUX tablespace and by default snapshots are generated once every 60min and maintained for 7 days.
An AWR report outputs a series of statistics based on the differences between snapshots that may be used to investigate performance and other issues.
Running a Basic Report
With appropriate licenses for AWR, you may generate an AWR report by executing the following script and pick the two snapshots you want to use for the sample :
$ORACLE_HOME/rdbms/admin/awrrpt.sql
Depending on the reasons for collecting the report, the default can be used, or for a more focused view, a short 10-15 minute snapshot could be used.
You will also be asked for the format of the report (text or html) along with the report name.
Generating Various Types of AWR Reports
AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format:
— awrrpt.sql
Displays various statistics for a range of snapshots Ids.
— awrrpti.sql
Displays statistics for a range of snapshot Ids on a specified database and instance.
— awrsqrpt.sql
Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.
— awrsqrpi.sql
Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
— awrddrpt.sql
Compares detailed performance attributes and configuration settings between two selected time periods.
— awrddrpi.sql
Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
Creating a SNAPSHOT Manually:
Below script can be used to create a snapshot manually.
BEGIN DBMS_WORKLOAD_REPOSITORY.create_snapshot(); END; /
Workload Repository Views
The following workload repository views are available:
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second. V$METRIC - Displays metric information. V$METRICNAME - Displays the metrics associated with each metric group. V$METRIC_HISTORY - Displays historical metrics. V$METRICGROUP - Displays all metrics groups. DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history. DBA_HIST_BASELINE - Displays baseline information. DBA_HIST_DATABASE_INSTANCE - Displays database environment information. DBA_HIST_SNAPSHOT - Displays snapshot information. DBA_HIST_SQL_PLAN - Displays SQL execution plans. DBA_HIST_WR_CONTROL - Displays AWR settings.
Automation of AWR reports :
There is no product functionality to produce the AWR reports in an automated way.The production of a report is a manual step.However, You can use the dbms_workload_repository.awr_report_text in a bash script and using cron you can run it on a daily basis for automated reports.
Generally when AWR reports are asked by Admins, this could be related to the following typical scenarios
— The performance has slowly degraded over time due to increased user activity
— The performance has slowly degraded over time due to increased or changing content
— The performance has become slower after a change of AppServer version or a AppServer patchset has been applied
— The performance has become slower after a change of Database version or a Database patchset has been applied
With appropriate licenses for AWR, you may generate an AWR report by executing the following script and pick the two snapshots you want to use for the sample :
$ORACLE_HOME/rdbms/admin/awrrpt.sql
A short period such as 30 or 60 minutes should be ideal.
You will also be asked for the format of the report (text or html) along with the report name. Please ensure that you gather TEXT output rather than HTML for this.
Try and get a report for a “good” period and a “bad” period if possible. This will help to highlight important issues quickly.
In addition to the AWR reports, 10g databases also have an advisory feature called ADDM which may help to pinpoint the key problem areas and how to fix them.
Below is a DRY run of creating a AWR report. Please follow the same steps to create your own AWR report for debugging.
C:\Users\Admin>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 9 16:04:02 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> exec dbms_workload_repository.create_snapshot PL/SQL procedure successfully completed. ( Reproduce the issue happening so that it will capture the details and once captured end the snapshot using below command ) SQL> exec dbms_workload_repository.create_snapshot PL/SQL procedure successfully completed. SQL> @?/rdbms/admin/awrrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1780233149 TECHPASTE 1 techpaste Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1780233149 1 TECHPASTE techpaste TechServer1 Using 1780233149 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. Enter value for num_days: Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- techpaste TECHPASTE 1162 02 Jul 2012 10:25 1 1163 02 Jul 2012 11:30 1 1164 02 Jul 2012 12:30 1 1165 02 Jul 2012 13:30 1 1166 02 Jul 2012 14:30 1 1167 02 Jul 2012 15:30 1 1168 02 Jul 2012 16:30 1 1169 02 Jul 2012 17:30 1 1170 03 Jul 2012 09:49 1 1171 03 Jul 2012 10:30 1 1172 03 Jul 2012 11:30 1 1173 03 Jul 2012 12:30 1 1174 03 Jul 2012 13:30 1 1175 03 Jul 2012 14:30 1 1176 03 Jul 2012 15:30 1 1177 03 Jul 2012 16:30 1 1178 03 Jul 2012 17:30 1 1179 03 Jul 2012 18:30 1 1180 04 Jul 2012 10:25 1 1181 04 Jul 2012 11:30 1 1182 04 Jul 2012 12:30 1 1183 04 Jul 2012 13:30 1 1184 04 Jul 2012 14:30 1 1185 04 Jul 2012 15:30 1 1186 04 Jul 2012 16:30 1 1187 04 Jul 2012 17:30 1 1188 05 Jul 2012 09:10 1 1189 05 Jul 2012 10:30 1 1190 05 Jul 2012 11:30 1 1191 05 Jul 2012 12:30 1 1192 05 Jul 2012 13:30 1 1193 05 Jul 2012 14:30 1 1194 05 Jul 2012 15:30 1 1195 05 Jul 2012 16:30 1 1196 05 Jul 2012 17:30 1 1197 06 Jul 2012 09:23 1 1198 06 Jul 2012 10:30 1 1199 06 Jul 2012 11:30 1 1200 06 Jul 2012 12:30 1 1201 06 Jul 2012 13:30 1 1202 06 Jul 2012 14:30 1 1203 09 Jul 2012 10:53 1 1204 09 Jul 2012 11:03 1 1205 09 Jul 2012 11:03 1 1206 09 Jul 2012 11:05 1 1207 09 Jul 2012 11:05 1 1208 09 Jul 2012 12:30 1 1209 09 Jul 2012 13:30 1 1210 09 Jul 2012 14:30 1 1211 09 Jul 2012 15:30 1 1212 09 Jul 2012 16:04 1 1213 09 Jul 2012 16:04 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1212 Begin Snapshot Id specified: 1212 Enter value for end_snap: 1213 (1212 and 1213 is the point where we reproduced the issue and took snapshot to diagnose the issue) Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_1212_1213.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: awr_report_techpaste.com.html (--------------) (--------------) (--------------) End of Report </body></html> Report written to awr_report_techpaste.com.html SQL>exit
The awr_report_techpaste.com.html report gets generated at the same directory where you have executed the sql commands.
In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.