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.
Hints have to be included as a comment followed by a +-sign right after the first keyword of the statement.
Some useful hints are:
There is a data dictionary view which lists all available hints since 11.2:
AKIRA@CPI-E1> desc v$sql_hint;
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...
create or replace trigger change_session_parameters
select * from table(dbms_xplan.display_cursor('sql_id',null,'OUTLINE'));