Oracle刪除一條SQL在Shared Pool里緩存的執(zhí)行計(jì)劃的三種方法

在Oracle里第一次執(zhí)行一條SQL語句后，該SQL語句會(huì)被硬解析，而且執(zhí)行計(jì)劃和解析樹會(huì)被緩存到Shared Pool里。方便以后再次執(zhí)行這條SQL語句時(shí)不需要再做硬解析，方便應(yīng)用系統(tǒng)的擴(kuò)展。但是如果該SQL對(duì)應(yīng)的表數(shù)據(jù)量突變或其他原因，Shared Pool里緩存的執(zhí)行計(jì)劃和解析樹已經(jīng)不再適用于現(xiàn)在的情況，SQL執(zhí)行效率急速下降，這種情況下就需要把該SQL緩存在Shared Pool里的執(zhí)行計(jì)劃和解析樹清理出去，以便對(duì)該SQL重新做硬解析，生成新的執(zhí)行計(jì)劃和解析樹。

從Shared Pool刪除SQL緩存的執(zhí)行計(jì)劃有三種方法：

• alter system flush shared_pool;

• 對(duì)表做DDL操作

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

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

創(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ù)，自然可以刪除想要?jiǎng)h除的SQL對(duì)就的執(zhí)行計(jì)劃，但負(fù)作用是它把Shared Pool里的所有數(shù)據(jù)都清除了，影響太大。生產(chǎn)系統(tǒng)一定要謹(jǐn)慎使用這個(gè)命令。

執(zhí)行兩個(gè)查詢，并查看在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

上面查詢分別對(duì)表s1和表s2做查詢，從輸出可以看出上面執(zhí)行的兩個(gè)SQL的執(zhí)行計(jì)劃和解析樹被緩存到了Shared Pool中，再次執(zhí)行時(shí)會(huì)直接用緩存的執(zhí)行計(jì)劃(EXECUTIONS變?yōu)?)。現(xiàn)在想刪除表s1對(duì)應(yīng)SQL的執(zhí)行計(jì)劃，執(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

從上面查詢結(jié)果可以看出命令確實(shí)刪除了s1對(duì)應(yīng)SQL的執(zhí)行計(jì)劃，但同時(shí)也把表s2對(duì)應(yīng)的SQL的執(zhí)行計(jì)劃也刪除了，傷及了無辜。

2、對(duì)表做DDL操作

一旦對(duì)某個(gè)表執(zhí)行了DDL操作，庫緩存中所有在SQL文本中包含了這個(gè)表的Shared Cursor都會(huì)被Oracle標(biāo)記為失效(invalid)，這意味著這些Shared Cursor中存儲(chǔ)的解析樹和執(zhí)行計(jì)劃將不再能被重用，所以當(dāng)Oracle再次執(zhí)行與這個(gè)表相關(guān)的SQL時(shí)就會(huì)使用硬解析。但這種方法的弊端在于其影響范圍還是太廣，因?yàn)橐坏?duì)某個(gè)表執(zhí)行了DDL操作，再次執(zhí)行與這個(gè)表相關(guān)的所有SQL時(shí)就會(huì)全部使用硬解析。這是很不好的，特別是對(duì)于OLTP類型的應(yīng)用系統(tǒng)而言，因?yàn)檫@可能會(huì)導(dǎo)致短時(shí)間內(nèi)的硬解析數(shù)量劇增，進(jìn)而影響系統(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

上面查詢對(duì)表s1做了兩個(gè)不同的查詢，從輸出可以看出上面執(zhí)行的兩個(gè)SQL的執(zhí)行計(jì)劃和解析樹被緩存到了Shared Pool中，再次執(zhí)行時(shí)會(huì)直接用緩存的執(zhí)行計(jì)劃(EXECUTIONS變?yōu)?)?，F(xiàn)在要?jiǎng)h除object_id=20對(duì)應(yīng)SQL的執(zhí)行計(jì)劃，這里選擇對(duì)表添加注釋(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

從上面的輸出可以看出，對(duì)表s1做DDL操作后緩存在Shared Pool里的執(zhí)行計(jì)劃沒有被清除，但是兩個(gè)SQL對(duì)應(yīng)的執(zhí)行計(jì)劃狀態(tài)都變?yōu)榱恕癐NVALID_UNAUTH”，當(dāng)再次執(zhí)行SQL時(shí)會(huì)做硬解析，重新緩存解析樹和執(zhí)行計(jì)劃。

3、dbms_shared_pool.purge包

它是從Oracle 10.2.0.4開始引入的一種方法，它可以用來刪除指定的緩存在庫緩存中的Shared Cursor，其影響范圍公限于目標(biāo)SQL所對(duì)應(yīng)的Shared Cursor，也就是說它可以做到讓Oracle在執(zhí)行目標(biāo)SQL時(shí)使用硬解析，在執(zhí)行其他所有SQL時(shí)都和原來一樣保持不變。

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)在要?jiǎng)h除object_id=20對(duì)應(yīng)的SQL緩存的執(zhí)行計(jì)劃和解析樹。

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對(duì)應(yīng)的SQL緩存的執(zhí)行計(jì)劃和解析樹被刪除了，而object_id=30對(duì)應(yīng)的SQL的執(zhí)行計(jì)劃沒有受影響。

需要注意的是，如果在10.2.0.4中使用dbms_shared_pool.purge，則在使用之前必須特工設(shè)置event 5614566(alter session set events '5614566 trace name context forever')，否則dbms_shared_pool.purge將不起作用，這個(gè)限制在10.2.0.4以上的版本中已經(jīng)不存在了。如果默認(rèn)沒有安裝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