Oracle刪除一條SQL在Shared Pool裡緩衝的執行計畫的三種方法

標籤：shared   oralce   pool   

在Oracle裡第一次執行一條SQL語句後，該SQL語句會被硬解析，而且執行計畫和解析樹會被緩衝到Shared Pool裡。方便以後再次執行這條SQL語句時不需要再做硬解析，方便應用系統的擴充。但是如果該SQL對應的表資料量突變或其他原因，Shared Pool裡緩衝的執行計畫和解析樹已經不再適用於現在的情況，SQL執行效率急速下降，這種情況下就需要把該SQL緩衝在Shared Pool裡的執行計畫和解析樹清理出去，以便對該SQL重新做硬解析，產生新的執行計畫和解析樹。

從Shared Pool刪除SQL緩衝的執行計畫有三種方法：

• alter system flush shared_pool;

• 對錶做DDL操作

• dbms_shared_pool.purge包(10.2.0.4及其以上)

上面三種方法的影響範圍依次遞減，下面分別用執行個體做示範

建立測試表

[email protected]>create table s1 as select * from dba_objects;Table created.[email protected]>create table s2 as select * from dba_objects;Table created.

1、alter system flush shared_pool;

這條命令是清除Shared Pool裡緩衝的所有資料，自然可以刪除想要刪除的SQL對就的執行計畫，但負作用是它把Shared Pool裡的所有資料都清除了，影響太大。生產系統一定要謹慎使用這個命令。

執行兩個查詢，並查看在Shared Pool裡的緩衝

[email protected]>select object_name from s1 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>select object_name from s2 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>col sql_text for a80[email protected]>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     11select object_name from s2 where object_id=20 a6gw4ht2unxun     11[email protected]>select object_name from s1 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>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     12select object_name from s2 where object_id=20 a6gw4ht2unxun     11

上面查詢分別對錶s1和表s2做查詢，從輸出可以看出上面執行的兩個SQL的執行計畫和解析樹被緩衝到了Shared Pool中，再次執行時會直接用緩衝的執行計畫(EXECUTIONS變為2)。現在想刪除表s1對應SQL的執行計畫，執行alter system flush shared_pool;

[email protected]>alter system flush shared_pool;System altered.[email protected]>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的執行計畫，但同時也把表s2對應的SQL的執行計畫也刪除了，傷及了無辜。

2、對錶做DDL操作

一旦對某個表執行了DDL操作，庫緩衝中所有在SQL文本中包含了這個表的Shared Cursor都會被Oracle標記為失效(invalid)，這意味著這些Shared Cursor中儲存的解析樹和執行計畫將不再能被重用，所以當Oracle再次執行與這個表相關的SQL時就會使用硬解析。但這種方法的弊端在於其影響範圍還是太廣，因為一旦對某個表執行了DDL操作，再次執行與這個表相關的所有SQL時就會全部使用硬解析。這是很不好的，特別是對於OLTP類型的應用系統而言，因為這可能會導致短時間內的硬解析數量劇增，進而影響系統的效能。

[email protected]>select object_name from s1 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>select object_name from s1 where object_id=30;OBJECT_NAME------------------------------I_COBJ#[email protected]>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     11select object_name from s1 where object_id=30 1hdyqyxhtavqs     11[email protected]>select object_name from s1 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>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     12select object_name from s1 where object_id=30 1hdyqyxhtavqs     11

上面查詢對錶s1做了兩個不同的查詢，從輸出可以看出上面執行的兩個SQL的執行計畫和解析樹被緩衝到了Shared Pool中，再次執行時會直接用緩衝的執行計畫(EXECUTIONS變為2)。現在要刪除object_id=20對應SQL的執行計畫，這裡選擇對錶添加註釋(COMMENT)，它也是DDL操作。

[email protected]>comment on table s1 is ‘test shared cursor‘;Comment created.[email protected]>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     12 INVALID_UNAUTHselect object_name from s1 where object_id=30 1hdyqyxhtavqs     11 INVALID_UNAUTH[email protected]>select object_name from s1 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>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     11 VALIDselect object_name from s1 where object_id=30 1hdyqyxhtavqs     11 INVALID_UNAUTH

從上面的輸出可以看出，對錶s1做DDL操作後緩衝在Shared Pool裡的執行計畫沒有被清除，但是兩個SQL對應的執行計畫狀態都變為了“INVALID_UNAUTH”，當再次執行SQL時會做硬解析，重新緩衝解析樹和執行計畫。

3、dbms_shared_pool.purge包

它是從Oracle 10.2.0.4開始引入的一種方法，它可以用來刪除指定的緩衝在庫緩衝中的Shared Cursor，其影響範圍公限於目標SQL所對應的Shared Cursor，也就是說它可以做到讓Oracle在執行目標SQL時使用硬解析，在執行其他所有SQL時都和原來一樣保持不變。

[email protected]>alter system flush shared_pool;System altered.[email protected]>select object_name from s1 where object_id=20;OBJECT_NAME------------------------------ICOL$[email protected]>select object_name from s1 where object_id=30;OBJECT_NAME------------------------------I_COBJ#[email protected]>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 1942752049select object_name from s1 where object_id=30     1hdyqyxhtavqs 1    1 VALID      00000000BE7E56C8 1637183192

現在要刪除object_id=20對應的SQL緩衝的執行計畫和解析樹。

[email protected]>exec sys.dbms_shared_pool.purge(‘00000000B4F85A18,1942752049‘,‘C‘);PL/SQL procedure successfully completed.[email protected]>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緩衝的執行計畫和解析樹被刪除了，而object_id=30對應的SQL的執行計畫沒有受影響。

需要注意的是，如果在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以上的版本中已經不存在了。如果預設沒有安裝dbms_shared_pool包的可以執行@?/rdbms/admin/dbmspool.sql

參考《基於Oracle的SQL最佳化》

官方文檔：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_shared_pool.htm#ARPLS68077

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1904641

Oracle刪除一條SQL在Shared Pool裡緩衝的執行計畫的三種方法