SQL Tuning in Oracle Database – Step-by-Step Guide

1. Introduction

SQL tuning is a critical activity in Oracle databases to improve query performance and optimize resource usage. This document outlines a structured approach to identify, analyze, and optimize a slow-performing SQL query.

---

2. Prerequisites

Before performing SQL tuning, ensure the following:

• Oracle Enterprise Database is available

• Oracle Diagnostics Pack and Tuning Pack licenses are enabled

• SQLTXPLAIN (SQLT) utility is installed

  • COE scripts are located at:
    …/sqlt/install/coe

---

3. Scenario

A SELECT query is executed on a table with approximately 5 million rows, resulting in:

• High memory consumption

• Slow response time

---

4. Step-by-Step SQL Tuning Process

Step 1: Capture SQL ID

Identify the SQL ID of the running query:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    q.sql_fulltext,
    s.sql_child_number,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.machine,
    s.program
FROM
    v$session s
    JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
    s.status = 'ACTIVE'
    AND s.sql_id IS NOT NULL
    AND s.username IS NOT NULL      
ORDER BY
    s.last_call_et DESC;

---

Step 2: Check Execution Plan

Analyze the current execution plan:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

Details:

• Displays actual execution statistics

• Helps identify bottlenecks like full table scans or high I/O

---

Step 3: Analyze SQL Execution History

Retrieve historical performance data:

SELECT
       ss.snap_id,
       ss.instance_number AS node,
       ss.begin_interval_time,
       s.sql_id,
       t.sql_text,
       s.plan_hash_value,
       NVL(s.executions_delta,0) AS execs,
       (s.elapsed_time_delta /
            DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta)) / 1000000
           AS avg_etime,
       (s.buffer_gets_delta /
            DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta))
           AS avg_lio
FROM   dba_hist_sqlstat s
       JOIN dba_hist_snapshot ss
           ON ss.snap_id = s.snap_id
          AND ss.instance_number = s.instance_number
       JOIN dba_hist_sqltext t
           ON t.sql_id = s.sql_id
WHERE  s.sql_id = '<SQL_ID>'
       AND s.executions_delta > 0
ORDER BY
       ss.begin_interval_time,
       ss.instance_number;

Purpose:

• Detect plan changes over time

• Compare performance across executions

---

Step 4: Identify and Apply Best Execution Plan (COE Script)

4.1 Identify Best Plan

START coe_xfr_sql_profile.sql <SQL_ID>

Sample output:

PLAN_HASH_VALUE      AVG_ET_SECS
------------------   ------------
4009583451            12.438
9123498812             0.210   <-- Best Plan
3009112234             5.901

---

4.2 Generate SQL Profile

START coe_xfr_sql_profile.sql <SQL_ID> <PLAN_HASH_VALUE>

This creates a script file containing:

• SQL text

• Execution hints

• SQL profile creation command

---

4.3 Apply SQL Profile

@coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH_VALUE>.sql

---

4.4 Verify Plan Usage

SELECT sql_id, plan_hash_value
FROM   v$sql
WHERE  sql_id = '<SQL_ID>';

---

Drop SQL Profile (if required)

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_<SQL_ID>_<PLAN_HASH_VALUE>');

---

Step 5: Use SQL Tuning Advisor

If no optimal plan is found:

BEGIN
    DBMS_SQLTUNE.create_tuning_task(
        task_name   => '<TASK_NAME>',
        sql_id      => '<SQL_ID>',
        time_limit  => <TIME_LIMIT>
    );

    DBMS_SQLTUNE.execute_tuning_task('<TASK_NAME>');
END;
/

---

View Tuning Report

SELECT DBMS_SQLTUNE.report_tuning_task('<TASK_NAME>')
FROM dual;

---

5. Common Recommendations

1. Create Index

CREATE INDEX EMP_DEPT_IDX 
ON EMPLOYEES(DEPARTMENT_ID, SALARY);

---

2. Accept SQL Profile

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
     task_name => '<TASK_NAME>',
     name      => '<PROFILE_NAME>');

---

3. Gather Table Statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS(
     ownname => 'HR',
     tabname => 'EMPLOYEES',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

---

4. Verify SQL Profile

SELECT * 
FROM   dba_sql_profiles
WHERE  name LIKE '%<SQL_ID>%';

---

6. Best Practices

• Always validate performance after applying changes

• Monitor execution plans regularly

• Avoid overusing SQL Profiles

• Keep optimizer statistics up to date

• Prefer indexing and query rewriting for long-term solutions

---

7. Conclusion

SQL tuning in Oracle involves:

1. Identifying the problematic SQL

2. Analyzing execution plans

3. Reviewing historical performance

4. Applying optimal execution plans

5. Using SQL Tuning Advisor recommendations

A systematic approach ensures improved performance and efficient resource utilization.

---