Hints - Introduction and Impact on optimizer

Optimizer hints are special comments in a SQL statement that pass instructions to the optimizer.
The optimizer uses hints to choose an execution plan for the statement unless prevented by some condition.

About Optimizer Hints

A hint is embedded within a SQL comment.
The hint comment must immediately follow the first keyword of a SQL statement block.
You can use either style of comment: a slash-star (/*) or pair of dashes (--). The plus-sign (+) hint delimiter must immediately follow the comment delimiter,
with no space permitted before the plus sign, as in the following fragment:

SELECT /*+ hint_text */ ...
OR
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;

Purpose of Hints

Hints enable you to make decisions normally made by the optimizer.

You can use hints to influence the optimizer mode, query transformation, access path, join order, and join methods.
For example, you may know that an index is more selective for certain queries, leading to a better plan.

Types of Hints

Hints fall into the following types:
•  Single-table
Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints. 
The following statement uses a single-table hint:
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id FROM   employees WHERE  department_id > 50;

•  Multitable
Multitable hints are like single-table hints except that the hint can specify multiple tables or views. LEADING is an example of a multitable hint.
The following statement uses a multitable hint:
SELECT /*+ LEADING(e j) */ * FROM   employees e, departments d, job_history j WHERE  e.department_id = d.department_id
AND    e.hire_date = j.start_date;

•  Query block
Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.
The following statement uses a query block hint to specify that the FULL hint applies only to the query block that references employees:

 SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)
FROM   jobs t1
WHERE t1.job_id IN (SELECT job_id FROM employees t1);

•  Statement
Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint. The following statement uses a statement hint:
SELECT /*+ ALL_ROWS */ * FROM sales;

Reporting on Hints (From 19c)

Pre-19c it wasn’t easy to get exact reason why Hints are not used or why conflict occurs.But POST 19c same has been enhanced now.
An explained plan includes a report showing which hints were used during plan generation.

The hint usage report solves this problem, why the optimizer did not use hints .
The hint report shows which hints were used and ignored, and typically explains why hints were ignored.

The most common reasons for ignoring hints are as follows:

•    Syntax errors
A hint can contain a typo or an invalid argument.
For example, in the hint specification /*+ INDEX(t1) FULL(t2) MERG(v) USE_NL(t2) */, MERG(v) has a syntax error.
The optimizer honors INDEX(t1) and FULL(t2), but ignores MERG(v) and USE_NL(t2).
The hint usage report lists MERG(v) as having an error, but does not list USE_NL(t2) because it is not parsed.

•    Unresolved hints
An unresolved hint is invalid for a reason other than a syntax error.
For example, a statement specifies INDEX(employees emp_idx), where emp_idx is not a valid index name for table employees.

•    Conflicting hints
The database ignores combinations of conflicting hints, even if these hints are correctly specified.
For example, a statement specifies FULL(employees) INDEX(employees), but an index scan and full table scan are mutually exclusive.

•    Hints affected by transformations
A transformation can make some hints invalid.
For example, a statement specifies PUSH_PRED(some_view) MERGE(some_view).

User Interface for Hint Usage Reports

Hint tracking is enabled by default. You can access the hint usage report by using the following DBMS_XPLAN functions:
•    DISPLAY
•    DISPLAY_CURSOR
•    DISPLAY_WORKLOAD_REPOSITORY
•    DISPLAY_SQL_PLAN_BASELINE
•    DISPLAY_SQLSET
The preceding functions generate a report when you specify the value HINT_REPORT in the format parameter.
The value TYPICAL displays only the hints that are not used in the final plan, whereas the value ALL displays both used and unused hints.

DEMO

=> Hints Conflicts to another hints

SQL> explain plan for select /*+ first_rows(5) all_rows */ * from EMP_TEST;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'ALL -projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
Plan hash value: 3124080142
------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	 107 |	7383 |	   3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP_TEST |	 107 |	7383 |	   3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP_TEST@SEL$1

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   0 -	STATEMENT
	 U -  all_rows / hint conflicts with another in sibling query block
	 U -  first_rows(5) / hint conflicts with another in sibling query block

33 rows selected.
As WE CAN SEE ALL_ROWS and FIRST_ROWS are mutually exclusive ,hence same has been ignored due to conflicts

=> When forcefully ignored hints

SQL> alter session set optimizer_ignore_hints=true;
Session altered.

SQL> explain plan for select /*+ first_rows(5) all_rows */ * from EMP_TEST;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'ALL -projection'));

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 3124080142
------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	 107 |	7383 |	   3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP_TEST |	 107 |	7383 |	   3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP_TEST@SEL$1

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   0 -	STATEMENT
	 U -  all_rows / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
	 U -  first_rows(5) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

33 rows selected.

SQL> 

=> When  No Join methods are available - forced by hints

SQL> set lines 300
SQL> set pages 100
SQL>  explain plan for select /*+ leading(DEPARTMENT_ID) no_use_nl(EMP_TEST) no_use_hash(EMP_TEST) no_use_merge(EMP_TEST) */ * from EMP_TEST join DEP_TEST using(DEPARTMENT_ID);

Explained.

SQL> select * from table(dbms_xplan.display(format=>'ALL -projection'));
PLAN_TABLE_OUTPUT
------------------------------
Plan hash value: 100746567

-------------------------------------------------------------------------------
| Id  | Operation	   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	      |   106 |  9540 |     6	(0)| 00:00:01 |
|*  1 |  HASH JOIN	   |	      |   106 |  9540 |     6	(0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEP_TEST |    27 |   567 |     3	(0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP_TEST |   107 |  7383 |     3	(0)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$58A6D7F6
   2 - SEL$58A6D7F6 / DEP_TEST@SEL$1
   3 - SEL$58A6D7F6 / EMP_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP_TEST"."DEPARTMENT_ID"="DEP_TEST"."DEPARTMENT_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (4))
---------------------------------------------------------------------------

   1 -	SEL$58A6D7F6
	 U -  leading(DEPARTMENT_ID)

   3 -	SEL$58A6D7F6 / EMP_TEST@SEL$1
	 U -  no_use_hash(EMP_TEST) / all join methods are excluded by hints
	 U -  no_use_merge(EMP_TEST) / all join methods are excluded by hints
	 U -  no_use_nl(EMP_TEST) / all join methods are excluded by hints

60 rows selected.

=> When Hints are used

SQL>  explain plan for select /*+ PARALLEL(20) */ * from EMP_TEST;
Explained.

SQL> select * from table(dbms_xplan.display(format=>'ALL -projection'));
PLAN_TABLE_OUTPUT
------
Plan hash value: 1161765406
--------------------------------------------------------------------------------------------------------------
| Id  | Operation	     | Name	| Rows	| Bytes | Cost (%CPU)| Time	|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		|   107 |  7383 |     2   (0)| 00:00:01 |	 |	|	     |
|   1 |  PX COORDINATOR      |		|	|	|	     |		|	 |	|	     |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   107 |  7383 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |		|   107 |  7383 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |	     |
|   4 |     TABLE ACCESS FULL| EMP_TEST |   107 |  7383 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |	     |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / EMP_TEST@SEL$1

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   0 -	STATEMENT
	   -  PARALLEL(20)
Note
-----
   - Degree of Parallelism is 20 because of hint
28 rows selected.

SQL>