{"id":4304,"date":"2025-06-18T21:53:55","date_gmt":"2025-06-18T21:53:55","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4304"},"modified":"2025-06-18T21:58:35","modified_gmt":"2025-06-18T21:58:35","slug":"purge-sql-plan-from-shared-pool-in-oracle","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/performance-tuning\/purge-sql-plan-from-shared-pool-in-oracle\/","title":{"rendered":"Purge SQL Plan from Shared Pool in Oracle"},"content":{"rendered":"\n

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

Purging is a temporary memory cleanup step<\/strong> \u2014 it does not delete the SQL from disk<\/strong>, nor does it prevent bad plans from returning unless further actions (like creating a baseline) are taken.<\/p>\n\n\n\n

When Should You Purge?<\/h2>\n\n\n\n

Use plan purging when:<\/p>\n\n\n\n

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

    \u26a0\ufe0f Do NOT purge casually.<\/strong>
    It only clears memory \u2014 it won\u2019t prevent Oracle from loading the same bad plan again unless you take action (e.g., use baselines or hints).<\/p>\n\n\n\n

    Important Pre-Check<\/h2>\n\n\n\n

    Before purging:<\/p>\n\n\n\n

      \n
    • \u2705 Make sure no sessions are actively using the SQL ID<\/strong>.<\/li>\n\n\n\n
    • \ud83d\udcac Ask the application team to pause usage<\/strong> or approve session termination.<\/li>\n<\/ul>\n\n\n\n
      SELECT sid, serial#, sql_id, status, username\nFROM v$session\nWHERE sql_id = 'f14nzf4gyq4y4';  -- \ud83d\udd04 Replace with your SQL_ID<\/code><\/pre>\n\n\n\n
      Step 1: Check SQL Execution History<\/h2>\n\n\n\n
      This step helps you analyze how a SQL ID has performed over time \u2014 across different plans. You can identify which plan hash was stable and how recent executions behaved.<\/p>\n\n\n\n
      CLEAR COLUMNS BREAKS COMPUTES\nSET LINESIZE 200\nSET PAGESIZE 1000\n\nCOLUMN sql_id          FORMAT A15        HEADING \"SQL|ID\"\nCOLUMN s_time          FORMAT A12        HEADING \"Snap|Time\"\nCOLUMN executions      FORMAT 999999999  HEADING \"Execs\"\nCOLUMN elapsed_t       FORMAT 99999999.99 HEADING \"Elapsed|Secs\"\nCOLUMN cpu_t           FORMAT 99999999.99 HEADING \"CPU|Secs\"\nCOLUMN user_io         FORMAT 99999999.99 HEADING \"User I\/O|Secs\"\nCOLUMN buffer_gets     FORMAT 999999999  HEADING \"Buffer|Gets\"\nCOLUMN disk_r          FORMAT 999999999  HEADING \"Disk|Reads\"\nCOLUMN rows_processed  FORMAT 999999999  HEADING \"Rows|Processed\"\nCOLUMN sec_per_exec    FORMAT 99999999.9999 HEADING \"Seconds|per Exec\"\nCOLUMN plan_hash_value FORMAT A15        HEADING \"Plan|Hash\"\nBREAK ON sql_id ON plan_hash_value SKIP 1\n\nACCEPT 1_sqlid PROMPT 'Enter SQL ID: '\nACCEPT 1_days DEFAULT 42 PROMPT 'Enter days in the past (default 42): '\n\nSELECT \n    sql_id,\n    TO_CHAR(plan_hash_value) plan_hash_value,\n    TO_CHAR(begin_interval_time, 'DD-MON-YYYY') s_time,\n    SUM(executions_delta) executions,\n    ROUND(SUM(elapsed_time_delta) \/ 1e6, 2) elapsed_t,\n    ROUND(SUM(cpu_time_delta) \/ 1e6, 2) cpu_t,\n    ROUND(SUM(iowait_delta) \/ 1e6, 2) user_io,\n    ROUND(SUM(buffer_gets_delta)) buffer_gets,\n    ROUND(SUM(disk_reads_delta)) disk_r,\n    ROUND(SUM(rows_processed_delta)) rows_processed,\n    DECODE(SUM(executions_delta), 0, ROUND(SUM(elapsed_time_delta) \/ 1e6, 2),\n        ROUND(SUM(elapsed_time_delta)\/1e6\/SUM(executions_delta), 4)) sec_per_exec\nFROM \n    dba_hist_sqlstat a, dba_hist_snapshot b\nWHERE \n    a.snap_id = b.snap_id\n    AND a.instance_number = b.instance_number\n    AND a.sql_id = '&&1_sqlid'\n    AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE - &&1_days)\nGROUP BY \n    sql_id, plan_hash_value, TO_CHAR(begin_interval_time, 'DD-MON-YYYY')\nORDER BY \n    TO_DATE(TO_CHAR(begin_interval_time, 'DD-MON-YYYY'), 'DD-MON-YYYY'),\n    plan_hash_value;<\/code><\/pre>\n\n\n\n
      \ud83d\udcdd Sample Output <\/h3>\n\n\n\n
      SQL|ID         Plan|Hash     Snap|Time   Execs  Elapsed|Secs  CPU|Secs  User I\/O|Secs  Buffer|Gets  Disk|Reads  Rows|Processed  Seconds|per Exec\n------------- ------------- ---------- ------ ------------- ----------- -------------- ------------ ------------ ---------------- ------------------\nf14nzf4gyq4y4  2881287423   16-JUN-25     150        220.50     150.10         45.20      890000      26000          100000               1.4700\nf14nzf4gyq4y4  1734569820   15-JUN-25      45         90.80      65.40         18.70      310000       9000           32000               2.0178<\/code><\/pre>\n\n\n\n
      \u2705 What to Check in Output:<\/h3>\n\n\n\n
        \n
      • Check how many distinct Plan Hash Values<\/strong> (PLAN_HASH_VALUE<\/code>) exist for the SQL_ID.\n
          \n
        • If there is only one<\/strong>, you can proceed with a targeted purge more confidently.<\/li>\n\n\n\n
        • If there are multiple<\/strong>, identify which one is bad based on:\n
            \n
          • High elapsed time<\/strong><\/li>\n\n\n\n
          • High buffer gets\/disk reads<\/strong><\/li>\n\n\n\n
          • Low rows processed<\/strong><\/li>\n\n\n\n
          • Very high seconds per execution<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n
          • Confirm recency<\/strong> of bad plans (check Snap|Time<\/code>).<\/li>\n\n\n\n
          • If multiple plans were used recently, a purge may not help<\/strong>, or may even cause Oracle to reload the same bad plan<\/strong>.<\/li>\n<\/ul>\n\n\n\n
            \n
            \ud83d\udca1 If the SQL frequently switches between plans, it’s better to:<\/p>\n\n\n\n