SQL Query Tuning Guide

1. Check for Waiting Events in v$session

SELECT SID, SERIAL#, STATUS, EVENT, INST_ID 
FROM GV$SESSION 
WHERE WAIT_CLASS <> 'Idle';

---

2. Check SQL ID Which is Waiting for Resources

SELECT SID, SERIAL#, STATUS, EVENT, INST_ID, SQL_ID, ROW_WAIT_OBJ# 
FROM GV$SESSION 
WHERE WAIT_CLASS <> 'Idle';

---

3. Check When Execution Plan of SQL ID Changed

SET lines 255 pages 1000
COL execs FOR 999,999,999
COL avg_etime FOR 999,999.999
COL avg_lio FOR 999,999,999.9
COL begin_interval_time FOR a25
COL node FOR 99999
COL PLAN_HASH_VALUE FOR 999999999999999
COLUMN MODULE FOR a18 TRUNC
BREAK ON plan_hash_value ON startup_time SKIP 1

SELECT 
    ss.snap_id, 
    ss.instance_number inst_id, 
    begin_interval_time, 
    sql_id, 
    plan_hash_value,
    NVL(executions_delta, 0) execs,
    (elapsed_time_delta / DECODE(NVL(executions_delta, 0), 0, 1, executions_delta)) / 1000000 avg_etime,
    (buffer_gets_delta / DECODE(NVL(executions_delta, 0), 0, 1, executions_delta)) avg_lio, 
    module,
    rows_processed_delta rws
FROM 
    DBA_HIST_SQLSTAT S, 
    DBA_HIST_SNAPSHOT SS
WHERE 
    sql_id = NVL('&sql_id', '4dqs2k5tynk61')
    AND ss.snap_id = S.snap_id
    AND ss.instance_number = S.instance_number
    AND executions_delta > 0
ORDER BY 1;

---

4. Check the Explain Plan for the Query

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID', FORMAT => 'ADVANCED'));

---

5. Run sqltrpt.sql (SQL Tuning Report)

@$ORACLE_HOME\rdbms\admin\sqltrpt.sql

---

6. Get Recommendations from Oracle

SELECT dbms_sqltune.report_tuning_task(:task_name) 
FROM dual 
WHERE :err <> 1;

---

7. Accept SQL Profile Based on Recommendations

EXECUTE dbms_sqltune.accept_sql_profile(
    task_name => 'TASK_6137',
    task_owner => 'SYS',
    replace => TRUE
);

---

8. Check Why Execution Plan Changed

SELECT * 
FROM v$sql_shared_cursor 
WHERE sql_id = '42hzrp8y7c22g';

Comments