清除掉shared pool中某条sql语句方法

来源：互联网发布：小猫多少钱一只淘宝网编辑：程序博客网时间：2024/05/22 11:35

发表于 2012 年 02 月 15 日由惜分飞

联系：手机(13429648788) QQ(107644445)

链接：http://www.xifenfei.com/2521.html

标题：清除掉shared pool中某条sql语句方法

在论坛中看到一个帖子，如何清除掉shared pool中某条sql语句，如果是在10g以前的版本，那估计会比较麻烦，为了删除一条sql语句记录，需要清空整个shared pool，在10g中提供了新的dbms_shared_pool包可以实现该功能(如果该包没有安装，可以通过?/rdbms/admin/dbmspool.sql安装)，下面我通过在11g中试验证明该问题

1.数据库版本信息

SQL> select* from v$version; 
  
BANNER 
----------------------------------------------------------------------
  
Oracle Database11g 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 for32-bit Windows: Version 11.2.0.1.0 - Production 
NLSRTL Version 11.2.0.1.0 - Production

2.模拟一条sql语句

SQL> createtable xff as
  2 select * fromdba_tables 
  3 where rownum<10; 
  
表已创建。 
  
SQL> selectcount(*) fromxff; 
  
  COUNT(*)
---------- 
         9
  
SQL> col sql_textfor a30 
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA 
  2 WHERE SQL_TEXT LIKE 'select % xff';
  
  
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff

3.打破神话一:权限操作
对表进行权限操作，可以清除该表在shared pool中关于该表记录

SQL> grantselect on xff to chf; 
  
授权成功。 
  
SQL> col sql_text for a30 
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA 
  2 WHERE SQL_TEXT LIKE 'select % xff';
  
  
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff 
  
  
SQL> revokeselect on xff from chf; 
  
撤销成功。 
  
SQL> col sql_textfor a30 
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA 
  2 WHERE SQL_TEXT LIKE 'select % xff';
  
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff

证明grant和revoke操作没有清除shared pool中关于该表的sql语句

4.打破神话二:ddl操作
对表进行ddl操作，可以清除该表在shared pool中关于该表记录

SQL> altertable xff add owner1 varchar2(100); 
  
表已更改。 
  
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA 
  2 WHERE SQL_TEXT LIKE 'select % xff';
  
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff

事实证明ddl操作不能达到预期效果，没有清除掉这条sql语句

5.刷新shared pool

SQL> altersystem flush shared_pool 
  2  ;
  
系统已更改。 
  
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA 
  2 WHERE SQL_TEXT LIKE 'select % xff';
  
未选定行

把整个shared pool都刷新了，自然其中的一条sql语句也没有了，在10g前只能通过这种方法实现

6.使用dbms_shared_pool.purge

SQL> selectcount(*) fromxff; 
  
  COUNT(*)
---------- 
         9
  
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA 
  2 WHERE SQL_TEXT LIKE 'select % xff';
  
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff 
  
SQL> execdbms_shared_pool.purge('1EFB91B8, 3642190903','C');
  
PL/SQL 过程已成功完成。 
  
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$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/SQLobjects, triggers, sequences, types and Java objects, 
--      whichare specified by name, and  
--      SQL cursor objectswhich are specified by a two-part number
--      (indicating a locationin the shared pool).  For example:
--        dbms_shared_pool.keep('scott.hispackage')
--      will keep package HISPACKAGE, owned by SCOTT.  The namesfor
--      PL/SQLobjects follows SQL rules fornaming 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 bein the first 8 characters.
--      The valuefor 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/functionand will resolve the name.  Otherwise, 
--        the parameter is a character string indicating what kind of object
--        to keep the name identifies.  The string iscase 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         javasource
--            JC         java class
--        JR         java resource
--        JD         java shared data
--        C          cursor
--      If and onlyif the first argument is a cursor address andhash-value, 
--        the flag parameter should beset to 'C' (or 'c').
-- 
--    heaps 
--      heaps to purge. e.gif 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 0which means the whole object will be purged.

9I中清除特定表相关执行计划
dbms_shared_pool.purge工作原理猜测
oracle 中如何定位重要(消耗资源多)的SQL
shared pool latch 等待事件
使用dblink导致的/*+ OPAQUE_TRANSFORM */
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
library cache pin等待事件
Read by other session等待事件

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

《清除掉shared pool中某条sql语句方法》有 2 条评论

惜分飞说：
2012 年 02 月 15 日10:12
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 - Productionon Wed Feb 15 09:59:03 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> selectcount(*) fromv$sqlarea;

  COUNT(*)
----------
      2886

SQL> col sql_textfor a30
SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';

ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90 418614462select count(*)from v$sqlarea

SQL> execdbms_shared_pool.purge('000000007C9BAC90,418614462','C');
BEGIN dbms_shared_pool.purge('000000007C9BAC90,418614462','C');END;

      *
ERROR atline 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> execdbms_shared_pool.purge('000000007C9BAC90,418614462','C');

PL/SQL proceduresuccessfully completed.

SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';

ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90 418614462select count(*)from v$sqlarea

SQL> altersession set events '5614566 trace name context forever';

Session altered.

SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';

ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90 418614462select count(*)from v$sqlarea

SQL> execdbms_shared_pool.purge('000000007C9BAC90,418614462','C');

PL/SQL proceduresuccessfully completed.

SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';

no rows selected
  惜分飞[引用]  [回复]
惜分飞说：
2012 年 02 月 15 日09:43
删除表，也不能清除shared pool中的sql语句
SQL> selectcount(*) fromxff;

  COUNT(*)
----------
         9

SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA
  2 WHERE SQL_TEXT LIKE 'select % xff';

ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff

SQL> droptable xff;

表已删除。

SQL> SELECTADDRESS,HASH_VALUE,SQL_TEXT FROMV$SQLAREA
  2 WHERE SQL_TEXT LIKE 'select % xff';

ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903select count(*)from xff

0 0