在Oracle里第一次執(zhí)行一條SQL語句后，該SQL語句會被硬解析，而且執(zhí)行計劃和解析樹會被緩存到Shared Pool里。方便以后再次執(zhí)行這條SQL語句時不需要再做硬解析，方便應用系統(tǒng)的擴展。但是如果該SQL對應的表數(shù)據(jù)量突變或其他原因，Shared Pool里緩存的執(zhí)行計劃和解析樹已經(jīng)不再適用于現(xiàn)在的情況，SQL執(zhí)行效率急速下降，這種情況下就需要把該SQL緩存在Shared Pool里的執(zhí)行計劃和解析樹清理出去，以便對該SQL重新做硬解析，生成新的執(zhí)行計劃和解析樹。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供陸豐網(wǎng)站建設、陸豐做網(wǎng)站、陸豐網(wǎng)站設計、陸豐網(wǎng)站制作等企業(yè)網(wǎng)站建設、網(wǎng)頁設計與制作、陸豐企業(yè)網(wǎng)站模板建站服務，十載陸豐做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡服務。
從Shared Pool刪除SQL緩存的執(zhí)行計劃有三種方法：
alter system flush shared_pool;
對表做DDL操作
dbms_shared_pool.purge包(10.2.0.4及其以上)
上面三種方法的影響范圍依次遞減，下面分別用實例做演示
創(chuàng)建測試表
zx@MYDB>create table s1 as select * from dba_objects; Table created. zx@MYDB>create table s2 as select * from dba_objects; Table created.
1、alter system flush shared_pool;
這條命令是清除Shared Pool里緩存的所有數(shù)據(jù)，自然可以刪除想要刪除的SQL對就的執(zhí)行計劃，但負作用是它把Shared Pool里的所有數(shù)據(jù)都清除了，影響太大。生產(chǎn)系統(tǒng)一定要謹慎使用這個命令。
執(zhí)行兩個查詢，并查看在Shared Pool里的緩存
zx@MYDB>select object_name from s1 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>select object_name from s2 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>col sql_text for a80 zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 1 select object_name from s2 where object_id=20 a6gw4ht2unxun 1 1 zx@MYDB>select object_name from s1 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 2 select object_name from s2 where object_id=20 a6gw4ht2unxun 1 1
上面查詢分別對表s1和表s2做查詢，從輸出可以看出上面執(zhí)行的兩個SQL的執(zhí)行計劃和解析樹被緩存到了Shared Pool中，再次執(zhí)行時會直接用緩存的執(zhí)行計劃(EXECUTIONS變?yōu)?)?，F(xiàn)在想刪除表s1對應SQL的執(zhí)行計劃，執(zhí)行alter system flush shared_pool;
zx@MYDB>alter system flush shared_pool; System altered. zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%'; no rows selected
從上面查詢結果可以看出命令確實刪除了s1對應SQL的執(zhí)行計劃，但同時也把表s2對應的SQL的執(zhí)行計劃也刪除了，傷及了無辜。
2、對表做DDL操作
一旦對某個表執(zhí)行了DDL操作，庫緩存中所有在SQL文本中包含了這個表的Shared Cursor都會被Oracle標記為失效(invalid)，這意味著這些Shared Cursor中存儲的解析樹和執(zhí)行計劃將不再能被重用，所以當Oracle再次執(zhí)行與這個表相關的SQL時就會使用硬解析。但這種方法的弊端在于其影響范圍還是太廣，因為一旦對某個表執(zhí)行了DDL操作，再次執(zhí)行與這個表相關的所有SQL時就會全部使用硬解析。這是很不好的，特別是對于OLTP類型的應用系統(tǒng)而言，因為這可能會導致短時間內(nèi)的硬解析數(shù)量劇增，進而影響系統(tǒng)的性能。
zx@MYDB>select object_name from s1 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>select object_name from s1 where object_id=30; OBJECT_NAME ------------------------------ I_COBJ# zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 1 select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1 zx@MYDB>select object_name from s1 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 2 select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1
上面查詢對表s1做了兩個不同的查詢，從輸出可以看出上面執(zhí)行的兩個SQL的執(zhí)行計劃和解析樹被緩存到了Shared Pool中，再次執(zhí)行時會直接用緩存的執(zhí)行計劃(EXECUTIONS變?yōu)?)?，F(xiàn)在要刪除object_id=20對應SQL的執(zhí)行計劃，這里選擇對表添加注釋(COMMENT)，它也是DDL操作。
zx@MYDB>comment on table s1 is 'test shared cursor'; Comment created. zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS OBJECT_STATUS -------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- --------------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 2 INVALID_UNAUTH select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1 INVALID_UNAUTH zx@MYDB>select object_name from s1 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS OBJECT_STATUS -------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- --------------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 1 VALID select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1 INVALID_UNAUTH
從上面的輸出可以看出，對表s1做DDL操作后緩存在Shared Pool里的執(zhí)行計劃沒有被清除，但是兩個SQL對應的執(zhí)行計劃狀態(tài)都變?yōu)榱恕癐NVALID_UNAUTH”，當再次執(zhí)行SQL時會做硬解析，重新緩存解析樹和執(zhí)行計劃。
3、dbms_shared_pool.purge包
它是從Oracle 10.2.0.4開始引入的一種方法，它可以用來刪除指定的緩存在庫緩存中的Shared Cursor，其影響范圍公限于目標SQL所對應的Shared Cursor，也就是說它可以做到讓Oracle在執(zhí)行目標SQL時使用硬解析，在執(zhí)行其他所有SQL時都和原來一樣保持不變。
zx@MYDB>alter system flush shared_pool; System altered. zx@MYDB>select object_name from s1 where object_id=20; OBJECT_NAME ------------------------------ ICOL$ zx@MYDB>select object_name from s1 where object_id=30; OBJECT_NAME ------------------------------ I_COBJ# zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea where sql_text like 'select object_name from s%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS OBJECT_STATUS ADDRESS HASH_VALUE ------------------------------------------------------------ --------------------------------------- ------------- ---------- --------------- ---------------- ---------- select object_name from s1 where object_id=20 1s45nwjtws2tj 1 1 VALID 00000000B4F85A18 1942752049 select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1 VALID 00000000BE7E56C8 1637183192
現(xiàn)在要刪除object_id=20對應的SQL緩存的執(zhí)行計劃和解析樹。
重慶分公司
重慶分公司
zx@MYDB>exec sys.dbms_shared_pool.purge('00000000B4F85A18,1942752049','C');
PL/SQL procedure successfully completed.
zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea where sql_text like 'select object_name from s%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS OBJECT_STATUS ADDRESS HASH_VALUE
------------------------------------------------------------ --------------------------------------- ------------- ---------- --------------- ---------------- ----------
select object_name from s1 where object_id=30 1hdyqyxhtavqs 1 1 VALID 00000000BE7E56C8 1637183192從輸出可以看出object_id=20對應的SQL緩存的執(zhí)行計劃和解析樹被刪除了，而object_id=30對應的SQL的執(zhí)行計劃沒有受影響。
需要注意的是，如果在10.2.0.4中使用dbms_shared_pool.purge，則在使用之前必須特工設置event 5614566(alter session set events '5614566 trace name context forever')，否則dbms_shared_pool.purge將不起作用，這個限制在10.2.0.4以上的版本中已經(jīng)不存在了。如果默認沒有安裝dbms_shared_pool包的可以執(zhí)行@?/rdbms/admin/dbmspool.sql
參考《基于Oracle的SQL優(yōu)化》
官方文檔：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_shared_pool.htm#ARPLS68077
