By automatically analyzing performance data for you, the ADDM relieves you of the responsibility of catching a problem at the right time to collect statistics. By default, the AWR collects new performance statistics in the form of an hourly snapshot and saves these snapshots for eight days before purging them. An AWR snapshot is a collection of database performance statistics at a single point in time, including statistics for resource-intensive SQL statements. Every time the AWR takes a new snapshot, the ADDM runs automatically, does its top-down system analysis, and reports its findings on the Database Control home page. The ADDM’s output consists of a description of each performance problem it identifies, along with the recommended action. The recommendations are ranked by the expected benefit of implementing each of them. You can view the regular ADDM reports from the OEM Database Control or view them from a SQL*Plus session with the help of an Oracle-supplied SQL script.
The ADDM runs automatically, but you can also manually invoke the tool to investigate problems that occur in between the scheduled snapshots. Oracle stores the ADDM analyses in the SYSAUX tablespace.
The Purpose of the ADDM
The basic rationale behind the ADDM is to reduce a key database metric called DB time, which is the total time (in microseconds) the database spends actually processing users’ requests.
DB time includes the total amount of time spent on actual database calls (at the user level) and it ignores time spent on background processes. DB time includes both the wait time and processing time (CPU time), but doesn’t include the idle time incurred by your processes. For example, if you spend an hour connected to the database and you’re idle for 58 of those minutes, the DB time is only 2 minutes.
If a problem contributes to inappropriate or excessive DB time, ADDM automatically flags it as an issue needing attention. If there is a problem in your system, but it doesn’t contribute significantly to the DB time, ADDM will simply ignore it. Thus, the ADDM is focused on the single mantra: reduce DB time. The ADDM aims to increase the throughput of your database, thereby serving more users with the same amount of resources.
Problems That the ADDM Diagnoses
The ADDM analyzes the AWR snapshots every hour by default, comes up with performance recommendations, and ranks them in terms of the expected benefit of implementing the various actions. These are some of the key problems that the ADDM diagnoses:
• Expensive SQL statements
• I/O performance issues
• Locking and concurrency issues
• Excessive parsing
• Resource bottlenecks, including memory and CPU bottlenecks
• Undersized memory allocation
• Connection management issues, such as excessive logon/logoff activity
When you are beset by a severe performance problem, look at the ADDM reports first, to get a good diagnosis of the problem. ADDM ignores the nonproblem areas and focuses on the truly significant causes affecting performance. The ADDM report contains the following:
• Expert problem diagnosis
• Emphasis on the root cause of the problem rather than on the symptoms
• A ranking of the effects of the problems
• Recommendations ranked according to their benefit
Unlike running some complex SQL scripts, the ADDM report has very little overhead associated with it, since its raw material is already saved in the AWR.
The ADDM uses sophisticated time-model statistics in Oracle Database 11g that are highly
effective in determining where the database spends the most time. These new time-model statistics enable Oracle to focus on only the most critical performance problems. If a problem exceeds the threshold for the key DB time metric, the ADDM tags it as a top performance problem; otherwise, it leaves it alone as a nonproblem area. Let’s look at these new time-model statistics in the following section.
ADDM Findings
The ADDM analysis is available in the form of a series of findings, of which there are three types: problem, symptom, and informational. Here’s an example of a typical ADDM finding:
FINDING 1: 45% impact (11223 seconds)
------------------------------------
SQL statements were not shared due to the usage of literals.
This resulted in additional hard parses which were consuming
significant database time.
This is a problem finding, because it’s accompanied by an impact estimate, which is an estimate
of the amount of additional DB time caused by the problem.
The findings are presented in decreasing order of importance (as defined by the impact
percentages), and the sum of the impact percentages for all the findings may exceed 100 percent,
as you can see in the following example:
FINDING 1: 34% impact (289378 seconds)
FINDING 2: 25% impact (214227 seconds)
FINDING 3: 23% impact (193521 seconds)
FINDING 4: 16% impact (134639 seconds)
FINDING 5: 6.1% impact (51563 seconds)
FINDING 6: 2.1% impact (17753 seconds) The sum of the impact percentages can exceed 100 percent of DB time because the performance issues of the various findings might overlap and, therefore, encompass the same portion of DB time.
ADDM Recommendations
ADDM usually proposes one or more recommendations for each of the problem findings in its analysis.
You may not need to follow all the recommendations to fix the problem. Each recommendation is accompanied by a quantified benefit that will result from the adoption of the ADDM recommendation, the benefit being measured in terms of the estimated reduction in DB time.
Here’s a typical ADDM recommendation, wherein you’re asked to first analyze your application logic:
RECOMMENDATION 1: Application Analysis, 45% benefit (11223 seconds) If you see multiple recommendations, which is common, it means that the benefit that accrues from adopting all the recommendations would be equal to the impact percentage noted for the relevant finding. Here’s an example:
FINDING 1: 34% impact (289378 seconds) The report starts with a finding that has a 34 percent impact on DB time. The finding is accompanied by the following five recommendations, each with a certain benefit. If you sum up the benefit (in percentages) that results from adopting all five recommendations, you’ll notice that it’s equal to the value of the finding’s impact (34 percent):
RECOMMENDATION 1: Segment Tuning, 13% benefit (112768 seconds)
RECOMMENDATION 2: Segment Tuning, 6.7% benefit (56805 seconds)
RECOMMENDATION 3: Segment Tuning, 6.1% benefit (51882 seconds)
RECOMMENDATION 4: Segment Tuning, 4.4% benefit (37330 seconds)
RECOMMENDATION 5: Segment Tuning, 3.6% benefit (30594 seconds)
ADDM recommendations may include the following:
• Hardware changes: The ADDM may recommend that you add more CPUs to your system or change the way you configure your I/O subsystem.
• Database and application changes: In some cases, the ADDM may recommend that you
change the setting of some of your initialization parameters, instead of rewriting your application code.
• Space configuration changes: The ADDM may sometimes make major recommendations,
such as using the new Automatic Storage Management feature, in order to fix certain performance problems.
• Use of performance advisors: In several cases, the ADDM will recommend that you use a
performance advisor, like the SQL Tuning Advisor or the Segment Advisor, to fix your performance problems.
Recommendations may also have action and rationale components, with actions showing you the various things you need to do to implement the recommendation, while rationales explain the reason for the recommendation. Here’s part of an ADDM report that shows an action and the rationale for the recommendation you saw earlier in this section:
ACTION: Investigate application logic for possible use of bind variables
instead of literals. Alternatively, you may set the parameter
"cursor_sharing" to "force".
RATIONALE: SQL statements with PLAN_HASH_VALUE 2094286255 were found to be
using literals. Look in V$SQL for examples of such SQL statements. Note that a recommendation may have one or more actions attached to it. Similarly, you may have one or more rationale items.
Comments