dbms_shared_pool.purge 清理某个SQL执行计划

最新推荐文章于 2024-04-09 22:06:35 发布

coco3600

最新推荐文章于 2024-04-09 22:06:35 发布

阅读量428

点赞数

文章标签：数据库 java 面试

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

About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2129896/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2129896/

coco3600

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
dbms_shared_pool.purge 清理某个SQL执行计划

dbms_shared_pool.purge 清理某个SQL执行计划在日常管理中,经常有让sql重新解析的需求,比如说使用了bind peeking,第一次绑...
复制链接

扫一扫