Mastering Oracle Hints: Benefits, Risks, and Best‑Practice Guidance

Background – In many enterprises, developers favor Oracle over other databases because it allows explicit control of the optimizer through Hints. A Hint is a specially formatted comment in a SQL statement that forces the optimizer to follow a chosen execution plan, such as using a specific index or join method.

Review of Oracle Hints

Common Hint categories include optimizer modes (e.g., /*+ ALL_ROWS */, /*+ FIRST_ROWS(n) */, /*+ RULE */), access paths (e.g., /*+ FULL(table) */, /*+ INDEX(table index) */, /*+ USE_NL(table) */), query transformations (e.g., /*+ NO_QUERY_TRANSFORMATION */, /*+ REWRITE */), join order control (e.g., /*+ ORDERED */, /*+ LEADING */), join operation types (e.g., /*+ USE_HASH */, /*+ USE_MERGE */), and miscellaneous options (e.g., /*+ APPEND */, /*+ DYNAMIC_SAMPLING(level) */, /*+ MONITOR */). Each Hint directs the optimizer to a specific behavior, overriding its default cost‑based decisions.

Negative Cases of Hint Misuse

Case 1 – Improper /*+ USE_NL(a) */

A CRM query joining a tiny table ( smtemplate) with a small lookup table ( smnotify_kf) forced a nested‑loop join via /*+ USE_NL(a) */. The optimizer would have chosen a hash join, resulting in memory usage of 9,171,128 bytes. After removing the Hint, the plan switched to a hash join and memory dropped to 3,385 bytes.

Case 2 – Misusing /*+ USE_MERGE */

A merge statement joining a large and a small table originally used a full‑table scan on both sides. After adding an index on the large table, the plan still used a sort‑merge join because of the /*+ USE_MERGE */ Hint. Removing the Hint allowed the optimizer to select a nested‑loop join with the new index, reducing execution time from over 20 minutes to 7 seconds.

Oracle’s Official View and Best Practices

Hint as a last resort – Oracle trusts its Cost‑Based Optimizer (CBO) when statistics are accurate. Hints should only be applied after confirming that statistics are up‑to‑date and the optimizer still produces sub‑optimal plans.

Stability does not equal optimality – Forcing a stable plan can backfire as data distributions change; modern Oracle versions provide adaptive features (SPM, Adaptive Cursor Sharing, Adaptive Query Optimization) that adjust plans dynamically.

Maintenance overhead – Extensive Hint usage requires deep knowledge of data distribution and incurs high maintenance cost when data volume, schema, or Oracle version changes.

View‑level caution – Applying Hints inside or on views can produce unpredictable results because the optimizer may rewrite or push predicates differently.

_OPTIMIZER_IGNORE_HINTS – Setting this hidden parameter to TRUE tells the optimizer to ignore all Hints, useful when migrating to newer Oracle releases where existing Hints become detrimental.

Not a one‑size‑fits‑all – Certain features (e.g., Result Cache, parallel execution) still benefit from explicit Hints, but they should be evaluated case‑by‑case.

Conclusion

Historically, developers relied heavily on Hints to compensate for optimizer shortcomings. With newer Oracle releases, the optimizer has become more intelligent and adaptive, reducing the need for manual Hint enforcement. Users should keep their Hints minimal, validate them against current statistics, and adopt Oracle’s adaptive optimization features to ensure sustainable performance.