Oracle中清除游标缓存的几种方法
来源:互联网 发布:sql语句查询所有库存 编辑:程序博客网 时间:2024/05/22 05:33
9i以后引入了bind peeking绑定变量窥视特性,但该特性常有帮当忙之嫌,所以有了11g的自适应游标特性。排除因绑定变量窥视造成的因素外,统计信息讹误也会造成执行计划偏差,这时我们就可能需要清除指定游标的缓存信息,从而达到重新解析的目的。
下面我们列举几种可以达到清除游标缓存的方法,权作抛砖引玉:
1. alter system flush shared_pool; /* 最简单最粗暴的方法,清除所有游标缓存,可能造成短期内大量解析,不推荐*/
2. dbms_shared_pool 包很早就有了,但该包名下的purge过程却要到10.2.0.4才出现,Bug 5614566最早在2006年描述了需要清除游标缓存接口的要求:
Hdr: 5614566 10.2.0.2 RDBMS 10.2.0.2 DICTIONARY PRODID-5 PORTID-176
Abstract: WE NEED A FLUSH CURSOR INTERFACE
*** 10/20/06 07:48 am ***
而且该过程在10.2.0.4中默认是无法正常使用的,需要通过设置event 5614566或者打上5614566补丁来启用;具体设置方法如下:
alter system set events ‘5614566 trace name context forever';
该存储过程的具体argument如下:
PROCEDURE PURGE参数名称 类型 输入/输出默认值?------------------------------ ----------------------- ------ --------NAME VARCHAR2 INFLAG CHAR IN DEFAULTHEAPS NUMBER IN DEFAULT
其中NAME指定了需要清除的对象名,这里分成2种。PL/SQL对象,触发器,序列,类型和JAVA对象以其命名指定;SQL游标对象通过该SQL的address与hash_value组合指定。FLAG指定了对象的类型,若没有指定该参数,Oracle将认为之前代入的NAME参数对应到包/存储过程/函数的命名空间, 需要注意的是该参数是大小写敏感的,包括了以下各类型:
FLAG值对应对象类型P包/存储过程/函数Q序列R触发器T类型JSJava源程序JCJava类程序JRJava资源JDJava共享数据CcursorHEAP参数指定了清除对象的哪些堆信息,以SQL游标为例,其最主要的信息包括在HEAP 0和HEAP 6中,HEAP 0包括了游标自身的大多数信息,而HEAP 6则存放了游标相关的执行计划。如果我们想要清除HEAP 0和HEAP 6中的信息,则2的0次方+2的6次方=1+64=65,那么我们在代入HEAP参数为65 即可;如果我们只想清除游标的执行计划则清除HEAP 6即可,代入HEAP参数为2的6次方即64。该参数的默认值为1,清除HEAP 0将会导致整个对象的缓存信息被清除掉。
下面我们来演示如何利用该存储过程来清除SQL缓存:
SQL> alter system flush shared_pool;系统已更改。SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls 9 from v$sqlarea 10 where sql_text like '%cache_me%' 11 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS------------- -------- ---------- ---------- ---------- ------------- ------------- -----------25asu5a76nqmn 2F51508C 2389334644 3 1 1 0 3SQL> select address, plan_hash_value 2 from v$sql_plan 3 where sql_id = '25asu5a76nqmn';ADDRESS PLAN_HASH_VALUE-------- ---------------2F51508C 25428068192F51508C 25428068192F51508C 2542806819SQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',64);PL/SQL 过程已成功完成。SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F51508C 2389334644 4 1 1 0 4 2542806819SQL> select * from v$sql_plan where plan_hash_value= 2542806819;未选定行/*执行计划消失了,而游标主体信息仍在*/SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F51508C 2389334644 5 1 1 0 5 2542806819/*这里新增的一次parse call是硬解析*/SQL> select address,operation from v$sql_plan where plan_hash_value= 2542806819;ADDRESS OPERATION-------- ------------------------------------------------------------2F51508C SELECT STATEMENT2F51508C SORT2F51508C TABLE ACCESSSQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',1);PL/SQL 过程已成功完成。SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';未选定行SQL> select address,operation from v$sql_plan where plan_hash_value= 2542806819;未选定行SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F51508C 2389334644 1 2 1 1 1 2542806819/*清除游标heap 0后,包括执行计划的所有信息都被清除了,甚至于simulator中的信息*/
3.如果您的环境中恰好无法利用dbms_shared_pool.purge存储过程,我们也可以采用一些折中的方法来清除游标缓存;譬如通过一个无关紧要的grant/revoke操作,但这样也会造成所有该授权/撤职对象相关SQL的执行计划失效:
SQL> alter system flush shared_pool;系统已更改。SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F540EA0 2389334644 1 1 1 0 1 2542806819SQL> select address,operation,to_char(timestamp,'HH24:MI:SS') from v$sql_plan where plan_hash_value= 2542806819;ADDRESS OPERATION TO_CHAR(-------- ------------------------------------------------------------ --------2F540EA0 SELECT STATEMENT 13:39:282F540EA0 SORT 13:39:282F540EA0 TABLE ACCESS 13:39:28SQL> revoke select on youyus from scott;撤销成功。SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F540EA0 2389334644 1 1 1 1 1 2542806819/*授权/撤销会造成执行计划invalid,此处 INVALIDATIONS上升到1*/SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9/*重新执行SQL,将引发一次硬解析*/SQL> select address,operation,to_char(timestamp,'HH24:MI:SS') from v$sql_plan where plan_hash_value= 2542806819;ADDRESS OPERATION TO_CHAR(-------- ------------------------------------------------------------ --------2F540EA0 SELECT STATEMENT 13:40:232F540EA0 SORT 13:40:232F540EA0 TABLE ACCESS 13:40:23/*执行计划的时间戳发生了变化,达到了重新解析游标的目的*/
4.或许你不是一个位高权重的DBA,无法执行授权/撤职命令,但如果你能分析游标所涉及对象的统计信息或者执行其他一些ddl操作,那么也可以达到同样的目的:
SQL> alter system flush shared_pool;系统已更改。SQL>SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F540EA0 2389334644 1 1 1 0 1 2542806819SQL> analyze table youyus compute statistics;表已分析。SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F540EA0 2389334644 1 1 1 1 1 2542806819/*统计信息更新,造成了invalid*/SQL> create index ind_youyus on youyus(t1);索引已创建。SQL> alter system flush shared_pool;系统已更改。SQL> select /* cache_me */ count(*) from youyus; COUNT(*)---------- 9SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F464EA0 2389334644 1 1 1 0 1 2542806819SQL> alter index ind_youyus rebuild online;索引已更改。SQL> select sql_id, 2 address, 3 hash_value, 4 executions, 5 loads, 6 version_count, 7 invalidations, 8 parse_calls, 9 plan_hash_value 10 from v$sqlarea 11 where sql_text like '%cache_me%' 12 and sql_text not like '%v$sqlarea%';SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F464EA0 2389334644 1 1 1 1 1 2542806819/*在线重建索引也可以达到同样的目的*/That's Great!
原文地址:http://www.askmaclean.com/archives/oracle%E4%B8%AD%E6%B8%85%E9%99%A4%E6%B8%B8%E6%A0%87%E7%BC%93%E5%AD%98%E7%9A%84%E5%87%A0%E7%A7%8D%E6%96%B9%E6%B3%95.html
- Oracle中清除游标缓存的几种方法
- Oracle中清除游标缓存的几种方法
- 清除几种缓存的方法
- Magento清除缓存的几种方法
- 清除浏览器缓存的几种方法
- 清除浏览器缓存的几种方法
- 清除浏览器缓存的几种方法
- js清除缓存的几种方法
- 清除浏览器缓存的几种方法
- ASP.NET几种清除页面缓存的方法
- ASP.NET几种清除页面缓存的方法
- ASP.NET几种清除页面缓存的方法
- js清除浏览器缓存的几种方法
- js清除浏览器缓存的几种方法
- js清除浏览器缓存的几种方法
- javascript清除浏览器缓存的几种方法
- js清除浏览器缓存的几种方法
- js清除浏览器缓存的几种方法
- group by 优化临时表
- jboss完全可以独立运行 servlet jsp ssh mybits spring mvc
- 【Leetcode】之Generate Parentheses
- 一次查找sqlserver死锁的经历
- 如何配置pl/sql 连接远程oracle服务器
- Oracle中清除游标缓存的几种方法
- andorid平板中安装完整的ubuntu系统
- 第四章 Controller接口控制器详解(5)——跟着开涛学SpringMVC
- CMake编译运行OpenCV程序_debug总结
- Elasticsearch 全字段搜索_all,query_string查询,不进行分词
- Weka聚类算法的两个实现算法
- Symfony2之创建一个简单的web应用
- [Leetcode] Product of Array Except Self
- 阿里云 log输出