My Blog

January 5, 2009

Automatic Workload Repository (AWR) & ADDM(Automatic Database Diagnostic Monitor)

Filed under: Oracle — by enotebook @ 4:30 pm
Tags: , ,

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).

bash-3.00$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin
bash-3.00$ ls |grep awr
awrddinp.sql
awrddrpi.sql
awrddrpt.sql
awrextr.sql
awrinfo.sql
awrinpnm.sql
awrinput.sql
awrload.sql
awrrpt.sql
awrrpt_1_142_143.html
awrrpti.sql
awrsqrpi.sql
awrsqrpt.sql
catawrtb.sql
catawrvw.sql
dbmsawr.sql
prvtawr.plb
bash-3.00$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.2.0 – Production on Mon Jan 5 10:58:51 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

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

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
1202538587 ORCL                1 orcl1

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:

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
1202538587        2 ORCL         orcl2        solaris02
* 1202538587        1 ORCL         orcl1        solaris01

Using 1202538587 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: 1

Listing the last day’s Completed Snapshots

Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
orcl1        ORCL               134 05 Jan 2009 00:00      1
135 05 Jan 2009 01:00      1
136 05 Jan 2009 02:00      1
137 05 Jan 2009 03:00      1
138 05 Jan 2009 04:00      1
139 05 Jan 2009 05:00      1
140 05 Jan 2009 06:00      1
141 05 Jan 2009 07:00      1
142 05 Jan 2009 08:00      1
143 05 Jan 2009 09:00      1
144 05 Jan 2009 10:00      1
145 05 Jan 2009 11:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 144
Begin Snapshot Id specified: 144

Enter value for end_snap: 145
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_144_145.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Report written to awrrpt_1_144_145.html
SQL>

Now you can check the file of awrrpt_1_144_145.html to see the detailed info what likes you have been using statpack to generate.
As a optional part of oracle database performance tuning process, we can use ADDM(Automatic Database Diagnostic Monitor) to check database system  by running the scripts of @?/rdbms/admin/addmrpt.sql.
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes:

* CPU load
* Memory usage
* I/O usage
* Resource intensive SQL
* Resource intensive PL/SQL and Java
* RAC issues
* Application issues
* Database configuration issues
* Concurrency issues
* Object contention

For more details, refer to following links:
http://www.oracle.com/technology/pub/articles/10gdba/week6_10gdba.html
http://www.oracle-base.com/articles/10g/AutomaticWorkloadRepository10g.php
http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_talking.html
http://www.oracle-base.com/articles/10g/AutomaticDatabaseDiagnosticMonitor10g.php

Advertisements

Blog at WordPress.com.