As an Oracle DBA, you know how difficult it is sometimes to figure out why your database performance isn’t up. You may need to hunt down the causes, but too often, you are likely to mistake the symptoms for the causes. Correctly diagnosing performance problems is the key to efficient performance tuning.
Traditionally, organizations have spent considerable amounts of effort on performance tuning, which usually tends to be quite laborious and not an exact science. Oracle Database 10g changes all that. It provides you with very powerful and accurate automatic Performance-tuning capabilities. The heart of the new automatic performance tuning is the new statistics collection facility, the Automatic Workload Repository (AWR), which automatically saves crucial performance information in the new mandatory SYSAUX tablespace.
By default, the AWR collects new performance statistics in the form of a snapshot on an hourly basis and saves the snapshots for seven days before purging them. These snapshots of database activity include resource-intensive SQL statements. The Automatic Database Diagnostic Monitor (ADDM) runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and provides recommendations for improving performance.
Oracle manages the ADDM with the help of a brand-new background process in Oracle Database 10g databases: the MMON. The MMON process schedules the automatic running of the ADDM. Each time the AWR takes a snapshot (every 60 minutes, by default), the MMON process asks the ADDM to analyze the interval between the last two snapshots it gathered. This is the default behavior of the ADDM performance analysis. Where does the ADDM store its analysis results? Not surprisingly, the ADDM stores the results in the AWR itself.
The ADDM analyzes the AWR snapshots periodically and comes up with performance recommendations, usually quantified in terms of expected benefit of various actions. Following are some of the key problems that the ADDM diagnoses:
■ Configuration issues
■ Improper application usage
■ Expensive SQL statements
■ I/O performance issues
■ Locking issues
■ Excessive parsing
■ CPU bottlenecks
■ Undersized memory allocation
■ Connection management issues, such as excessive logon/logoff statistics
Benefits Provided by the ADDM
The ADDM bases its recommendations on a holistic approach, with time spent on database activities as its main focus. Here are some of the important benefits of using the ADDM:
■ It identifies the root causes of performance problems, instead of merely focusing on the symptoms. The ADDM will automatically capture highly resource-intensive SQL statements.
■ It produces automatic performance diagnostic reports at periodic intervals.
■ You’ll experience very little performance overhead when using the tool typical ADDM analysis takes only three or four seconds.
■ The ADDM points out nonproblem areas, so you don’t waste your efforts poking around in areas with little bang for the buck.
Taking ADDM Report:
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sql;
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
115495086 DBA12 1 dba12
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 115495086 1 DBA12 dba12 NAZMUL
Using 115495086 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.
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
dba12 DBA12 139 01 Jun 2010 16:55 1
140 01 Jun 2010 18:07 1
141 01 Jun 2010 20:34 1
142 01 Jun 2010 22:00 1
143 02 Jun 2010 10:05 1
144 03 Jun 2010 09:37 1
145 03 Jun 2010 11:01 1
146 03 Jun 2010 12:00 1
147 03 Jun 2010 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 140
Begin Snapshot Id specified: 140
Enter value for end_snap: 147
End Snapshot Id specified: 147
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_140_147.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: check.txt
Using the report name check.txt
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ...
Generate a ADDM Report:
SQL> CONN SYS AS SYSDBA
SQL>exec dbms_workload_repository. Create_snapshot;
SQL> CREATE TABLE addmtest AS SELECT * FROM all_objects;
Execute this script:
SQL> begin
for i in 1..15 Loop
insert into addmtest
select * from all_objects;
delete from addmtest;
end loop ;
commit ;
end;
SQL>exec dbms_workload_repository. Create_snapshot;
@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sql;
You can check in the report some problem (Ex: I/O Wait) and recommendations.
Note: If you get no problem then force more activity in step 4.