Manage Learn to apply best practices and optimize your operations.

Five SQL performance tuning tips: Using the right third-party tools

With the right tools, an Oracle DBA was able to resolve a SQL performance tuning problem in minutes rather than days. Here is how he uses those tools.

Like many database administrators, I get hit with SQL performance tuning problems. I can remember days of old when...

I had a library full of scripts and would hope that the library held the script I needed to diagnose the problem. Thankfully, those days are over.

Today's Oracle DBA has many SQL tools at his disposal. My favorite SQL tools are those that provide me with a visual representation that lets me get to the root cause of a performance problem quickly.  That's why I use SolarWinds' database performance analyzer (DPA) and Oracle's diagnostics and tuning packs.

Normally, I don't use many third-party products for Oracle administration. They cost extra money and I usually get what I need from Oracle. However, I'm happy that my company purchased SolarWinds' DPA long before I arrived on the scene. With Oracle's diagnostics and tuning packs, I could create SQL tuning sets from automatic workload repository snapshots, but that requires me to do a lot of work. I could let the automatic database diagnostic monitor (ADDM) look at the performance between snapshots and make recommendations, but then I have to actively seek out the ADDM findings.

Instead, I have DPA monitor my production databases regularly. On Monday mornings, DPA sends me a report of the top 15 SQL statements for the past week (Figure 1).

DPA report showing top SQL statements
Figure 1. DPA report showing top 15 SQL statements

The large brown slice at the bottom of the chart in Figure 1 shows my biggest problem this week. The height of the brown bars shows how much time they are taking up as opposed to every other process I am running. Mouse over any part of the bar chart to make the the SQL text and other information about that slice pop up. With this tool I can immediately see that I have a problematic SQL statement that needs to be addressed. I know that this one SQL statement ran for more than nine hours. I love it when data visualization helps you get meaningful information in a flash.

With DPA, I was able to gather additional nuggets of information about this SQL statement. Armed with the SQL identifier from the initial DPA report, I asked DPA to give me the top wait events for this single SQL statement (Figure 2). Looking at the graph provided by DPA, I can see that this problem started on April 3 and that almost all of this statement's time is spent on the CPU.

Report showing top wait events
Figure 2. Report showing top wait events

My experience has taught me that a SQL statement using too much CPU has one of two root causes: either a Cartesian product is performed or statistics on a table is causing the optimizer to arrive at a suboptimal execution plan (i.e., a full table scan is being performed when an index lookup would be preferred).

This brings me to the next tool, the Oracle diagnostics pack. If your company does not license the diagnostics and tuning packs, you may want to ask them to do so. Coupled with Enterprise Manager 12c, these SQL performance tools have saved me a great deal of time over the years. Taking into account the labor savings, they pay for themselves inside the first year or so.

I started running the problematic SQL statement in SQL Developer. Then I started Enterprise Manager. I navigated to my database and went to "search sessions" to find my session. I clicked on my session and then clicked on the SQL monitoring tab. When I clicked on the SQL ID link, I was taken to the monitored SQL executions page, which shows me the execution plan.

Next I need to check to make sure that the plan is not calling for a Cartesian product. It isn't, so I eliminate that from my list of possibilities. The column headings above the execution plan (Figure 3) show me that the tenth column provides the CPU Activity %, so I know that this statement is suffering from too much CPU usage.

Column headings in the execution plan
Figure 3. Column headings in the execution plan

This query has a lengthy plan, so I scroll down and see that one line in the plan is consuming 66% of the total CPU utilization (Figure 4).

Report showing CPU utilization
Figure 4. Report showing 66% CPU utilization

Another line shows a table consuming 32% of the CPU for this query and it was the same table. Yes, this statement references the same table twice, but that's another matter. In all, 98% of the CPU utilization was from this one table. I updated the table's statistics and the nine-hour query completed in less than four minutes. SolarWinds' DPA and Oracle's diagnostics and tuning packs cost money, but I think they are worth the price. If I did not have these SQL performance tools at my disposal, I could have easily spent a week, instead of minutes, on this Oracle SQL performance issue.

Next Steps

Learn about a SolarWinds' monitoring tool that is helping DBAs get their nights and weekends back

Find out about the Oracle product that simultaneously queries SQL, Hadoop and NoSQL

This was last published in May 2015

Dig Deeper on Oracle DBA tools

Join the conversation

6 comments

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 tools do you use for SQL performance tuning?
Cancel
Enterprise Manager 12c R4 - there is no equal
Cancel
I believe Jmeter is one possibility, better sku's of SQL Server have the ability to profile SQL server's usage. (however, I've read that leaving it on full time is not a wise issue because of the inherent additional load it adds to the system.)
Cancel
To @user12340, I have always been a strong believer in using EM12c for performance tuning. But it works best if you are also licensing the Diagnostics and Tuning Packs.

There is no equal? I'm not so sure about that any more. I've started using Lighty for Oracle by orachrome and its pretty fantastic! If you're familiar with EM12c's performance tuning tools, then Lighty is a walk in the park. Lighty only does performance tuning (EM12c does so much more) and it does it very well. These days, I'm using both Lighty and EM12c in my arsenal of tuning tools.
Cancel
To @Veretax, I'm not a SQL Server tuning expert by any stretch. I have used JMeter to produce a load on a database system, but have always used other tools to monitor the performance of that load. That being said, I'll check it out again to see what it can do for me.
Cancel
The EM tools are great for immediate visual reporting and the 3rd party products are also excellent.

However, since the '90s I've been using the v$session_wait to find contention objects (wait_time column) and waiting processes which lets you then source both the statement and the hot spot.

Then get an execution plan if necessary or often you can determine what's going on just looking at the statement for inefficient joins.

It's old hat but works pretty well.  Obviously the enviroment you work in determines what becomes necessary and that won't be the same for everyone.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close