How to use oracle optimizer hints for tuning

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 Oracle Optimizer Hint?

Oracle Optimizer Hints are clauses that are used in sql queries to force the optimizer to take a particular route like using the index, nested loop join.

Syntax to use the hint

/*+ <Hint name>*/

You can find hint name from the view v$sql_hint

Some of the commonly used hint

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

Example
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM emp WHERE employee_id = 12;

This is a statement-level hint. This hints the optimizer to choose the plan that most effectively returns the result set at the minimum cost

SELECT /*+ FIRST_ROWS */ * FROM emp;

Example
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary FROM emp WHERE department_id = 50;

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

SELECT /*+ FIRST_20_ROWS */ * FROM emp;

This is a statement-level hint. 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 is again a statement-level hint. 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.
This is an access path Hint

/*+ NO_INDEX((Index name) */

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

Other Index related hints are

/*+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 joined chain that starts with this table. This is a join order hint

/*+ USE_NL(table table) */

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity FROM orders h ,order_items l WHERE l.order_id = h.order_id;

This hints optimizer to use Nested Loop join the specified tables. This is join operation hint

/*+ USE_MERGE(table table) */

This hints optimizer to use Merge join the specified tables. This is join operation hint

/*+ USE_HASH(table table) */

SELECT /*+ USE_HASH(l h) */ * FROM orders h, order_items l WHERE l.order_id = h.order_id AND l.order_id > 3500;

This hints optimizer to use hash join the specified tables. This is join operation hint

PARALLEL (table n)

SELECT /*+ FULL(a) PARALLEL(a, 2) */ last_name FROM employees a;

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 cost optimizer

Important points about HINT usage

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

Hint syntax should be used carefully else this will be ignored
Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.

What is Optimizer?

What is a Oracle Optimizer Hint?

Important points about HINT usage

Related Posts

Leave a Comment Cancel Reply