Oracle刪除一條SQL在Shared 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及其以上)

上面三種方法的影響范圍依次遞減，下面分別用實例做演示

創建測試表

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里緩存的所有數據，自然可以刪除想要刪除的SQL對就的執行計劃，但負作用是它把Shared Pool里的所有數據都清除了，影響太大。生產系統一定要謹慎使用這個命令。

執行兩個查詢，并查看在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做查詢，從輸出可以看出上面執行的兩個SQL的執行計劃和解析樹被緩存到了Shared Pool中，再次執行時會直接用緩存的執行計劃(EXECUTIONS變為2)。現在想刪除表s1對應SQL的執行計劃，執行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的執行計劃，但同時也把表s2對應的SQL的執行計劃也刪除了，傷及了無辜。

2、對表做DDL操作

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

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做了兩個不同的查詢，從輸出可以看出上面執行的兩個SQL的執行計劃和解析樹被緩存到了Shared Pool中，再次執行時會直接用緩存的執行計劃(EXECUTIONS變為2)。現在要刪除object_id=20對應SQL的執行計劃，這里選擇對表添加注釋(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里的執行計劃沒有被清除，但是兩個SQL對應的執行計劃狀態都變為了“INVALID_UNAUTH”，當再次執行SQL時會做硬解析，重新緩存解析樹和執行計劃。

3、dbms_shared_pool.purge包

它是從Oracle 10.2.0.4開始引入的一種方法，它可以用來刪除指定的緩存在庫緩存中的Shared Cursor，其影響范圍公限于目標SQL所對應的Shared Cursor，也就是說它可以做到讓Oracle在執行目標SQL時使用硬解析，在執行其他所有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

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

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緩存的執行計劃和解析樹被刪除了，而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