SQL Tuning

50.1 Tuning Methodology

Identify the high-load SQL (AWR "SQL ordered by Elapsed Time/CPU/Gets", ASH, SQL Monitor, V$SQLSTATS).

Get the actual execution plan with row source statistics.

Compare E-Rows with A-Rows and find the most expensive steps (Buffers, A-Time).

Fix the root cause: statistics, indexes, SQL rewrite, schema design, or plan stability.

Validate and measure. Prevent regressions.

50.2 Common Problems and Fixes

Full scan on a selective predicate Create an index. Check for implicit conversions and functions.

Gather statistics, histograms, column groups, dynamic sampling

WHERE TRUNC(order_date) = ... → range predicate, or a function-based index

Match types (VARCHAR2 column = number literal applies TO_NUMBER to the column) Set-based SQL, BULK COLLECT/FORALL Excessive hard parsing

Scalar subqueries / PL/SQL functions in SQL Joins, PRAGMA UDF, result cache

Select only the needed columns (enables index-only access) OR predicates UNION ALL, OR expansion NOT IN with nullable columns Huge sorts / hash spills to TEMP

PGA sizing, better filtering, indexes for ORDER BY Paging deep into results

Keyset pagination instead of large OFFSETs

50.3 Optimizer Hints

Hints are directives. Use them for diagnosis or as a last resort. Prefer SQL Plan Management for stability. Use DBMS_XPLAN +HINT_REPORT (19c) to see which hints were used.

SELECT /*+ INDEX(o ix_orders_customer) */ * FROM orders o WHERE customer_id = 42;
SELECT /*+ FULL(o) PARALLEL(o, 8) */ COUNT(*) FROM orders o;
SELECT /*+ LEADING(c o) USE_NL(o) */ ... FROM customers c JOIN orders o ON ...;
SELECT /*+ USE_HASH(o) */ ...;  /*+ NO_MERGE */  /*+ UNNEST */  /*+ PUSH_PRED */  /*+ MATERIALIZE */
SELECT /*+ OPT_PARAM('optimizer_adaptive_plans' 'false') */ ...;
SELECT /*+ RESULT_CACHE */ ...;  /*+ APPEND */  /*+ DYNAMIC_SAMPLING(4) */  /*+ FIRST_ROWS(10) */
SELECT /*+ MONITOR */ ...;  /*+ GATHER_PLAN_STATISTICS */

50.4 SQL Tuning Advisor and SQL Access Advisor (Tuning Pack)

DECLARE t VARCHAR2(64);
BEGIN
  t := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7h35uxf5uhmm1', time_limit => 300, task_name => 'tune_q1');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune_q1');
END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_q1') FROM dual;
-- Recommendations: gather statistics, create indexes, restructure SQL, accept a SQL profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tune_q1', name => 'prof_q1');

50.5 Plan Stability

SQL Plan Management (SPM) / SQL plan baselines Only accepted, verified plans are used. New plans are evolved after verification.

SQL Profiles Auxiliary statistics (scaling factors) from the Tuning Advisor SQL Patches

Inject hints without changing code (DBMS_SQLDIAG.CREATE_SQL_PATCH) Stored Outlines SQL Quarantine (19c, Exadata) Blocks runaway SQL plans

-- Load a plan from the cursor cache into a baseline
DECLARE n PLS_INTEGER;
BEGIN n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7h35uxf5uhmm1', plan_hash_value => 1234567890); END;
/
SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines;
-- Evolve
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_abc123') FROM dual;
-- Parameters: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, OPTIMIZER_USE_SQL_PLAN_BASELINES
-- 23ai: real-time SPM (automatic regression detection and repair)

-- SQL patch
EXEC DBMS_SQLDIAG.CREATE_SQL_PATCH(sql_id => '7h35uxf5uhmm1', hint_text => 'INDEX(o ix_orders_customer)', name => 'patch_q1');

50.6 SQL Performance Analyzer and Real Application Testing

SQL Tuning Sets (STS): capture workloads.

SQL Performance Analyzer (SPA): compare SQL performance before and after a change (upgrade, parameter, index).

Database Replay: capture and replay a production workload.