Oracle SQL Tuning and CBO Internal Oracle Hints

Call (800) 766-1884 for Oracle support & training

Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Internals of Oracle Hints

One of the long-running questions about cost-based optimization (CBO) is the meaning of the word hint. As far as CBO is concerned, is a hint a hint, or is it a directive? Personally, I am convinced that the answer is that it is a directive; this chapter argues my case and examines why there is so much room for doubt. This chapter is based on information previously published on the Web at www.jlcomp.demon.co.uk.

My Hint Is Not Working

Hints exist to allow programmers to tell Oracle that they already know the best execution path for a query and give Oracle details of that path so that Oracle will take it. However, there remains a lot of discussion about the word hint, and whether it has its normal English meaning of “a polite but perhaps over-subtle suggestion which may not be noticed and could be ignored” or does it actually mean “a direct command and you had better have a good reason for not doing what you are told”?

The most common argument for regarding hints as a “suggestion” that Oracle may ignore comes from the often-heard complaint: “I put this hint in and Oracle still doesn’t do …”. Does this mean that the hint has been ignored? Not necessarily.

There are four main reasons why Oracle appears to treat hints with disdain.

n There are (or have been) bugs in various pieces of Oracle code.

n Illegal or incorrect hints are not hints; they are comments.

n Oracle ignores hints that cannot be applied.

n Most importantly, the optimizer is sufficiently complex that it is possible for Oracle to find ways of making hints inapplicable.

Let’s examine an example of each issue. Consider the hint:

/*+index(t1,t1_pk)*/

This appears to be a perfectly valid hint that appears (based on names) to be instructing Oracle to use the index representing the primary key when accessing table T1. Due to a bug in various versions of PL/SQL, this hint will not be effective if it appears as part of a SQL statement embedded in a PL/SQL block unless you make sure you have left a space after the plus sign.

As an example of an incorrect hint, take a look at the most commonly occurring error in the book:

select

/*+ index(big1, big1_pk) */

{list of columns}

from

big1 t

where

{list of conditions}

;

Again, we appear to be telling Oracle to use an index representing the primary key to access a table big1. The problem here is that we have given the table an alias, and hints have to use the table alias, not the table name. In this case, the hint should be:

/*+ index(t, big1_pk) */

As an example of a hint that appears valid but cannot be applied, we can highlight another common error. The example is a very simple one that would be very easy to spot, but the nature of the error does cause some surprises in more complex situations.

select

/*+ index(t, big1_uk) */

count(*)

from

big1 t

where

{list of conditions}

{but none on the Unique Key}

;

Note in this example that we have changed to a hint that appears to be asking Oracle to use an index big1_uk that represents a unique constraint to access the table big1.

The problem here is that unique constraints do not imply not null constraints (unlike primary key constraints, which imply unique and not null). In this example, and assuming that we had not introduced a not null constraint on at least one of the columns in the unique key, Oracle cannot use the index because there may be rows in the table that do not appear in the index. That is, using the index could produce the wrong answer.

Finally, we come to the situations that leave most of us thinking that Oracle is simply ignoring our hints because it wants to. Before looking at, and explaining, an example of Oracle “manufacturing” a reason for rejecting a hint, I would like to offer three logical arguments why hints must really be directives.

n Oracle Corporation uses hints internally to make code work (we hope) in the most efficient manner. Examine recursive SQL or the SQL generated for parallel query slaves, for example. If hints were simply there to be ignored, entire swathes of the Oracle product base would become unstable.

n Oracle Corporation has introduced plan stability, also known as stored outlines in Oracle 8.1 to guarantee (so the documents say) that execution paths will not change, even across product upgrades. How is plan stability implemented? As a set of hints stored in the database. So if hints are not directives, plan stability is not stable.

n If Oracle has the option to ignore hints, how could it decide to ignore them? Presumably, it would have to run through the normal optimization code to find the cost of the path and then decide to take it or not. But one reason for hinting the SQL is to reduce the parse (or, more specifically, optimization) time, so Oracle must be skipping at least some optimization because of the hints. Moreover, if Oracle calculates the cost of your hinted path and then decides whether or not to take it, what criteria does it use in its decision? Does it run the calculations without the hint and compare costs? Presumably not, because that would be just the standard cost-based optimization mechanism.

Clearly, hints make something (not) happen, and there must always be a good reason why Oracle has decided that a hint cannot be applied.

It is time for an example. Exhibit 1 is a simple script to build a pair of tables. This script was run on an Oracle 8.1.7 system using a 4K blocksize.

-- *************************************************

-- This script is free for non-commercial purposes

-- with no warranties. Use at your own risk.

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

-- *************************************************

create table ignore_1

nologging

select

rownum id, rownum val,

rpad('x',250) padding

from all_objects

where rownum <= 3000;

create table ignore_2

nologging

select

rownum id, rownum val,

rpad('x',250) padding

from all_objects

where rownum <= 500;

alter table ignore_2

add constraint ig2_pk primary key (id);

