10G Automatic Database Diagnostic Monitor (ADDM)
Once I cover all of the advisors, we’ll review what we have learned and then apply our newfound knowledge by running through a couple of performance tuning scenarios.
Oracle's ADDM utility uses information stored in the Automatic Workload Repository (AWR) as input. For those readers that are familiar with Statspack, you can loosely equate AWR's Snapshot style of information gathering to its Statspack predecessor. The Statspack utility is a set of SQL, PL/SQL and SQL*PLUS scripts that collect, store and report on Oracle database performance statistics. The utility stores the statistical information in permanent tables, which allows the data to be used for after-the-fact performance analysis as well as performance trending. The administrator collects performance information by running data captures called Snapshots. Snapshots record key statistics, high resource consuming SQL statements and wait events. Administrators run the Snap in SQLPLUS, which can be scheduled to run on a regular basis by using Oracle Enterprise Manager or a job scheduler. To report on this information, users run an Oracle supplied script that prompts them for beginning and ending Snapshot IDs. Oracle then displays performance information, which can be spooled to an output file in SQLPLUS. Since the information is stored in permanent tables, the information is available to be viewed at any time.
ADDM on Automatic
ADDM follows the same Snapshot premise. By default, AWR Snapshots occur every 60 minutes. After the AWR Snapshot is executed, the Automatic Database Diagnostic Monitor is triggered to analyze the information contained in the AWR for the period pertaining to the last two Snapshots. If ADDM requires additional information to make a decision, it will activate other advisories to gather more information. ADDM's output includes a plethora of reports, charts, graphs, heartbeats and related visual aids. In general, ADDM provides users with a top-down analysis of performance bottlenecks and their associated resolutions. This information can be sent by ADDM to the various database targets as alerts and recommendations. Oracle recommends that DBAs review ADDM output first when performing performance problem analysis.
Manually Executing ADDM
Administrators are also able to execute ADDM manually. Administrators specify
start and end times on the ADDM
Task Page to ask ADDM to generate performance information for the selected
time period. You'll notice that the ADDM Task Page also displays a chart showing
information on the number of sessions, resource utilization and waits.
The administrator clicks OK to tell ADDM to begin the analysis process. Once the analysis is complete, 10 Enterprise Manager displays the findings at the bottom of the ADDM Task Page. The listing of findings is categorized (on the far right side of tabular display) and each recommendation is actually a link that allows the user to drill down to more specific information pertaining to that finding.
ADDM Output
I clicked on the recommendation
link titled "The buffer cache was undersized causing significant additional
I/O". 10G Enterprise Manager displayed a Performance
Finding Details Page that provides more information on that particular recommendation.
You'll notice that there is a navigation button that says "Implement".
Be very careful when you hit this button. Depending on the type of recommendation,
10G Enterprise Manager may not prompt you again. Look at the bottom of the page.
ADDM displays a Findings Path that provides you with some of the information
it used to make its decision.
I continued my investigation by clicking on the recommendation link titled "SQL statements consuming significant database time were found". Considering this is a large data warehouse database, this shouldn't surprise anyone. 10G Enterprise Manager displayed another Performance Finding Details Page that provides more specific information on the selected recommendation. The page displays the SQL text of the statement and provides a navigation button that allows the user to activate the SQL Advisor Utility. We'll discuss the SQL Advisor in an upcoming blog.
Clicking on the SQL text takes the user to a SQL Details Page which shows the entire text of the SQL statement being reviewed. In addition, 10G Enterprise Manager also displays navigation tabs that allows the users to view the statement's execution plan, current statistics, execution history and tuning history. Once again, 10G Enterprise Manager provides a navigation button that allows the user to activate the SQL Advisor Utility.
You should have quickly come to the conclusion that ADDM provides different functionality, information and navigation paths based on the recommendation being reviewed. That's exactly as it should be. Pretty neat!
If you would like to see a text version of the recommendations, navigate back to the ADDM Task Page that displays the performance findings. Don't navigate too far back or you'll go back to the first page where you specified the start and end times to analyze. Navigate to the page that contains the recommendation summary and click on the "View Report" link to see a text version of the recommendations and the " View Snapshots" link to see the information ADDM used as input.
If you navigate back to the Advisor Central Page, you'll find a listing of jobs including the ADDM task I just executed. As you can see based on the information displayed on the right side of the screen, I have 30 days to view the report before it is automatically removed by ADDM.
Saving Performance Statistics
It is important to note that the performance information used as input by ADDM
is stored in the Automatic Workload Repository. AWR snapshots are purged automatically
by MMON every night. MMON, by default, tries to keep one week's worth of AWR
snapshots available. If AWR detects that the SYSAUX tablespace is in danger
of running out of space, it will free space in SYSAUX by automatically deleting
the oldest set of snapshots. If this occurs, AWR will initiate a server-generated
alert to notify administrators of the out-of-space error condition.
Administrators are able to manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable. So if you want to retain your performance information longer than the default setting allows, you'll need to ensure your SYSAUX tablespace has sufficient space and run the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure. Please turn to my blog titled "Advisory Framework - 10G Common Manageability Infrastructure" for more information on 10G Enterprise Manager's Automatic Workload Repository and the SYSAUX tablespace.
I stated at the beginning of this blog that we could loosely equate ADDM to Statspack. The snapshot mechanism is pretty close but the similarities soon end there. Statspack's reporting script dumps the information into a spooled SQPLUS output file. ADDM provides intelligent recommendations and allows you to activate additional advisors when necessary. Oracle states that there are hundreds of years' worth of experience in the recommendations provided by ADDM. Considering this is the first iteration of this tool, I can't wait to see what future releases offer.
Next up
We'll turn our attention to the 10G Enterprise Manger's SQL Access Advisor.
