One of my favourite quotes from Maria Colgan is: “Optimizer hints should only be used with extreme care”
Let me give you an example of why that is the case.
As anyone would know, one of the key aims of anyone building an application running on a database is the minimize risk. We have things like RAC and DataGuard to minimise risk of outage due to hardware failure. We have things like security privileges and auditing to minimise the risk of data being stolen. And of course, we want to minimise the risk of performance problems due to poorly performing SQL.
Which leads to the question – Does using a hint reduce the risk of SQL performance issues or increase it?
Consider the following hints designed to improve an optimizer decision
--
-- Force a merge join
--
select /*+ merge(e d) */ *
from emp e, dept d
where d.deptno = e.deptno;
--
-- Force a full scan
--
select /*+ full(employees) */ *
from hr.employees e
where employee_id = 1234;
--
-- Force a hash join
--
select /*+ hash(e d) */ *
from emp e, dept d
where d.deptno = e.deptno;
--
-- Drive the query by DEPT then EMP
--
select /*+ ordered(d e) */ *
from emp e, dept d
where d.deptno = e.deptno;
--
-- Force access by INDEX
--
select /*+ index(EMP_EMP_ID_PK) */ *
from hr.employees e
where employee_id = 1234;
--
-- Dynamically sample the tables to help the optimizer
--
select /*+ dynamic_sampling */ count(*)
from employees e;
--
-- Do not allow a parallel operation
--
select /*+ no_parallel(t) */ max(owner), min(object_name)
from t;
--
-- Force a full scan
--
select /*+ full(hr.employees) */ *
from hr.employees
where employee_id = 1234;
Notice anything?
Every single one of those hints is invalid in some way. None of them are guaranteed to achieve the desired result.
Thankfully you can get some help (from 19c onwards) with the Hint Report section in the execution plan.
SQL> select /*+ merge(e d) */ *
2 from emp e, dept d
3 where d.deptno = e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 798 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 518 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - merge(e
Reason: It is the USE_MERGE hint to force a merge join not MERGE
SQL> select /*+ full(employees) */ *
2 from hr.employees e
3 where employee_id = 1234;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=1234)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$1
N - full(employees)
Reason: You need to use the alias “e” not “employees”
SQL> select /*+ hash(e d) */ *
2 from emp e, dept d
3 where d.deptno = e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 798 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 518 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - hash(e
Reason: It is the USE_HASH hint to force a hash join not HASH
SQL> select /*+ ordered(d e) */ *
2 from emp e, dept d
3 where d.deptno = e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 798 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 518 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - ordered
Reason: ORDERED does not take table aliases as parameters. It is the LEADING hint for that.
SQL> select /*+ index(EMP_EMP_ID_PK) */ *
2 from hr.employees e
3 where employee_id = 1234;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=1234)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$1
N - index(EMP_EMP_ID_PK)
Reason: You need a table alias as the first parameter in the INDEX hint
SQL> select /*+ dynamic_sampling */ count(*)
2 from employees e;
Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - dynamic_sampling
Reason: DYNAMIC_SAMPLING requires parameters
SQL> select /*+ no_parallel(t) */ max(owner), min(object_name)
2 from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1724422472
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3278 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 39 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 39 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 39 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1774K| 65M| 3278 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | INDEX FAST FULL SCAN| IX | 1774K| 65M| 3278 (1)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
6 - SEL$1 / "T"@"SEL$1"
U - no_parallel(t)
Reason: NO_PARALLEL on a table does not stop a parallel index scan
But in case you are thinking “Well, the hint report is all I need then”. Be aware that the hint report can’t cope with all potential hint errors.
SQL> select /*+ full(hr.employees) */ *
2 from hr.employees
3 where employee_id = 1234;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=1234)
Take care with those hints!
Ho Ho Ho! Merry Christmas!
Learning is not a spectator sport 
Got some thoughts? Leave a comment