Knowledge Base : Oracle Hints

30 November 1, 2021

A hint is code snippet embedded into an SQL statement to persuade the Oracle optimizer to take a specific approach when rendering an execution plan.

In most, if not any cases, the Oracle optimizer will provide a useful execution plan on it’s own when proper statistics are available. Trying to override the optimizes decisions may result in overall inflexibility.

The optimizer may decide not to show any reaction to a given hint at all if it doesn’t feel any sense in the hint.

Syntax

Hints have to be included as a comment followed by a +-sign right after the first keyword of the statement.

Examples:

–+RULE
/*+RULE */

Available hints

Some useful hints are:

  • ORDERED – usually used with USE_NL to prevent Oracle from using hash joins
  • INDEX(t index_name) – suggest using this index (and not a different one)
  • NO_INDEX – discourage index usage
  • INDEX_COMBINE – suggest merging bitmap indexes
  • FIRST_ROWS(n) – suggest query makes sense to use the first rows of result
  • PARALLEL – suggest parallel query
  • GATHER_PLAN_STATISTICS – sql trace
  • CARDINALITY – provide better information
  • BIND AWARE – force bind aware cursor sharing

There is a data dictionary view which lists all available hints since 11.2:

AKIRA@CPI-E1> desc v$sql_hint;
Name Null? Type
———————– ——– —————-
NAME VARCHAR2(64)
SQL_FEATURE VARCHAR2(64)
CLASS VARCHAR2(64)
INVERSE VARCHAR2(64)
TARGET_LEVEL NUMBER
PROPERTY NUMBER
VERSION VARCHAR2(25)
VERSION_OUTLINE VARCHAR2(25)

Examples

Suggest a FULL TABLE SCAN method to be used:

SELECT /+ FULL(x)/ FROM tab1 x WHERE col1 = 10;

Suggest usage of a specific index:

SELECT /+ INDEX(x emp_idx1) / … FROM scott.emp x…

Suggest not to use a specific index:

SELECT /+ NO_INDEX(x emp_idx1) / … FROM scott.emp x…

Set a hint in user environment via login trigger

create or replace trigger change_session_parameters
AFTER LOGON ON dbuser.SCHEMA
BEGIN
execute immediate ‚alter session set „_serial_direct_read“=never‘;
END;

Get the outline (full set of hints) for a given query

select * from table(dbms_xplan.display_cursor(’sql_id‘,null,’OUTLINE‘));