Remedy for bad hints: SQL Profiles

This is the second week in a row that I am in Berlin, this time teaching a 10g Performance Tuning course. Fortunately, the weather this week is a lot better 🙂

Well, one really great improvement of the 10g version was the introduction of SQL Profiles. They can give the optimizer additional information, but they do not determine an execution plan like hints or stored outlines do. Using the SQL Tuning Advisor for high load statements may (not for sure) lead to the recommendation to create such a SQL Profile that may very well speed up that query dramatically. A special case is, if you have statements hard coded in applications that use hints which are not (longer) good. You can hint the optimizer not to use those bad hints with SQL Profiles 🙂 Here is an example.

I have created a larger sales table with a very skewed channel_id column:

I have also informed the optimizer of that skew by creating histograms. Also, there is an index on the channel_id column, that is used by the following statement:

But not by following statement – which is perfectly ok, the index access would be slower as the FTS here.

Now I force the optimizer to use that index with a hint:

In fact, this is bad, as we can see by runtime comparison:

But let’s assume that hint is already hard coded in an application running against my Database.
Then I can use the SQL Tuning Advisor:

The following is the recommendation of the SQL Tuning Advisor. It recommends a SQL Profile:

I decide to accept that with the above accept_sql_profile call.
Then comes again the hard coded statement with the index hint:

As you see, the SQL Profile makes it possible for the optimizer to ignore the hint!


  1. #1 von Don Seiler am Juli 28, 2009 - 17:47

    Thanks for the great article. Is there a way to use SQL Profiles without the extra cost of the tuning advisor?

    One note, „wheather“ should be „weather“. 🙂

  2. #2 von Uwe Hesse am Juli 28, 2009 - 20:50

    Thank you Don, for correcting my English – I can’t hide that I am not a native speaker 🙂

    Unfortunately, SQL Profiles can only be used as part of the Tuning Pack. At least as far as I can see (not being a lawyer) looking at the licensing guide:

    Kind regards

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..