Purge SQL Plan from Shared Pool in Oracle

W3Buddy

June 18, 2025 · 7 min read · 0

Add W3Buddy as a preferred source on Google

Article Summary

Safely purge bad SQL execution plans from Oracle’s shared pool. Includes step-by-step scripts, checks, and best practices.

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

Scenario	Action
SQL using bad plan in memory	Purge with `DBMS_SHARED_POOL.PURGE()`
SQL ID has multiple cursors	Loop purge for all cursors
Active sessions exist	Kill or request session pause
Need permanent fix	Use SQL Plan Baselines instead

Was this helpful?

Written by

W3buddy

Explore W3Buddy for in-depth guides, breaking tech news, and expert analysis on AI, cybersecurity, databases, web development, and emerging technologies.