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 snapshoton 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
