How to use Optimizer hints



What is Optimizer?

When a query is submitted in Oracle server. optimizer attempts to generate the best execution plan for a SQL statement. The best execution plan is defined as the plan with the lowest cost among all considered candidate plans. The cost computation accounts for factors of query execution such as I/O, CPU, and communication.

The best method of execution depends on myriad conditions including how the query is written, the size of the data set, the layout of the data, and which access structures exist. The optimizer determines the best plan for a SQL statement by examining multiple access methods, such as full table scan or index scans, and different join methods such as nested loops and hash joins.

What is a Optimizer Hint ?
Optimizer Hints are clause that  used in sql queries to force the optimizer to take particular route like using index,nested loop join.
Syntax to use hint/*+ < Hint name>*/

You can find hint name from the view   v$sql_hint

Some of commonly used hint

SELECT /*+ ALL_ROWS */ empid, last_name, sal FROM emp;

This hints  the optimizer to choose the plan that most effectively returns the resultset at the minimum cost

SELECT /*+ FIRST_ROWS */ * FROM emp;

This hint instructs the optimizer to select a plan that returns the first  rows most efficiently..

SELECT /*+ FIRST_20_ROWS */ * FROM emp;

This hint instructs the optimizer to select a plan that returns the first 20 rows most efficiently..

SELECT /*+ FIRST_ROWS(100) */ empid, last_name, sal FROM emp;

This hint instructs the optimizer to select a plan that returns the first 100 rows most efficiently..

/*+INDEX(Index name)*/

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

/*+ NO_INDEX((Index name) */

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

/*+INDEX_JOIN(emp index1, index2)*/ /*+INDEX_COMBINE(emp bitmap1, bitmap2)*/ /*+AND_EQUAL(emp index1, index2,…)*/
/*+ dynamic_sampling(A 3) */

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

USE_NL(table table)

This hint optimizer to use Nested Loop join the specified tables

USE_MERGE(table table)

This hint optimizer to use Merge join the specified tables
USE_HASH(table table)

This hint optimizer to use hash join the specified tables

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

Important points

  1. It is no recommended to use Hints as such and it should be avoided  as with patches and upgrade, hint may generate sub-optimal plan and degrade the performance
  2. Hint syntax should be used carefully else this will be ignored

Related performance Tuning Articles

Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof)

sql performance tuning

Nested Loop Join in Oracle 11g

Hash join in Oracle with example

How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility

Autotrace Utility

All about AWR ( Automatic Workload Repository)


Leave a Reply