Oracle Scratchpad

October 9, 2013

Hinting

Filed under: 12c,Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 5:33 pm BST Oct 9,2013

I’ve spent so many years trying to explain that a “hint” to the Oracle optimizer is an order – if you know how to do it properly – that I finally decided to list the manual references that have made this point over the last 15 or so years. Here’s the list, which ends with a surprising change of flavour. (Emphasis in the body of the text is mine).

From the 8.1.7 manual

Using Hints

As an application designer, you may know information about your data that the optimizer does not know. For example, you may know that a certain index is more selective for certain queries. Based on this information, you may be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

From the 9.2 manual

Understanding Optimizer Hints

Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

From the 10.2 manual

16.1 Understanding Optimizer Hints

Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to instruct the optimizer to use the optimal execution plan.

From the 11.2 manual

19.1 Overview of Optimizer Hints

A hint is an instruction to the optimizer. When writing SQL, you may know information about the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer, sometimes causing the optimizer to select a plan that it sees as higher cost.

In a test or development environments, hints are useful for testing the performance of a specific access path. For example, you may know that a certain index is more selective for certain queries. In this case, you may use hints to instruct the optimizer to use a better execution plan.

From the 12.1 manual

Strangely, when we get to the 12c manuals, an element of doubt seems to creep into the vocabulary used by the technical authors.

About Optimizer Hints

Use hints to influence the optimizer mode, query transformation, access path, join order, and join methods. For example, Figure 14-2 shows how you can use a hint to tell the optimizer to use a specific index for a specific statement. Oracle Database SQL Language Reference lists the most common hints by functional category.

The advantage of hints is that they enable you to make decisions normally made by the optimizer. In a test environment, hints are useful for testing the performance of a specific access path. For example, you may know that an index is more selective for certain queries, as in Figure 14-2. In this case, the hint may cause the optimizer to generate a better plan.asis

And my opinion: 

Right up to 12c we “instruct” or “force” the optimizer to use a better plan and then in 12c, where the optimizer depends on hints for profiles, baselines and directives, we only “influence” it in ways that “may” it to use a better plan !

Good job I never trust the manuals 100% – but I’d still like to see an example where a proper set of hints fails to influence the optimizer in exactly the way it should. But maybe the new text is a wedge into allowing adaptive cursor sharing (or some such) to use the hinted path the first time and then do something different the second time around.

8 Comments »

  1. Or maybe it’s protection for the flood of support calls bound to happen now that there are so many possibilities of code manipulation, including things like smart scan offloading. We’ve long had to account for things like “warming up the buffers,” so things happening differently the first time isn’t new. It’s just harder to explain in a short doc.

    Comment by jgarry — October 9, 2013 @ 5:47 pm BST Oct 9,2013 | Reply

  2. Let us roll back to Oracle7 (capitalization is mine):
    “Hints are SUGGESTIONS that you give the optimizer for optimizing a SQL statement.”

    Comment by al0 — October 9, 2013 @ 7:30 pm BST Oct 9,2013 | Reply

    • I couldn’t find a reference for Oracle 7 on line – but the same comment appears in the 8.0.4 manuals: http://docs.oracle.com/cd/A58617_01/server.804/a58246/optimiz.htm
      I wonder if this reflects the stability of the CBO in those early versions.

      Comment by Jonathan Lewis — October 9, 2013 @ 7:41 pm BST Oct 9,2013 | Reply

      • Oracle7 (7.3.3) documentation is still on the Oracle site – here http://docs.oracle.com/cd/A57673_01/DOC/dcommon/oin/index.htm.
        The quote in my previous post is from the later, “Oracle7 Server/Server tuning/Chapter 7 Optimization Modes and Hints/Introduction to hints”.

        But it is a real fun to compare it with a description of the INDEX_DESC hint in the same manual, which suggests to use

         
        SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature
        FROM tank_readings
        WHERE time <= TO_DATE(:t) AND ROWNUM = 1
        ORDER BY time DESC;
        

        instead of

        SELECT temperature
        FROM tank_readings
        WHERE time = (SELECT MAX(time)
              FROM tank_readings
               WHERE time <= TO_DATE(:t) );
        

        Just assuming that INDEX_DESC hint will be always granted by the optimizer.

        Comment by al0 — October 10, 2013 @ 9:12 pm BST Oct 10,2013 | Reply

        • That’s the primary example which I always quote when I tell people (a) to be very careful about hinting and (b) to be very careful about dropping or renaming indexes in case someone else has NOT been very careful about hinting.

          In principle the query in the hinted form was safe (but not one I’d want to use, nevertheless) – it wouldn’t be the hinting that would cause problems, it would be someone mucking about with the index in some way.

          Comment by Jonathan Lewis — October 11, 2013 @ 6:57 pm BST Oct 11,2013

  3. The 12c entry reveals the truth: hints are a form of prayer. I’ve always admired prayer based database administration techniques.

    Comment by mgogala — October 10, 2013 @ 8:49 pm BST Oct 10,2013 | Reply

    • I disagree – hints work, it’s just that few people know how they work.

      If you get to the point where you ask yourself: “Has Oracle ignored my hint, have I done it wrong or have I found a bug”- the answer is almost always: “you’ve done it wrong”.

      Comment by Jonathan Lewis — October 11, 2013 @ 6:51 pm BST Oct 11,2013 | Reply

  4. Same here: I’m telling attendees of Oracle University courses since years that an Optimizer hint is actually a directive (the term ‘hint’ is just odd here) and that with correct syntax, it must be followed if that is at all possible. Now the 12c documentation seems to be a step backward when trying to convince them about it :(

    Comment by Uwe Hesse — October 11, 2013 @ 7:38 pm BST Oct 11,2013 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,113 other followers