Using Driving Table Hints to Tune Oracle Performance

By: Dan Hotka


Hints are CBO directives. If Oracle can use the hint, it will only produce explain plans that contain the hint directive. This article examines the main hints that control diriving tables. Knowing how to use these hints can help improve performance tuning.

Main hints that control driving tables

The main hints that control the driving table of a SQL statement include:

  • FULL(table [table] …)
  • ORDERED (being depreciated, may still work)
  • LEADING(table [table] …)
  • USE_NL(table table)
  • USE_MERGE(table table)
  • USE_HASH(table table)

The ORDERED hint gives the user the ability to shuffle the tables on the FROM clause.  Directing Oracle to join the tables in a specific order will alter the order of the joins.

TIP

This is the first thing that should be done when tuning SQL.  Put this hint in and shuffle the tables around on the FROM clause.  You should notice that some SQL performs better than others.  Start your tuning process with the SQL that performs the best from this exercise.

The LEADING hint is similar to the ORDERED except that it tells Oracle to drive from this table and to let the CBO make the other decisions as to join order.  You can list more than one table.

NOTE

The ORDERED hint also orders the sub queries where the LEADING hint just works with the table join order.

The USE_* drives the CBO to use nested loops, merge joins or hash joins.

Examples of driving table hints

In this section, we’ll look at an example with a full hint, an example without an ordered hint and an example that show benefit from an ordered hint.

The example below is a FULL hint illustration:

Hotka Page 110 Hints 1aHotka Page 109 Hints 1b

 

The example below does not have the ORDERED hint:

Hotka Page 110 Hints 1a

 

Hotka Page 110 Hints 1b

 

 

Finally, consider that the SQL in the example below benefited from the ORDERED hint.

Hotka Page 111 Hints 1a

Hotka Page 111 Hints 1b

?

Leave a Reply