dbms_shared_pool.purge 清理某個SQL執行計劃

---

---

在日常管理中,經常有讓sql重新解析的需求,比如說使用了bind peeking,第一次繫結特定值的時候執行計劃走的特別糟,因為繫結變數導致之後的語句不作重新解析,重用了最差的執行計劃,這時候我們希望重新解析來得到一個相對好的執行計劃,常見的方法有:

a.alter system flush shared_pool;

b.對語句中的物件做個ddl ; --只會重新生成一個子遊標

c.重新收集統計資訊

但是這些操作的影響都比較大,因此oracle在10.2.0.4後提供了個dbms_shared_pool.purge的方法,能夠將某個sql的shared  cursor從共享池中清除,這樣只會對單個sql產生影響.

 

測試:

 

10.2.0.4:

 

SQL> create table test(id int);

表已建立。

SQL> select * from test;

未選定行

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS

---------------- ---------- ---------- -----------
0000040229F039E0 1689401402          1           1

QL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');

PL/SQL 過程已成功完成。

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000040229F039E0 1689401402          1           1

 

可以看到purge並沒有成功,為了進一步證實,再做一遍查詢

 

SQL> select * from test;

未選定行

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000040229F039E0 1689401402          2           2

 

executions和parse_calls增加,說明前面的parse確實沒生效

 

SQL> alter session set events '5614566 trace name context forever';

會話已更改。

SQL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');

PL/SQL 過程已成功完成。

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

未選定行

 

參考 metalink Doc ID:  751876.1  
10.2.0.4預設不開啟,要靠event 5614566或者補丁5614566來啟用

 

11g:

 

11g>create table test_purge(id int);

 

11g>select * from test_purge;

 

11g>select address,hash_value from v$sql where sql_text like 'select * from test%';

ASH_VALUE
---------
683003671

11g>exec dbms_shared_pool.purge('215E2F78,3683003671','C');

PL/SQL 過程已成功完成。

11g>select address,hash_value from v$sql where sql_text like 'select * from test%';



清除掉shared pool中某條sql語句方法

原文地址：http://www.xifenfei.com/2012/02/%e6%b8%85%e9%99%a4%e6%8e%89shared-pool%e4%b8%ad%e6%9f%90%e6%9d%a1sql%e8%af%ad%e5%8f%a5%e6%96%b9%e6%b3%95.html

在論壇中看到一個帖子，如何清除掉shared pool中某條sql語句，如果是在10g以前的版本，那估計會比較麻煩，為了刪除一條sql語句記錄，需要清空整個shared pool，在10g中提供了新的dbms_shared_pool包可以實現該功能(如果該包沒有安裝，可以通過?/rdbms/admin/dbmspool.sql安裝)，下面我通過在11g中試驗證明該問題
1.資料庫版本資訊
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
2.模擬一條sql語句
SQL> create table xff as
  2  select * from dba_tables
  3  where rownum<10;
 
表已建立。
 
SQL> select count(*) from xff;
 
  COUNT(*)
----------
         9
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
3.打破神話一:許可權操作
對錶進行許可權操作，可以清除該表在shared pool中關於該表記錄
SQL> grant select on xff to chf;
 
授權成功。
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
 
 
SQL> revoke select on xff from chf;
 
撤銷成功。
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
證明grant和revoke操作沒有清除shared pool中關於該表的sql語句


4.打破神話二:ddl操作
對錶進行ddl操作，可以清除該表在shared pool中關於該表記錄
SQL> alter table xff add  owner1 varchar2(100);
 
表已更改。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
事實證明ddl操作不能達到預期效果，沒有清除掉這條sql語句


5.重新整理shared pool
SQL> alter system flush shared_pool
  2  ;
 
系統已更改。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
未選定行
把整個shared pool都重新整理了，自然其中的一條sql語句也沒有了，在10g前只能通過這種方法實現


6.使用dbms_shared_pool.purge
SQL> select count(*) from xff;
 
  COUNT(*)
----------
         9
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
 
SQL> exec dbms_shared_pool.purge('1EFB91B8, 3642190903','C');
 
PL/SQL 過程已成功完成。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
未選定行
試驗證明在shared pool中清除了一條sql記錄


7.關於dbms_shared_pool.purge引數說明
purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
--    name
--      The name of the object to keep.  There are two kinds of objects:
--      PL/SQL objects, triggers, sequences, types and Java objects,
--      which are specified by name, and
--      SQL cursor objects which are specified by a two-part number
--      (indicating a location in the shared pool).  For example:
--        dbms_shared_pool.keep('scott.hispackage')
--      will keep package HISPACKAGE, owned by SCOTT.  The names for
--      PL/SQL objects follows SQL rules for naming objects (i.e.,
--      delimited identifiers, multi-byte names, etc. are allowed).
--      A cursor can be keeped by
--        dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
--      The complete hexadecimal address must be in the first 8 characters.
--      The value for this identifier is the concatenation of the
--      'address' and 'hash_value' columns from the v$sqlarea view.  This
--      is displayed by the 'sizes' call above.
--      Currently 'TABLE' and 'VIEW' objects may not be keeped.
--
--    flag
--      This is an optional parameter.  If the parameter is not specified,
--        the package assumes that the first parameter is the name of a
--        package/procedure/function and will resolve the name.  Otherwise,
--        the parameter is a character string indicating what kind of object
--        to keep the name identifies.  The string is case insensitive.
--        The possible values and the kinds of objects they indicate are
--        given in the following table:
--          Value        Kind of Object to keep
--          -----      ----------------------
--        P          package/procedure/function
--        Q          sequence
--        R          trigger
--        T          type
--        JS         java source
--        JC         java class
--        JR         java resource
--        JD         java shared data
--        C          cursor
--      If and only if the first argument is a cursor address and hash-value,
--        the flag parameter should be set to 'C' (or 'c').
--
--    heaps
--      heaps to purge. e.g if heap 0 and heap 6 are to be purged.
--      1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
--      Default is 1 i.e heap 0 which means the whole object will be purged.
10.2.0.4中bug(DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1])
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 15 09:59:03 2012
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select count(*) from v$sqlarea;
 
  COUNT(*)
----------
      2886
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90  418614462 select count(*) from v$sqlarea
 
SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
BEGIN dbms_shared_pool.purge('000000007C9BAC90,418614462','C'); END;
 
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
 
SQL> @?/rdbms/admin/dbmspool.sql
 
Package created.
 
 
Grant succeeded.
 
 
View created.
 
 
Package body created.
 
SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90  418614462 select count(*) from v$sqlarea
 
SQL> alter session set events '5614566 trace name context forever';
 
Session altered.
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90  418614462 select count(*) from v$sqlarea
 
SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
no rows selected




刪除表，也不能清除shared pool中的sql語句
SQL> select count(*) from xff;
 
  COUNT(*)
----------
         9
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
 
SQL> drop table xff;
 
表已刪除。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff