Tuesday, October 27, 2015

Oracle Hints in SQL and PLSQL

Oracle hints we use in the SQL 's to improve performance. But some time it will reduce performance too so try test possibilities and use them properly.

Some of the more useful hints are:

The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

ORDERED - usually with USE_NL to get Oracle to not hash join

INDEX(t index_name) - where Oracle chooses the wrong index over the correct one

NO_INDEX - prevent an index from being used

INDEX_COMBINE - merging bitmap indexes (use when Oracle does not merge bitmap indexes)

FIRST_ROWS(n) - when you only want the first few rows

PARALLEL - to force parallel query on certain specific queries

GATHER_PLAN_STATISTICS - used as a handy sql trace

DYNAMIC_SAMPLING - used as alternative to statistics for large-scale warehouse queries

OPT_PARAM - used to control optimizer behavior at query level (added in

QB_NAME - specify query block name when tuning complex queries. It helps in finding a particular query for troubleshooting (10 and up)

CARDINALITY - give the optimizer better information

[edit]Some examples


SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id , b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ...

Note that the hints could have been also been in this format: SELECT --+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb)
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;

suggesting that a FULL TABLE SCAN method be used:

SELECT /*+ FULL(x) */ FROM tab1 x WHERE col1 = 10;

Suggest that Oracle uses a specific index:

SELECT /*+ INDEX(x emp_idx1) */ ... FROM scott.emp x...

Suggest that Oracle DOES NOT USE a specific index:

SELECT /*+ NO_INDEX(x emp_idx1) */ ... FROM scott.emp x...

No comments:

Post a Comment