analyze table ignore_1

compute statistics;

analyze table ignore_2

compute statistics;

Exhibit 1. Creating the Sample Data Set

Now we run a simple query against these two tables, joining them on the id column. Our query will pick a small number of rows from the larger table and join them to the matching rows in the smaller table. To encourage Oracle to use the conveniently created primary key index, we will include a hint to tell Oracle to use a nested loop join between the tables (see Exhibit 2).

-- *************************************************

-- This script is free for non-commercial purposes

-- with no warranties. Use at your own risk.

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

-- *************************************************

set autotrace traceonly explain

select /*+ use_nl(i2) */

i1.val val1,

i2.val val2

from

ignore_1 i1,

ignore_2 i2

where

i2.id = i1.id

and i1.val <= 10

;

SELECT STATEMENT Optimizer=ALL_ROWS

HASH JOIN (Cost=44 Card=11 Bytes=132)

TABLE ACCESS (FULL) OF 'IGNORE_1' ( (Cost=36 Card=11)

TABLE ACCESS (FULL) OF 'IGNORE_2' ( (Cost=7 Card=500)

Exhibit 2. A Query that Will Not Take the Hint

Our query, with the hint, visits the tables in the order we expect but appears to ignore our hint to use_nl(i2). The definition of this hint means:

When examining paths entering table (aliased by) i2, you should consider only a nested loop join to get into table i2.

At this point, some developers will change the hint to read:

/*+ use_nl(i1 i2) */

and find that the query suddenly changes to the nested loop that they expect. This is sometimes taken as an indication that you need to specify both ends of the nested loop join in this hint. This is not true; the altered path would actually be a side effect of, not a direct result of, this change. In its modified form, the hint now reads:

When examining paths entering the table (aliased by) i2, you should consider only a nested loop join to get into table i2; and when examining paths entering the table (aliased by) i1, you should consider only a nested loop join to get into table i1.

Other developers may come up with the “more correct” solution of changing the hint to read:

/*+ ordered use_nl(i2) */

This tells Oracle to visit the tables i1 and i2 in the correct order (as listed in the from clause) and to use a nested loop to get into i2.

Why is there a problem, and why is this the solution?

The answer comes from examining the 10053 trace file. If we have no hints, the trace file will show the stages (edited for extreme compaction) shown in Exhibit 3. The interesting quirk appears when we use only the use_nl(i2) hint. We have not told Oracle to avoid examining join orders from i2 to i1, so the trace looks like Exhibit 4.

Try the order i1 to i2 using NL.

Try the order i1 to i2 using sort/merge

Try the order i1 to i2 using hash

Hash is the cheapest

Try the order i2 to i1 using NL.

Try the order i2 to i1 using sort/merge

Try the order i2 to i1 using hash

Hash is the cheapest

Hash i1 -> i2 is the cheapest overall

Exhibit 3. A Shortened 10053 Trace

Thus, given the restrictions imposed by the hints, the best available execution path from Oracle’s perspective is to hash the two tables going in the order i2 to i1 — but swapping the join inputs. That is, Oracle has legally found a way of bypassing our demand that it should only consider a nested loop when entering table i2. As we can see from Exhibit 4, when deliberately considering methods of getting into i2, the only option examined was the nested loop. The unexpected hash join appears only as an “accidental” by-product of considering paths into i1. This, of course, is why the addition of the ordered hint is the correct solution to the problem; it stops Oracle from considering any paths into i1 and therefore blocks the emergence of the swapped hash join.

Try the order i1 to i2 using NL.

Use_nl is the cheapest

Try the order i2 to i1 using NL.

Try the order i2 to i1 using sort/merge

Try the order i2 to i1 using hash,

Try the hash again with "(sides swapped)"

Swapped Hash is the cheapest

Hash i2 -> i1 is the cheapest overall

Exhibit 4. The Trace after the Use_Nl() Hint

Just for the sake of completeness, Exhibit 5 shows an extract of the trace information you would get from the two hint modifications given above.

/*+ ordered use_nl(i2) */

Try the order i1 to i2 using NL.

Nested loop is the cheapest

NL i1 -> i2 is the cheapest overall

/*+ use_nl(i1,i2) */

Try the order i1 to i2 using NL.

Nested loop is the cheapest

Try the order i2 to i1 using NL.

Nested loop is the cheapest

NL i1 -> i2 is the cheapest overall

Exhibit 5. Alternative Traces

Conclusion

Hints are not mildly worded suggestions; they are directives to the optimizer to reduce the number of different options it is allowed to examine.

Unfortunately, it is easy to be convinced for several reasons that your hints are being ignored; and this may simply mean that there is something trivially wrong with your hinting.

There are cases, however, where the increasing sophistication of the optimizer allows it to use alternative mechanisms to reach a path that you thought you had blocked by the use of hints.

If you are going to use hints, be very thorough with the hints you apply. If there is a valid path that you want Oracle to take, the correct and complete set of hints will ensure that Oracle does take it.