SQL tuning is one of the most critical skills for an Oracle DBA. A poorly written query can bring an entire system to its knees, while a well-tuned one can run in milliseconds. This guide covers the most important techniques every Oracle DBA should know.

1. Understanding the Execution Plan

The execution plan is the roadmap Oracle uses to execute your SQL. Always start here when tuning.

Using EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM
    employees   e
    JOIN departments d
        ON e.department_id = d.department_id
WHERE
    e.salary > 5000
ORDER BY
    e.last_name;

-- Display the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Using DBMS_XPLAN with actual runtime statistics

-- Step 1: Run the query with the hint
SELECT /*+ gather_plan_statistics */
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM
    employees   e
    JOIN departments d
        ON e.department_id = d.department_id
WHERE
    e.salary > 5000;

-- Step 2: Pull the actual execution plan
SELECT *
FROM
    TABLE(
        DBMS_XPLAN.DISPLAY_CURSOR(
            sql_id          => NULL,
            cursor_child_no => 0,
            format          => 'ALLSTATS LAST'
        )
    );

2. Gathering Statistics

Stale or missing statistics are the number one cause of bad execution plans in Oracle.

Gather stats on a single table

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'HR',
        tabname          => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => 4
    );
END;
/

Gather stats on an entire schema

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname          => 'HR',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => 4
    );
END;
/

Check when statistics were last gathered

SELECT
    table_name,
    num_rows,
    last_analyzed,
    stale_stats
FROM
    dba_tab_statistics
WHERE
    owner = 'HR'
ORDER BY
    last_analyzed ASC NULLS FIRST;

3. Index Usage and Management

Check if an index is being used

-- Enable monitoring on the index
ALTER INDEX hr.emp_salary_idx MONITORING USAGE;

-- After running your workload, check usage
SELECT
    index_name,
    table_name,
    monitoring,
    used,
    start_monitoring,
    end_monitoring
FROM
    v$object_usage
WHERE
    index_name = 'EMP_SALARY_IDX';

Find high-cost SQL candidates for indexing

SELECT
    sql_id,
    executions,
    disk_reads,
    buffer_gets,
    ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
    SUBSTR(sql_text, 1, 100)                   AS sql_snippet
FROM
    v$sqlstats
WHERE
    disk_reads / NULLIF(executions, 0) > 1000
ORDER BY
    disk_reads DESC
FETCH FIRST 20 ROWS ONLY;

Create a function-based index

-- Useful when queries filter on UPPER(column)
CREATE INDEX hr.emp_upper_lastname_idx
    ON hr.employees ( UPPER(last_name) );

-- This query will now use the index
SELECT
    employee_id,
    first_name,
    last_name
FROM
    hr.employees
WHERE
    UPPER(last_name) = 'KING';

4. SQL Hints

Hints are directives you give to the Oracle optimizer. Use them carefully — they override the optimizer’s decisions. Always fix the root cause (statistics, indexes) before resorting to hints.

Force an index

SELECT /*+ INDEX(e emp_salary_idx) */
    e.employee_id,
    e.first_name,
    e.salary
FROM
    hr.employees e
WHERE
    e.salary BETWEEN 5000 AND 10000;

Force a Full Table Scan

-- Sometimes faster when returning a large percentage of rows
SELECT /*+ FULL(e) */
    e.employee_id,
    e.first_name,
    e.salary
FROM
    hr.employees e
WHERE
    e.department_id = 90;

Control join methods

-- Force a Hash Join (good for large tables)
SELECT /*+ USE_HASH(e d) */
    e.employee_id,
    e.last_name,
    d.department_name
FROM
    hr.employees   e
    JOIN hr.departments d
        ON e.department_id = d.department_id;

-- Force a Nested Loop Join (good for small driving sets)
SELECT /*+ USE_NL(e d) */
    e.employee_id,
    e.last_name,
    d.department_name
FROM
    hr.employees   e
    JOIN hr.departments d
        ON e.department_id = d.department_id;

Parallel query hint

SELECT /*+ PARALLEL(e, 4) */
    department_id,
    COUNT(*)    AS headcount,
    AVG(salary) AS avg_salary
FROM
    hr.employees e
GROUP BY
    department_id
ORDER BY
    avg_salary DESC;

5. Identifying Top SQL from AWR

-- Top 10 SQL statements by elapsed time from the last AWR snapshot
SELECT *
FROM (
    SELECT
        s.sql_id,
        ROUND(s.elapsed_time_delta / 1e6, 2)                                 AS elapsed_secs,
        s.executions_delta                                                     AS executions,
        ROUND(s.elapsed_time_delta / NULLIF(s.executions_delta, 0) / 1e6, 3) AS secs_per_exec,
        SUBSTR(t.sql_text, 1, 100)                                            AS sql_snippet
    FROM
        dba_hist_sqlstat  s
        JOIN dba_hist_sqltext t
            ON s.sql_id = t.sql_id
    WHERE
        s.snap_id = (
            SELECT MAX(snap_id)
            FROM dba_hist_snapshot
        )
    ORDER BY
        s.elapsed_time_delta DESC
)
WHERE ROWNUM <= 10;

6. SQL Tuning Advisor

Oracle’s built-in advisor can automatically suggest improvements for a specific SQL_ID.

-- Step 1: Create and execute the tuning task
DECLARE
    l_task_name VARCHAR2(30);
BEGIN
    l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id      => 'your_sql_id_here',
        scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit  => 60,
        task_name   => 'tune_my_query',
        description => 'Tuning task for slow query'
    );

    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
        task_name => 'tune_my_query'
    );
END;
/

-- Step 2: View the recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_my_query')
FROM dual;

7. Common Tuning Anti-Patterns

Anti-Pattern Problem Fix
SELECT * Fetches unnecessary columns, prevents index-only scans Select only needed columns
Functions on indexed columns in WHERE clause Prevents index usage Use function-based indexes
Implicit data type conversion Causes full table scans Match data types explicitly
OR conditions on different columns Optimizer struggles to use indexes Rewrite as UNION ALL
Missing bind variables Hard parses, high CPU, library cache contention Always use bind variables
Outdated or missing statistics Bad cardinality estimates, wrong join methods Gather stats regularly

Key Takeaways

  • Always check the execution plan first before making any changes.
  • Statistics drive the optimizer — keep them fresh and accurate.
  • Indexes are not always faster — for large result sets a full table scan can be better.
  • Hints are a last resort — fix the root cause first.
  • Use AWR and SQL Tuning Advisor — Oracle gives you powerful built-in tools, use them.