✅ Oracle Optimizer Hints – Complete Performance Guide

---

1️⃣ Access Path Hints (HOW data is accessed)

🔹 FULL

SELECT /*+ FULL(emp) */ * FROM emp;

✅ Use when

• Table scan is cheaper than index (large result set)
• Data warehouse / batch queries
• Index is highly fragmented or low selectivity

❌ Avoid when

• OLTP queries
• Highly selective predicates

---

🔹 INDEX

SELECT /*+ INDEX(emp emp_idx1) */ * FROM emp WHERE empno=100;

✅ Use when

• Optimizer ignores a good index
• Predicate is highly selective

❌ Avoid when

• Index clustering factor is poor
• Query returns large % of table

---

🔹 INDEX_FFS (Fast Full Scan)

SELECT /*+ INDEX_FFS(emp emp_idx1) */ empno FROM emp;

✅ Use when

• Index contains all required columns
• Avoids table access
• Batch/reporting queries

❌ Avoid when

• OLTP row lookup
• Index selective scan is better

---

🔹 NO_INDEX

SELECT /*+ NO_INDEX(emp emp_idx1) */ * FROM emp;

✅ Use when

• Bad index is wrongly chosen
• Index causes excessive random IO

---

2️⃣ Join Method Hints (HOW tables are joined)

Hint	Best For	Notes
USE_NL	Small → Large joins	OLTP
USE_HASH	Large ↔ Large	DWH
USE_MERGE	Sorted inputs	Rare

---

🔹 USE_NL

SELECT /*+ USE_NL(o c) */ *

FROM orders o, customers c

WHERE o.cust_id = c.cust_id;

✅ Use when

• Driving table is small
• Index exists on joined column

❌ Avoid when

• Large datasets
• No index → CPU disaster

---

🔹 USE_HASH

SELECT /*+ USE_HASH(o c) */ *

FROM orders o, customers c;

✅ Use when

• Large volume joins
• Data warehouse queries

❌ Avoid when

• OLTP
• Memory constrained systems

---

🔹 LEADING

SELECT /*+ LEADING(o c l) */ ...

✅ Use when

• Optimizer chooses wrong driving table
• Join order critical

❌ Avoid when

• Tables grow unpredictably

---

3️⃣ Parallel Execution Hints

🔹 PARALLEL

SELECT /*+ PARALLEL(orders 8) */ * FROM orders;

✅ Use when

• Batch jobs
• Reporting queries
• Offline processing

❌ Avoid when

• OLTP
• CPU saturation risk

---

🔹 NOPARALLEL

SELECT /*+ NOPARALLEL */ * FROM orders;

✅ Use when

• Unexpected parallelism
• CPU contention scenarios

---

4️⃣ Subquery & Query Transformation Hints

🔹 UNNEST

SELECT /*+ UNNEST */ *

FROM emp

WHERE deptno IN (SELECT deptno FROM dept);

✅ Use when

• Subquery performs badly
• Join equivalent is faster

---

🔹 NO_UNNEST

SELECT /*+ NO_UNNEST */ ...

✅ Use when

• Subquery logic must be preserved
• Optimizer transforms incorrectly

---

🔹 PUSH_SUBQ

✅ Use when

• Filter subquery earlier
• Reduce result set sooner

---

5️⃣ Aggregation & Grouping

🔹 HASH_GROUP_BY

SELECT /*+ HASH_GROUP_BY */ deptno, COUNT(*)

FROM emp GROUP BY deptno;

✅ Use when

• Large aggregations
• Enough memory available

---

🔹 SORT_GROUP_BY

✅ Use when

• Small datasets
• Avoid hash memory usage

---

6️⃣ Result Cache Hints

🔹 RESULT_CACHE

SELECT /*+ RESULT_CACHE */ COUNT(*) FROM country;

✅ Use when

• Read‑mostly tables
• Reference data

❌ Avoid when

• High DML rate tables

---

7️⃣ Cursor & Parsing Hints

🔹 BIND_AWARE

✅ Use when

• Data skew exists
• Bind peeking causes bad plans

---

🔹 CURSOR_SHARING_EXACT

✅ Use when

• Prevent unwanted cursor sharing
• SQL stability is critical

---

8️⃣ Anti‑Hints (Disable Optimizer Features)

🔹 NO_MERGE

SELECT /*+ NO_MERGE */ ...

✅ Use when

• View merge causes bad plans

---

🔹 NO_PARALLEL

✅ Use when

• Unexpected PX usage

---

🔹 NO_GATHER_OPTIMIZER_STATISTICS

✅ Use when

• Query stats collection causes overhead

---

9️⃣ When SHOULD You Use Hints ✅

✔ Reproducible bad execution plan
✔ Statistics verified as accurate
✔ SQL rewrite not feasible
✔ Emergency performance fix
✔ Plan stability required

---

🔟 When You Should NOT Use Hints ❌

❌ As first tuning action
❌ Without understanding execution plan
❌ For dynamic workloads
❌ Across application code blindly
❌ Instead of fixing SQL design

---

🔑 Best‑Practice Strategy (Golden Order)

1️⃣ Fix SQL logic
2️⃣ Add correct indexes
3️⃣ Refresh statistics
4️⃣ SQL Profiles
5️⃣ SQL Baselines
6️⃣ Hints (last resort)

✅ When to Use Hints

✔ Emergency fix
✔ Ad‑hoc SQL
✔ No control over stats
✔ Temporary workaround

---

✅ When to Use SQL Baselines (Recommended)

✔ Production applications
✔ Long‑term plan stability
✔ After tuning complete

✔ Upgrade‑safe deployments 

---

🎯 Real‑World DBA Tip

Hints freeze assumptions. Plans age badly.
Use them surgically and document heavily.