Purge SQL Plan from Shared Pool in Oracle

ADVERTISEMENT

Purging a SQL plan means removing the compiled version of a SQL statement from Oracle’s shared pool (memory). This forces Oracle to reparse the SQL the next time it’s executed, which may result in a better execution plan if the current one is inefficient or outdated.

Purging is a temporary memory cleanup step — it does not delete the SQL from disk, nor does it prevent bad plans from returning unless further actions (like creating a baseline) are taken.

When Should You Purge?

Use plan purging when:

  • The SQL is performing poorly, and a bad plan is currently in memory.
  • You’re troubleshooting plan issues and want to clear the in-memory version.
  • You need Oracle to re-optimize the query on next execution.
  • You’re preparing to monitor a fresh parse (e.g., after fixing stats or hinting).

⚠️ Do NOT purge casually.
It only clears memory — it won’t prevent Oracle from loading the same bad plan again unless you take action (e.g., use baselines or hints).

Important Pre-Check

Before purging:

  • ✅ Make sure no sessions are actively using the SQL ID.
  • 💬 Ask the application team to pause usage or approve session termination.
SELECT sid, serial#, sql_id, status, username
FROM v$session
WHERE sql_id = 'f14nzf4gyq4y4';  -- 🔄 Replace with your SQL_ID

Step 1: Check SQL Execution History

This step helps you analyze how a SQL ID has performed over time — across different plans. You can identify which plan hash was stable and how recent executions behaved.

CLEAR COLUMNS BREAKS COMPUTES
SET LINESIZE 200
SET PAGESIZE 1000

COLUMN sql_id          FORMAT A15        HEADING "SQL|ID"
COLUMN s_time          FORMAT A12        HEADING "Snap|Time"
COLUMN executions      FORMAT 999999999  HEADING "Execs"
COLUMN elapsed_t       FORMAT 99999999.99 HEADING "Elapsed|Secs"
COLUMN cpu_t           FORMAT 99999999.99 HEADING "CPU|Secs"
COLUMN user_io         FORMAT 99999999.99 HEADING "User I/O|Secs"
COLUMN buffer_gets     FORMAT 999999999  HEADING "Buffer|Gets"
COLUMN disk_r          FORMAT 999999999  HEADING "Disk|Reads"
COLUMN rows_processed  FORMAT 999999999  HEADING "Rows|Processed"
COLUMN sec_per_exec    FORMAT 99999999.9999 HEADING "Seconds|per Exec"
COLUMN plan_hash_value FORMAT A15        HEADING "Plan|Hash"
BREAK ON sql_id ON plan_hash_value SKIP 1

ACCEPT 1_sqlid PROMPT 'Enter SQL ID: '
ACCEPT 1_days DEFAULT 42 PROMPT 'Enter days in the past (default 42): '

SELECT 
    sql_id,
    TO_CHAR(plan_hash_value) plan_hash_value,
    TO_CHAR(begin_interval_time, 'DD-MON-YYYY') s_time,
    SUM(executions_delta) executions,
    ROUND(SUM(elapsed_time_delta) / 1e6, 2) elapsed_t,
    ROUND(SUM(cpu_time_delta) / 1e6, 2) cpu_t,
    ROUND(SUM(iowait_delta) / 1e6, 2) user_io,
    ROUND(SUM(buffer_gets_delta)) buffer_gets,
    ROUND(SUM(disk_reads_delta)) disk_r,
    ROUND(SUM(rows_processed_delta)) rows_processed,
    DECODE(SUM(executions_delta), 0, ROUND(SUM(elapsed_time_delta) / 1e6, 2),
        ROUND(SUM(elapsed_time_delta)/1e6/SUM(executions_delta), 4)) sec_per_exec
FROM 
    dba_hist_sqlstat a, dba_hist_snapshot b
WHERE 
    a.snap_id = b.snap_id
    AND a.instance_number = b.instance_number
    AND a.sql_id = '&&1_sqlid'
    AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE - &&1_days)
GROUP BY 
    sql_id, plan_hash_value, TO_CHAR(begin_interval_time, 'DD-MON-YYYY')
ORDER BY 
    TO_DATE(TO_CHAR(begin_interval_time, 'DD-MON-YYYY'), 'DD-MON-YYYY'),
    plan_hash_value;

📝 Sample Output

SQL|ID         Plan|Hash     Snap|Time   Execs  Elapsed|Secs  CPU|Secs  User I/O|Secs  Buffer|Gets  Disk|Reads  Rows|Processed  Seconds|per Exec
------------- ------------- ---------- ------ ------------- ----------- -------------- ------------ ------------ ---------------- ------------------
f14nzf4gyq4y4  2881287423   16-JUN-25     150        220.50     150.10         45.20      890000      26000          100000               1.4700
f14nzf4gyq4y4  1734569820   15-JUN-25      45         90.80      65.40         18.70      310000       9000           32000               2.0178

✅ What to Check in Output:

  • Check how many distinct Plan Hash Values (PLAN_HASH_VALUE) exist for the SQL_ID.
    • If there is only one, you can proceed with a targeted purge more confidently.
    • If there are multiple, identify which one is bad based on:
      • High elapsed time
      • High buffer gets/disk reads
      • Low rows processed
      • Very high seconds per execution
  • Confirm recency of bad plans (check Snap|Time).
  • If multiple plans were used recently, a purge may not help, or may even cause Oracle to reload the same bad plan.

💡 If the SQL frequently switches between plans, it’s better to:

  • Fix with a SQL Plan Baseline, or
  • Investigate bind peeking / adaptive plan causes.

Step 2: Get SQL Memory Address & Hash Value

