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 by Don Seiler on July 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 by Uwe Hesse on July 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:
    http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#CIHIGHAH

    Kind regards
    Uwe

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: