How To Automatic Workload Repository (AWR) in Oracle

Oracle feature provided some execution collecting and news tools over the period. Originally the UTLBSTAT/UTLESTAT scripts were used to varan execution poetics. Oracle8i introduced the Statspack functionality which Oracle9i considerable. In Oracle 10g statspack has evolved into the Autoloading Workload Repository (AWR).The AWR is a intimate of execution collection equanimous by the database to aid in the tuning impact for DBAs. Oracle 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when we create a new Seer database. We can injure and enable the schedule job by masses overlook: we can alter this job by using the dbms_scheduler.invalid procedure as below : Exec dbms_scheduler.disable('GATHER_STATS_JOB'); And we can enable the job using the dbms_scheduler.enable procedure as below : Exec dbms_scheduler.enable('GATHER_STATS_JOB'); AWR consists of a collection of performance statistics including : * Wait events used to identify performance problems. * Time model statistics indicating the amount of DB time associated with a process from the v$sess_time_model and v$sys_time_model views. * Active Session History (ASH) statistics from the v$active_session_history view. * Some system and session statistics from the v$sysstat and v$sesstat views. * Object usage statistics. * Resource intensive SQL and PL/SQL. The resource intensive SQL and PL/SQL section of the report can be used to focus tuning efforts on those areas that will yield the greatest returns. The statements are ordered by several criteria including : * SQL ordered by Elapsed Time * SQL ordered by CPU Time * SQL ordered by Gets * SQL ordered by Reads * SQL ordered by Executions * SQL ordered by Parse Calls * SQL ordered by Sharable Memory Several of the automatic database tuning features require information from the AWR to function correctly, including: * Automatic Database Diagnostic Monitor * SQL Tuning Advisor * Undo Advisor * Segment Advisor How to generate AWR report ? There are two scripts that are provided by oracle to generate the AWR report. The scripts are available in the directory $ORACLE_HOME\\rdbms\\admin. The two scripts are 1.) awrrpt.sql : If we have only One Oracle Database then run awrrpt.sql sql script. 2.) awrrpti.sql : If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation. By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the below procedure : BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL. interval => 15); -- Minutes. Current value retained if NULL. END; / Here we have alter the snapshot interval to 15min. It is recommended that 15 Minutes is enough in two snapshot for better performance bottleneck. AWR using Enterprise Manager : The automated workload repository administration tasks have been included in Enterprise Manager. The \"Automatic Workload Repository\" page is accessed from the main page by clicking on the \"Administration\" link, then the \"Workload Repository\" link under the \"Workload\" section. The page allows us to modify AWR settings or manage snapshots without using the PL/SQL APIs. Here is the Demo of the AWR report . C:\\>sqlplus sys/xxxx@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 16 11:42:19 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @D:\\app\\Neerajs\\product\\11.2.0\\dbhome_1\\RDBMS\\ADMIN\\awrrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1281052636 ORCL 1 orcl 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 ------------ -------- ------------ ------------ ------------ * 1281052636 1 ORCL orcl xxxx Using 1281052636 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 without specifying a number lists all completed snapshots. Enter value for num_days: (Press Enter to see all the snapshots) Listing all Completed Snapshots Instance DB Name Snap Id Snap Started Level --------- ------------ --------- ------------------ ----- orcl ORCL 1 08 Jun 2011 11:30 1 3 08 Jun 2011 14:41 1 4 08 Jun 2011 15:30 1 . Data is truncated . 120 16 Jun 2011 05:30 1 121 16 Jun 2011 06:30 1 122 16 Jun 2011 07:30 1 123 16 Jun 2011 08:30 1 124 16 Jun 2011 09:30 1 125 16 Jun 2011 10:30 1 126 16 Jun 2011 11:30 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 125 Begin Snapshot Id specified: 125 Enter value for end_snap: 126 End Snapshot Id specified: 126 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_125_126.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: (Press enter if you want to use the above name) Using the report name awrrpt_1_125_126.html . . Report is truncated . . End of Report Report written to awrrpt_1_125_126.html SQL> In the above report the line which are shaded with red colour are the entered values when it prompts.