{"id":2998,"date":"2025-03-31T14:12:58","date_gmt":"2025-03-31T14:12:58","guid":{"rendered":"https:\/\/w3buddy.com\/?p=2998"},"modified":"2026-01-15T13:10:27","modified_gmt":"2026-01-15T07:40:27","slug":"how-to-remove-a-cached-sql-execution-plan-in-oracle","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-remove-a-cached-sql-execution-plan-in-oracle\/","title":{"rendered":"How to Remove a Cached SQL Execution Plan in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">A <strong>cursor cache<\/strong> is a stored execution plan of a SQL statement in memory. Oracle reuses it to improve performance. However, sometimes we need to clear the cache to apply a new execution plan.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Steps to Clear a Cursor Cache<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To clear a specific SQL statement from the cache, follow these steps:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Get the Address and Hash Value<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Run the following SQL query to find the <strong>ADDRESS<\/strong> and <strong>HASH_VALUE<\/strong> of the SQL statement:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT inst_id, address, hash_value, users_executing \nFROM gv$sqlarea \nWHERE sql_id = '9x12bcd34efgh' \nORDER BY 1;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example output:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">INST_ID   ADDRESS            HASH_VALUE    USERS_EXECUTING\n1         08000200AA7CDE12   3456789012    0\n2         08000200BB8F1234   3456789012    0<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2. Generate the Purge Commands<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Use the following SQL command to generate the <strong>DBMS_SHARED_POOL.PURGE<\/strong> commands:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT inst_id, \n       'exec dbms_shared_pool.purge(''' || address || ',' || hash_value || ''',''C'');' stmt \nFROM gv$sqlarea \nWHERE sql_id = '9x12bcd34efgh' \nORDER BY 1;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example output:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">INST_ID   STMT\n1         exec dbms_shared_pool.purge('08000200AA7CDE12,3456789012','C');\n2         exec dbms_shared_pool.purge('08000200BB8F1234,3456789012','C');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. Execute the Purge Commands<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Run the generated purge commands for each instance:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>For Node 1:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">EXEC dbms_shared_pool.purge('08000200AA7CDE12,3456789012','C');<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>For Node 2:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">EXEC dbms_shared_pool.purge('08000200BB8F1234,3456789012','C');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Expected Result<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If successful, the SQL execution plan will be removed from the shared pool. You can verify by checking the <strong>gv$sqlarea <\/strong>view again to ensure the SQL statement is no longer in cache.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What If Purging Fails?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If DBMS_SHARED_POOL.PURGE does not work as expected, try the following:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Flush the shared pool<\/strong>:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ALTER SYSTEM FLUSH SHARED_POOL;<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Modify the execution plan** by using hints or restructuring the query.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">By following these steps, you can ensure your SQL statements use the latest execution plan<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A cursor cache is a stored execution plan of a SQL statement in memory. Oracle reuses it to improve performance. However, sometimes we need to clear the cache to apply a new execution plan. Steps to Clear a Cursor Cache To clear a specific SQL statement from the cache, follow these steps: 1. Get the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-2998","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2998","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=2998"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2998\/revisions"}],"predecessor-version":[{"id":3002,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2998\/revisions\/3002"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}