COL ADDRESS FORMAT A20
COL HASH_VALUE FORMAT 999999999
COL PLAN_HASH_VALUE FORMAT 999999999
COL ELAPSED_TIME FORMAT 999999999
COL ROWS_PROCESSED FORMAT 99999999
SELECT 
  ADDRESS, 
  HASH_VALUE, 
  PLAN_HASH_VALUE, 
  ELAPSED_TIME, 
  ROWS_PROCESSED
FROM 
  GV$SQLAREA
WHERE 
  SQL_ID = 'f14nzf4gyq4y4';  -- 🔄 Replace with your SQL_ID

📌 Note down ADDRESS and HASH_VALUE. You will need these in the purge step.

Step 3: Purge from Shared Pool (Memory)

EXEC sys.DBMS_SHARED_POOL.PURGE('&ADDRESS, &HASH_VALUE', 'C');

✅ Replace:

  • &ADDRESS with actual SQL address
  • &HASH_VALUE with actual SQL hash value

'C' indicates it’s a cursor. Use 'P' for packages if applicable.

✅ Step 4: Confirm Purge

Re-run the memory check to ensure SQL is gone:

COL ADDRESS FORMAT A20
COL HASH_VALUE FORMAT 999999999
COL PLAN_HASH_VALUE FORMAT 999999999
COL ELAPSED_TIME FORMAT 999999999
COL ROWS_PROCESSED FORMAT 99999999
SELECT 
  ADDRESS, 
  HASH_VALUE, 
  PLAN_HASH_VALUE, 
  ELAPSED_TIME, 
  ROWS_PROCESSED
FROM 
  GV$SQLAREA
WHERE 
  SQL_ID = 'f14nzf4gyq4y4';

If no rows are returned: ✅ Plan successfully purged from memory.

Step 5: Re-check AWR Performance (Optional)

CLEAR COLUMNS BREAKS COMPUTES
SET LINESIZE 200
SET PAGESIZE 1000

COLUMN sql_id          FORMAT A15        HEADING "SQL|ID"
COLUMN s_time          FORMAT A12        HEADING "Snap|Time"
COLUMN executions      FORMAT 999999999  HEADING "Execs"
COLUMN elapsed_t       FORMAT 99999999.99 HEADING "Elapsed|Secs"
COLUMN cpu_t           FORMAT 99999999.99 HEADING "CPU|Secs"
COLUMN user_io         FORMAT 99999999.99 HEADING "User I/O|Secs"
COLUMN buffer_gets     FORMAT 999999999  HEADING "Buffer|Gets"
COLUMN disk_r          FORMAT 999999999  HEADING "Disk|Reads"
COLUMN rows_processed  FORMAT 999999999  HEADING "Rows|Processed"
COLUMN sec_per_exec    FORMAT 99999999.9999 HEADING "Seconds|per Exec"
COLUMN plan_hash_value FORMAT A15        HEADING "Plan|Hash"
BREAK ON sql_id ON plan_hash_value SKIP 1

ACCEPT 1_sqlid PROMPT 'Enter SQL ID: '
ACCEPT 1_days DEFAULT 42 PROMPT 'Enter days in the past (default 42): '

SELECT 
    sql_id,
    TO_CHAR(plan_hash_value) plan_hash_value,
    TO_CHAR(begin_interval_time, 'DD-MON-YYYY') s_time,
    SUM(executions_delta) executions,
    ROUND(SUM(elapsed_time_delta) / 1e6, 2) elapsed_t,
    ROUND(SUM(cpu_time_delta) / 1e6, 2) cpu_t,
    ROUND(SUM(iowait_delta) / 1e6, 2) user_io,
    ROUND(SUM(buffer_gets_delta)) buffer_gets,
    ROUND(SUM(disk_reads_delta)) disk_r,
    ROUND(SUM(rows_processed_delta)) rows_processed,
    DECODE(SUM(executions_delta), 0, ROUND(SUM(elapsed_time_delta) / 1e6, 2),
        ROUND(SUM(elapsed_time_delta)/1e6/SUM(executions_delta), 4)) sec_per_exec
FROM 
    dba_hist_sqlstat a, dba_hist_snapshot b
WHERE 
    a.snap_id = b.snap_id
    AND a.instance_number = b.instance_number
    AND a.sql_id = '&&1_sqlid'
    AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE - &&1_days)
GROUP BY 
    sql_id, plan_hash_value, TO_CHAR(begin_interval_time, 'DD-MON-YYYY')
ORDER BY 
    TO_DATE(TO_CHAR(begin_interval_time, 'DD-MON-YYYY'), 'DD-MON-YYYY'),
    plan_hash_value;

💡 Additional Purging Scenarios

Purge All Plans for a SQL_ID (if multiple child cursors exist)

SELECT 'EXEC SYS.DBMS_SHARED_POOL.PURGE(''' || address || ', ' || hash_value || ''', ''C'');'
FROM gv$sqlarea
WHERE sql_id = 'f14nzf4gyq4y4';  -- Replace as needed

Run the generated EXEC lines to purge all memory plans.

Kill Sessions Using SQL_ID (if active)

SELECT 
  s.sid, 
  s.serial#, 
  s.username, 
  s.status
FROM 
  gv$session s
JOIN 
  gv$sqlarea a ON s.sql_id = a.sql_id
WHERE 
  a.sql_id = 'f14nzf4gyq4y4';

-- Then kill using:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

📌 Use only with proper approval or coordination with the app team.

📌 Summary

ScenarioAction
SQL using bad plan in memoryPurge with DBMS_SHARED_POOL.PURGE()
SQL ID has multiple cursorsLoop purge for all cursors
Active sessions existKill or request session pause
Need permanent fixUse SQL Plan Baselines instead

ADVERTISEMENT