Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 38 ยท Performance and Administration

Oracle SQL Tuning, Bind Variables and Optimizer Hints

Practical Oracle SQL tuning: bind variables and the shared pool, set-based rewrites, sargable predicates, EXISTS versus IN, pagination, optimizer hints and SQL monitoring tools.

Tuning is not magic. Most slow Oracle code falls into a handful of patterns, and fixing them is more effective than any hint or parameter. In this lesson you will learn the habits that make statements fast from the start, then the tools to diagnose the ones that are not, and finally optimizer hints, which are the last resort rather than the first.

Oracle cannot run on this site. Oracle code is read-only. Runnable examples use Python and SQLite as stand-ins to show the effect of the same ideas (bind variables, set-based work, sargable predicates).

1. Use bind variables

Every distinct SQL text must be parsed and planned. Oracle caches parsed statements in the shared pool keyed by the text. If you build statements by concatenating values, each one is new:

SQL
-- hard parse every time, fills the shared pool, invites SQL injection
EXECUTE IMMEDIATE 'SELECT salary FROM employees WHERE employee_id = ' || p_id INTO v_salary;

-- one shared cursor, safe
EXECUTE IMMEDIATE 'SELECT salary FROM employees WHERE employee_id = :1' INTO v_salary USING p_id;

Static SQL inside PL/SQL (SELECT ... INTO ... WHERE employee_id = p_id) automatically turns PL/SQL variables into binds. The problem arises mostly with dynamic SQL and with applications that build strings. A system doing 100,000 unique statements per minute spends its time parsing and fights over shared-pool latches, so scalability collapses.

Python Cached statements versus unique text (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

2. Think in sets, not rows

The slowest code is a loop that runs one statement per row ("row by row", also called "slow by slow"). Replace it with one statement:

SQL
-- slow: 50,000 statements and context switches
FOR r IN (SELECT employee_id FROM employees WHERE department_id = 50) LOOP
  UPDATE employees SET salary = salary * 1.05 WHERE employee_id = r.employee_id;
END LOOP;

-- fast: one statement
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 50;

MERGE handles "insert or update" in one pass, INSERT ... SELECT copies sets, and analytic functions (ROW_NUMBER() OVER (...), SUM() OVER (...)) replace many self-joins and loops. When per-row logic is truly unavoidable, use BULK COLLECT and FORALL.

3. Keep predicates sargable

A predicate is sargable (search-argument-able) when the optimizer can use an index to evaluate it. Wrapping the column in a function or conversion breaks that:

Slow (index ignored) Better
WHERE TRUNC(hire_date) = DATE '2026-01-05' WHERE hire_date >= DATE '2026-01-05' AND hire_date < DATE '2026-01-06'
WHERE salary + 100 > 5000 WHERE salary > 4900
WHERE TO_CHAR(order_id) = '42' WHERE order_id = 42
WHERE SUBSTR(name, 1, 3) = 'Smi' WHERE name LIKE 'Smi%'
WHERE NVL(commission, 0) > 0 WHERE commission > 0

Move the computation to the other side of the comparison, or create a function-based index on the expression. Beware implicit conversion: comparing a VARCHAR2 column to a number makes Oracle convert the column, which defeats the index and can fail with ORA-01722 on odd data.

SQL A sargable rewrite (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

4. Write the query shape the optimizer likes

  • Select only the columns you need. SELECT * prevents covering-index plans and moves extra data.
  • EXISTS for "is there a matching row", rather than joining and removing duplicates with DISTINCT. EXISTS stops at the first match. IN with a subquery is fine too; Oracle usually rewrites both to the same semi-join. Use NOT EXISTS instead of NOT IN when the subquery column can be NULL: NOT IN returns no rows if even one NULL appears.
  • Use UNION ALL instead of UNION when duplicates cannot occur; UNION adds a sort to remove them.
  • Filter early. Apply WHERE conditions before joining and avoid HAVING for conditions that can go in WHERE.
  • Avoid COUNT(*) just to test existence. Use EXISTS or FETCH FIRST 1 ROW ONLY.
  • Paginate with the row limiting clause (12c+), not nested ROWNUM tricks: ORDER BY salary DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. For deep pages, "keyset" pagination (WHERE salary < :last_salary OR (salary = :last_salary AND id < :last_id)) beats large offsets, because an offset still reads and discards the skipped rows.

5. Optimizer hints

A hint is a comment placed right after SELECT, UPDATE, DELETE or INSERT that suggests a plan to the optimizer:

SQL
SELECT /*+ INDEX(e emp_dept_ix) */ first_name
  FROM employees e
 WHERE department_id = 50;

SELECT /*+ FULL(e) PARALLEL(e 4) */ COUNT(*) FROM employees e;

SELECT /*+ LEADING(d e) USE_NL(e) */ e.first_name, d.department_name
  FROM departments d JOIN employees e ON e.department_id = d.department_id;

Common hints: INDEX, FULL, LEADING (join order), USE_NL, USE_HASH, PARALLEL, FIRST_ROWS(n), ALL_ROWS, APPEND (direct-path insert), and GATHER_PLAN_STATISTICS (diagnostics only, see execution plans).

Treat hints with caution. A hint freezes a decision that was right for today's data; when the data changes, the hint keeps forcing the old plan. An invalid hint is silently ignored, with no error. Misspelled aliases are the typical cause. Prefer, in order: correct statistics, better indexes, query rewrites, then SQL plan baselines (DBMS_SPM) or SQL profiles from the SQL Tuning Advisor, which steer plans without editing application code. Hints are acceptable for a known, stable, last-mile fix and in diagnostics.

6. Tools to find the problem

Tool Purpose
V$SQL, V$SQLSTATS Top statements by elapsed time, CPU, buffer gets, executions.
AWR and ASH reports Time-based history of load and waits (separately licensed Diagnostics Pack).
SQL Monitor (DBMS_SQLTUNE.REPORT_SQL_MONITOR) Live and historical plan with per-step time (needs the Tuning Pack).
SQL trace and tkprof Detailed per-statement report for one session.
DBMS_PROFILER / DBMS_HPROF Line-by-line profile of PL/SQL code.
SQL Tuning Advisor Suggests indexes, statistics and profiles.

A reliable first look: sort V$SQL by ELAPSED_TIME, find the top statement, and check whether it is slow because it runs once for a long time (fix the plan) or millions of times for a short time (fix the caller's loop).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why are bind variables better than concatenating values into SQL text?

    Identical text means a shared cursor, so no repeated hard parses; values are never interpreted as SQL.
  2. 2. Which predicate can use a plain index on salary?

    Only the predicate where the column stands alone is sargable.
  3. 3. Why prefer NOT EXISTS over NOT IN when the subquery may return NULL?

    x NOT IN (1, NULL) evaluates to unknown for every x, so nothing is returned.
  4. 4. What happens when a hint cannot be applied or is misspelled?

    An unusable hint is treated as a comment, so check the plan to confirm it worked.

Summary

  • Use bind variables, think in sets, and keep predicates sargable. These three habits fix most problems.
  • Select only needed columns, prefer EXISTS/NOT EXISTS, UNION ALL, and the row limiting clause.
  • Hints force plan choices and can go stale; prefer statistics, indexes and rewrites, then baselines.
  • Find the real problem first with V$SQL, AWR/ASH, SQL Monitor or trace.

Next: Users, Privileges and Tablespaces