Oracle’s SQL Tuning pack, part III

How does the STA work in 11gR2 with the query from “Oracle’s SQL Tuning Pack – part II″ ?

In Part II, the STA in 10g proposed a new profile for the query but that profile actually caused the query to run slower. Quetion is, in 11gR2 does the STA do better?

Below I ran the query load, identied the query, submitted to STA and STA spent 30 minutes burning CPU trying to tune the query and finally ended with an error that a better plan could not be found.

Here is the view on the Top Activity Page:
The first spike is when I ran the query. I pass the gray window over the spike and selected the first query and submitted it to the tuner. The rest of the load, the next 30 minutes is the STA running trying to find a better plan. After 30 minutes the STA times out saying not better plan found.
On 10gR2 the STA finishes in less than 2 minutes and finds a profile though the profile puts a higher load on the database for this query. On 11gR2, the STA runs for 30 and times out without finding any suggestions. Yet in DB Optimizer a new plan is found in minutes that runs faster than the original. So a better plan was out there, relatively easy to get yet STA in 11gR2 could’t find it in a half an hour. I guess that’s better though than suggesting a worse profile as it did in 10gR2.

Here is a video on Vimeo of the tuning with DB Optimizer which takes less than 60 seconds to find a faster plan:


  1. Trackbacks

  2. No trackbacks yet.

  2. June 24th, 2014 at 19:29 | #1

    Hi Kyle,

    This is cool ! Does DB Optimizer is able to rewrite queries, or only provides improvements using Hints ?
    What is according to you the perenity of a query tuning using hints ?


  3. khailey
    June 24th, 2014 at 19:53 | #2

    DB Optimizer does rewrite queries but it’s only through a guessing method, which is find for what it is, but unfortunate that DB Optimizer doesn’t profit from it’s own ability to create a sql join tree, a Visual SQL Tuning (VST) diagram, along with it’s ability to capture relevant tuning statistics and use all of that information to propose intelligent execution plans. Intelligent execution plans are easily deduced by the user with all the information DB Optimizer gives but that also means DB Optimizer could have easily taken this last step.

    As far as using hints hard coded, it has a history of leading to issues over the long run. Over the long run it would be wiser to figure out why the optimizer is using a sub-optimal plan. On the other hand we typically want plan stability so implementing a stored outline or profile, which is another way to apply hints, can add stability and potentially be more manageable. I haven’t had the chance to push much on adaptive plan management but it’s controls seem to be the way to go.

  4. June 25th, 2014 at 02:06 | #3

    Thank you. I agree when you say “it has a history of leading to issues over the long run”. Using profiles and outlines can solve some problems but requires more management for the DBA because we need to monitor more frequently those queries to assure that the profiles and outlines are still valid over the time.

    isn’t it more suitable to understand why Oracle is giving us a bad plan ? maybe this last assumption is a little utopian :)

  5. khailey
    June 25th, 2014 at 11:58 | #4

    Yes, definitely good to find and address why Oracle is producing a bad plan.
    Even if one tracks it down, it may still be reasonable to try and stabilize the plan. With adaptive plan management its nice to be able to stabilize a plan while at the same time being informed of possible better plans.

  6. June 25th, 2014 at 14:32 | #5

    I had never seen it from this angle, but you’re right, this is good to know that a better plan exists. This can also lead to “Compulsive tuning disorder” (Hermant) :)

    Thanks for the follow-up.

× 7 = seven