Problem solve Get help with specific problems with your technologies, process and projects.

Using Oracle ADDM reports to resolve SQL performance problems: Step-by step guide

Using Oracle ADDM is an effective way to resolve SQL performance problems, according to Brian Peasland. ADDM is his go-to tool for finding problem SQL statements.

Oracle's Automatic Database Diagnostic Monitor (ADDM) examines database bottlenecks and recommendations for fixing...

them. 

Out of the box, the Automatic Workload Repository (AWR) takes a snapshot of database metrics once per hour. The difference between two snapshots tells a lot about what a database is doing. 

After ADDM takes a snapshot, it looks at performance over the past hour and automatically generates a report of its findings. 

I interact with ADDM reports via Enterprise Manager 12c by finding my database and then selecting Advisor Central from the Performance menu. In the Results section, I can see all the ADDM runs (Figure 1).

Recently, I got a call about a batch job that did not complete in a timely fashion. I knew the job was running somewhere between 5:00 and 6:00 in the morning. I found the report that correlated with a 5:00 a.m. start time and selected it.

An example ADDM report
Figure 1: An example ADDM report

Unfortunately, I rarely find the next screen to be of much help.

Instead, I click on the View Report button. I can see from the top of the report (Figure 2) that these findings are indeed from the time period I need. ADDM then gives me a quick summary of its findings (Figure 3).

Finding the correct time period
Figure 2: Finding the correct time period
Summary of ADDM findings
Figure 3: Summary of ADDM findings

By looking at the findings, I see that there are five recommendations about problematic SQL statements. The report indicates that the top SQL statements are responsible for 43.4% of total database activity (Figure 4). Of that 43.4%, the first SQL statement accounts for 39.75%. Clearly, I know the singular SQL statement that is contributing to the database performance bottleneck.

Identifying problematic SQL statements
Figure 4: Identifying problematic SQL statements

The Action provided to me by the ADDM is to use the SQL Tuning Advisor, but I can use other methods, too. It is important to note that ADDM recommendations and rationale need to be weighed carefully. Figure 5 shows the rationale from one ADDM finding.

Rationale from an ADDM finding
Figure 5: Rationale from an ADDM finding

ADDM thinks this SQL statement is a problem because it had a total runtime of 438 seconds. However, each execution was less than two-tenths of a second. This query is performing fine and the application does need to execute it that many times per hour. The lesson is that not all ADDM findings are action items for the DBA.

If you're using SQL Developer, you can access ADDM findings without leaving the application. Simply go to View > DBA and add a database connection. In that connection, go to Performance > ADDM to see the runs. Click on a run and you can promptly see the Findings and ADDM Report.

I also like to use EM12c's Real Time ADDM when someone calls to complain that "the database is slow." I don't want to wait for the ADDM report at the top of the hour. Instead, I select Real Time ADDM under the Performance menu of my database. I then click on the Start button. Real Time ADDM goes to work and when finished, presents me with its findings (Figure 6).

The findings after running Real Time ADDM
Figure 6: The findings after running Real Time ADDM

I can then click on the Findings tab to obtain more information. I'm now off and running with ADDM again.

There is a danger for some DBAs to comb through ADDM reports trying to spot a problem, even when they don't know a problem exists; in other words, they exhibit Compulsive Tuning Disorder. I rarely look at ADDM reports automatically generated by Oracle. Instead, I look at them only when I know I have a problem at a specific point in time, or I use Real Time ADDM. Rather than deciphering a long AWR report, ADDM findings can save me a lot of time.

Next Steps

Get expert advice on the right tools to use for SQL performance tuning

Find out how one Oracle customer developed a way to pinpoint technical problems and fix them

This was last published in June 2015

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What problems do you have that you would want to try using Oracle ADDM to solve?
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close