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).
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.
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.
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).
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.
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