Introduction to ADDMAs mentioned in previous chapters, Oracle Database 10g comes with a number of advisors built in. For the performance analyst, the Automatic Database Diagnostic Monitor (ADDM) is the most important of the advisors and is indeed the starting point in the investigation of any performance issue. Affectionately called "ADDuM" or "ADAM," this component of the Database Diagnostic Pack is a powerful aide to the performance analyst when it is understood and used carefully. Simply stated, it is a self-diagnostic mechanism built into the kernel that automatically examines and analyzes the AWR snapshots at the end of every snapshot with the objective of determining any performance-affecting issue. It is then able to recommend corrective action; these recommendations come with an expected benefit. ADDM does not stop with these recommendations. Just as a general practitioner examines a patient in an initial investigation and recommends further examination in particular areas by other specialists, ADDM can direct the performance analyst to other advisors such as the SQL Tuning Advisor or the SQL Access Advisor when it determines that a problem exists. By default, ADDM executes at the end of every scheduled snapshot, performing a scheduled "database performance health checkup" using the captured AWR snapshots. In effect, it is casting an expert eye on the AWR report in a scheduled manner, every time, all the time. This is equivalent to an unpaid expert DBA performing analysis of these snapshots 24/7/365! These recommendations and findings are stored in the database so that you can analyze and report on them later on, at a convenient time. The Goal of ADDMUltimately, the goal of ADDM is to reduce the DB Time component generated by any load on the database. As you saw in Chapter 10, "Adopting a New Approach to Tuning," the DB Time is the total time spent by the foreground sessions performing useful work. In other words, it is a combination of CPU spent parsing and executing SQL, PL/SQL, and Java as well other overheads such as process setup and management. When triggered, ADDM drills down into the performance statistics to identify the root cause of problems rather than just the symptoms, and reports the overall impact of the issue on the system as a whole. In making a recommendation, it reports the benefits that can be expected, again in terms of this DB Time. The use of this common currency allows the impact of several problems or recommendations to be compared effectively.
Using such a well-understood and easily identifiable set of components to quantify the impact also prevents judgments based on experience rather than hard figures. For example, a rule of thumb, based on experience, might have said that an IOPS (the number of I/O operations per second) should not exceed the rate of, say, 1,000 per second. Anything more than this rate was classified as a problem that should be fixed. That said, we are aware of many systems that can run significantly higher IOPS rates without noticeably affecting performance. Using the new Time and Wait model data in AWR, ADDM can now report quantitatively that such I/O operations are, say, taking 30% of time spent in the database during that period. This quantified value makes it much easier to understand the problem and help determine the effect of fixing the issue, rather than just making a judgmental statement such as "The database is performing too much I/O." Better still, it helps the performance analyst concentrate on what is important to tune so that the fix has the most effect. Problem Areas Handled by ADDMADDM handles the most frequently observed performance problems and drills down to the root cause rather than taking the easier approach of just reporting symptoms. This reporting includes but is not limited to problems seen in the following areas:
ADDM reports these problems as "findings," but does not stop with the diagnosis; it recommends possible solutions, based on the detected problem areas. When appropriate, ADDM recommends multiple solutions for the performance analyst to choose from. These are in the form of recommendations, and include the following:
Using ADDM with ASHWhen ADDM is combined with ASH data, it is possible to quickly determine the root cause as well as to drill down to the depths of session details to document what exactly transpired. It is thus possible to even determine what occurred a while ago without having to resort to setting up a replay of the workload in a test environment. Even if ASH data is flushed from the buffers, keep in mind that every 10th sample is retained, and this provides at least a coarse drill down. In comparison, STATSPACK and most other tools do not store session-level data that could assist in such drill downs. As mentioned previously, the extended SQL trace cannot be used in this case because trace needs to be switched on prior to the occurrence of the problem. Nonproblematic AreasADDM highlights nonproblematic areas in addition to the problem areas. This is based on wait classes that have been determined as not affecting the result significantly, but are nevertheless listed. A performance analyst can then quickly see that these wait classes were eliminated and hence not spend time and effort working on something that will not produce significant improvement. This is akin to a general practitioner assuring a patient that an otherwise worrying symptom is not the cause of a deeper issue, helping the patient concentrate on battling what is more important to his or her health. Sometimes, large values in STATSPACK reports that actually do not pose a problem seem alarming. Many a performance analyst has wasted time and resources trying to chase and fix such issues. This is almost entirely avoided in Oracle Database 10g as you'll see later.
Helpful ADDM ViewsIt pays to be aware of a number of views that ADDM uses. You can use these views in many innovative ways. For example, you can use them to help preserve interesting scenarios and findings before the data is purged by AWR. A small number of these views is listed in Table 12.1. More details can be found in the Oracle Database 10g Reference Manual as well as in the Oracle Database 10g Performance Guide.
Before you move to the next section, let's look at a brief example of how one of the views can be used. A performance analyst has many other tasks, so rather than expecting him or her to review all the ADDM reports that are automatically produced by the system throughout the day and night, the DBA_ADVISOR_FINDINGS view can be used to summarize this information. The query and output are shown in Listing 12.1. Listing 12.1. Determining Whether ADDM Found Problems in the Past 24 HoursSQL> select type, count(*) from dba_advisor_findings 2 where task_id in 3 (select task_id from dba_advisor_log where execution_start > sysdate - 1) 4 group by type; TYPE COUNT(*) ----------- ---------- INFORMATION 40 PROBLEM 31 SYMPTOM 18 From the result, you can see that there were at least 31 problems detected by ADDM during the past 24 hours. This will warrant more research and analysis of individual ADDM tasks. You can use the query shown in Listing 12.2 to determine which ADDM task IDs to query on. Listing 12.2. Determining the ADDM Task IDs with Problems in the Past 24 HoursSQL> select distinct f.task_id, f.task_name, l.execution_start 2 from dba_advisor_findings f, dba_advisor_log l 3 where f.type in ('PROBLEM', 'SYMPTOM') 4 and f.task_id = l.task_id 5 and l.execution_start > sysdate 1; TASK_ID TASK_NAME EXECUTION ---------- ------------------------------ --------- 1490 TASK_1490 05-FEB-05 1493 ADDM:845721274_1_1522 05-FEB-05 1494 TASK_1494 05-FEB-05 1495 ADDM:845721274_1_1523 05-FEB-05 You can then use this output to produce a detailed ADDM report, given the task IDs. You also notice that tasks 1490 and 1494 were probably manually performed, while ADDM automatically produced tasks 1493 and 1495. |