A Beginner’s Guide to Optimizer Hints

It is often easy to forget this, but in many ways it is after we hit the execute button that the really exciting stuff starts with our code. A number of engines silently spring into action; including the optimizer. The optimizer analyses your SQL statement and decides the most efficient way to execute it based on the objects involved in

It is often easy to forget this, but in many ways it is after we hit the execute button that the really exciting stuff starts with our code. A number of engines silently spring into action; including the optimizer. The optimizer analyses your SQL statement and decides the most efficient way to execute it based on the objects involved in the statement and the conditions you’re subjecting them to. Your database automatically gathers stats about your objects – stuff like the number of rows, number of distinct values, of nulls, data distribution – and the optimizer uses this information in its decision-making. (You can study the explain plan to see what decisions the optimizer has taken.)  The optimizer arrives at its conclusions, often in barely a whisper of time.

And when the SQL statement is executed, you sit back and you feel like a genius.

And that’s it, right? THE END.

Optimizer Hints

Well, not necessarily. The optimizer is the hero of our story; let me introduce the potential villains, Optimizer Hints. An optimizer hint is a code snippet within an SQL statement controlling the decisions of the optimizer. Hints give us the opportunity, in cases where we have superior knowledge about the database, to influence the optimizer. In fact, the very name is a misnomer – they are not hints; they are commands that override the optimizer (as long as the hint is valid and the _OPTIMIZER_IGNORE_HINTS initialization parameter is not TRUE).

Hints are injected into DML statements within the bounds of a comment. The syntax is as follows:

Also valid is the less fashionable

The + tells Oracle that this isn’t an ordinary comment, that it is in fact a hint. No spaces are allowed between the comment delimiter and the plus sign.

Here’s an example instructing that a full table scan should be carried out on the emp table:

That’s pretty straightforward. So why am I painting optimizer hints as the baddies here? My All Things Oracle colleague Jonathan Lewis tells us why in his Rules for Hinting (imagine Brad Pitt reading these out, if you like: The first rule of Fight Club is…):

  1. Don’t.
  2. If you must use hints, assume you’ve used them incorrectly. So don’t.
  3. With every Oracle patch or upgrade, assume every hint is going to go wrong. So don’t.
  4. With every DDL, assume every hint applied to that object is going to go wrong. So don’t.

The reason to be wary of hinting is that by embedding hints in your SQL, you are overriding the optimizer and saying that you know more than it does – not just now, but every time in the future that your SQL will be run, irrespective of any other changes that may happen to your database. The likely consequence of this is that your SQL will possibly run sub-optimally now and almost certainly in the future.

You can relax the rules slightly in your test environment, especially after massive data or structural changes may have thrown the optimizer temporarily off the scent. You may also experiment with hints when carrying out one-off operations. However, if you ever feel the need to include hints in your application in your Production environment, remember Brad Pitt’s First Rule of Hinting:

Don’t.

And that’s it. THE END.

Hints in Detail

Well, not necessarily. Cos telling you about these enchanting things called hints and then telling you to immediately forget everything you’ve just heard would be like telling you there’s a tree of knowledge in the middle of the garden but that you must never, ever eat its apple. And we know how that story ends.

So, more details. There are many dozens of different hints (have a look in the v$sql_hint view), however close to half of them are undocumented. So The First Rule of Hinting must really be borne in mind if you decide to use them. Here are a select few.

FIRST_ROWS(n): This hint instructs the optimizer to select a plan that returns the first n rows most efficiently.

You may also want to read up about FIRST_ROWS_1, FIRST_ROWS_10 and FIRST_ROWS_100. Of interest, also, is ALL_ROWS which details the optimizer to choose the plan that most effectively returns the resultset at the minimum cost.

NO_INDEX(<table_name> < index_name>): Instructs the optimizer to specifically not use the named index in determining a plan.

See also: the INDEX hint. You may also want to investigate INDEX_COMBINE, INDEX_JOIN, INDEX_ASC and INDEX_FFS.

LEADING(table_name): This hint tells Oracle to use the parameterised table as the first in the join order. The optimizer will consequently select a join chain that starts with this table.

Related to the LEADING hint is the ORDERED hint.  This hint instructs Oracle to join tables in the exact order in which they are listed in the FROM clause.

CACHE(table): This hint tells Oracle to add the blocks retrieved for the table to the head of the most recently used list. This might be useful with regularly-used lookup tables, for example.

Oracle caches small tables by default, making this hint redundant in many cases. Also often redundant is the NOCACHE hint, since this places blocks at the tail of the LRU list, which is also Oracle’s default behaviour with the majority of blocks.

CARDINALITY(table n): This hint instructs Oracle to use n as the table, rather than rely on its own stats. You may need to use this hint with a global temporary table, for instance.

REWRITE: This hint instructs Oracle to rewrite the query using a materialized view, irrespective of cost. To learn more about Query Rewrite, you may want to read this other article that I wrote.

PARALLEL (table n): This hint tells the optimizer to use n concurrent servers for a parallel operation.

APPEND: This hint instructs the optimizer to carry out a direct-path insert.  This may make INSERT … SELECT statements faster because inserted data is simply appended to the end of the table and any referential constraints are ignored.

RULE: This hint basically turns off the optimizer.  This hint has been deprecated and should not be used. Never ever. So that’s it, right? THE END.

Conclusion

Well, not necessarily the end. I was holidaying in Serbia recently and, when you visit a foreign country, it is always advisable to arm yourself with a few handy local words: Good day (dobar dan), thank you (hvala) – and, of course, beer (pivo). Take the examples above as your first handy words as you discover optimizer hints. There is more to learn, much more (indeed Jonathan Lewis has written dozens of articles on the subject, as have others). Hopefully, you are now ready to tackle those articles.

So that’s it, right? THE BEGINNING.