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