Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).The AWR is a repository of performance information collected by the database to aid in the tuning process 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 Oracle database. We can disable and enable the schedule job by following command:
we can disable this job by using the dbms_scheduler.disable 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 <return> 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 <return> 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
</body></html>
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.
Enjoy :-)
