在日常管理中,经常有让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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31448824/viewspace-2139333/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31448824/viewspace-2139333/