After going through the information available in a Workload Repository Report, you might feel a bit daunted in trying to interpret it all. Further, you may ask yourself, “Do I need to read through these reports every 30 minutes? Once a day? Once a week?” The answer depends on the quality of your social life. In all seriousness, however, the good news is that you do not have to read these reports on a twice-hourly basis, because the Oracle Automatic Database Diagnostic Monitor (ADDM) will do that for you.
ADDM is constantly running in the background, monitoring information that is collected in the Workload Repository. ADDM uses this data, as well as data from the Active Session History (ASH), and automatically analyzes the information to provide proactive recommendations on tuning and performance issues. The Home screen in OEM has an Advice section, with links to ADDM findings, as well as an ADDM Analysis section where this information is displayed.
ADDM can be used in two separate modes—a proactive and a reactive mode. The proactive mode is defined by the automatic diagnosis already mentioned. In addition to these automatic ADDM checks, other ADDM tasks can be created to look for specific problems or to go back in time and look at a period when a problem was observed or reported. This is the reactive mode usage of ADDM.
Viewing the ADDM reports
The ADDM is scheduled to run automatically at the end of each Workload Repository snapshot run, so each time a snapshot is gathered, ADDM runs automatically behind the scenes, gathering information on the last 30 minutes of activity. These reports can be viewed in OEM by querying the database directly via the dbms_advisor.get_task_report function against the dba_advisor_tasks view, or you can run the addmrpt.sql script from the rdbms_home/rdbms/admin folder: @addmrpt.sql.
For those not using OEM, you are able to view and generate reports (text and html) from the command line. Report generation is achieved by using awrrpt.sql, awrgrpt.sql (for Oracle RAC), or awrrpti.sql (instance level); these scripts are located in rdbms_home/rdbms/admin. Once the report is generated, it can then be viewed in either a web browser (if HTML was chosen) or your favorite text viewer.
ADDM uses a combination of sources for its findings and recommendations: wait events for individual sessions, the DB Time model to determine how much DB time is spent in database calls, with a focus on reducing the overall database time spent on operations; wait classes, which are a high-level grouping of the more fine-grained wait events; and operating system and database metrics. Using this information stored in the Workload Repository and the ASH lays the groundwork for ADDM to make the call to report a finding and a subsequent recommendation.
Wait events and active session history
Wait events are essentially the lifeblood of diagnosing a performance problem. Oracle provides a view called V$ACTIVE_SESSION_HISTORY, which maintains data from active sessions, capturing the wait events and their wait times, the SQL_ID, and session information for the waiting session. This allows you to go back and view this detailed information as it existed in the past. ADDM can use this for proactive and reactive analysis purposes.
Monitoring Oracle RAC metrics using AWR
The reports generated from the AWR in an Oracle RAC environment will contain a section titled “Oracle RAC Statistics,” where you can easily gather information on operations related to the global cache and global enqueues. You can take a quick look at the frequency of reads from the local cache, versus the remote cache, and how frequently you need to go to disk for a read; and you can view average get times for various operations. Generally, these average values should not be higher than 30 ms as the top range, but they are normally lower. For example, the average current block receive time may be as high as 30 ms, but the average CR block receive time should not be much higher than 15 ms. The sum of the global cache current block pin time (time to process a blocking asynchronous trap or BAST), plus send and receive time altogether account for the total average time to process a current block request, and this total should not exceed 30 ms.
NOTE
These numbers are ballpark figures and only given to provide an idea of minimum expected performance. Oftentimes, values less than 10 ms are expected and easily achieved.
Leave a Reply