Hints

Parameters#

Parallel Hint

Statement-level parallel hints are the easiest:

SELECT /*+ PARALLEL(8) */ first_name, last_name FROM employee emp;

Object-level parallel hints give more control but are more prone to errors; developers often forget to use the alias instead of the object name, or they forget to include some objects.

SELECT /*+ PARALLEL(emp,8) */ first_name, last_name FROM employee emp;

SELECT /*+ PARALLEL(table_alias,Degree of Parallelism) */ FROM table_name table_alias;

Let’s say a query takes 100 seconds to execute without using parallel hint. If we change DOP to 2 for same query, then ideally the same query with parallel hint will take 50 second. Similarly using DOP as 4 will take 25 seconds.

In practice, parallel execution depends on many other factors and does not scale linearly. This is especially true for small run times where the parallel overhead may be larger than the gains from running in multiple parallel servers.

USE_NL

Use Nested Loops.

Usage : use_nl(A B)

This hint will ask the engine to use nested loop method to join the tables A and B. That is row by row comparison. The hint does not force the order of the join, just asks for NL.

SELECT /*+use_nl(e d)*/ *
FROM Employees E
JOIN Departments D on E.DepartmentID = D.ID

APPEND HINT

“Use DIRECT PATH method for inserting new rows”.

The APPEND hint instructs the engine to use direct path load. This means that the engine will not use a conventional insert using memory structures and standard locks, but will write directly to the tablespace the data. Always creates new blocks which are appended to the table’s segment. This will be faster, but have some limitations:

• You cannot read from the table you appended in the same session until you commmit or rollback the transaction.
• If there are triggers defined on the table Oracle will not use direct path(it’s a different story for sqlldr loads).
• others

Example.

INSERT /*+append*/ INTO Employees
SELECT *
FROM Employees;

USE_HASH

Instructs the engine to use hash method to join tables in the argument.

Usage : use_hash(TableA [TableB] ... [TableN])

As explained in many places, “in a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one) and probes the hash table for matches to it.”

It is preferred against Nested Loops method when the tables are big, no indexes are at hand, etc.

Note: The hint does not force the order of the join, just asks for HASH JOIN method.

Example of usage:

SELECT /*+use_hash(e d)*/ *
FROM Employees E
JOIN Departments D on E.DepartmentID = D.ID

FULL

The FULL hint tells Oracle to perform a full table scan on a specified table, no matter if an index can be used.

create table fullTable(id) as select level from dual connect by level < 100000;
create index idx on fullTable(id);

With no hints, the index is used:

select count(1) from fullTable f where id between 10 and 100;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX  |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

FULL hint forces a full scan:

select /*+ full(f) */ count(1) from fullTable f where id between 10 and 100;
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    13 |    47   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| FULLTABLE |     2 |    26 |    47   (3)| 00:00:01 |
--------------------------------------------------------------------------------

Result Cache

Oracle (11g and above) allows the SQL queries to be cached in the SGA and reused to improve performance. It queries the data from cache rather than database. Subsequent execution of same query is faster because now the data is being pulled from cache.

SELECT /*+ result_cache */ number FROM main_table;

Output -

Number
------
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

Elapsed: 00:00:02.20

If I run the same query again now, the time to execute will reduce since the data is now fetched from cache which was set during the first execution.

Output -

Number
------
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

Elapsed: 00:00:00.10

Notice how the elapsed time reduced from 2.20 seconds to 0.10 seconds.

Result Cache holds the cache until the data in database is updated/altered/deleted. Any change will release the cache.