Search

Top 60 Oracle Blogs

Recent comments

Hinting

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.

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

Huh ?!

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 cause” 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.