2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
Oracle中清除游标缓存的几种方法
9i以后引入了bind peeking绑定变量窥视特性，但该特性常有帮当忙之嫌，所以有了11g的自适应游标特性。排除因绑定变量窥视造成的因素外，统计信息讹误也会造成执行计划偏差，这时我们就可能需要清除指定游标的缓存信息，从而达到重新解析的目的。
下面我们列举几种可以达到清除游标缓存的方法，权作抛砖引玉:
1. alter system flush shared_pool; /* 最简单最粗暴的方法，清除所有游标缓存，可能造成短期内大量解析，不推荐*/
2. dbms_shared_pool 包很早就有了，但该包名下的purge过程却要到10.2.0.4才出现，Bug 5614566最早在2006年描述了需要清除游标缓存接口的要求：
HEAP参数指定了清除对象的哪些堆信息，以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缓存:
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 IN FLAG CHAR IN DEFAULT HEAPS NUMBER IN DEFAULT其中NAME指定了需要清除的对象名,这里分成2种。PL/SQL对象，触发器，序列，类型和JAVA对象以其命名指定；SQL游标对象通过该SQL的address与hash_value组合指定。FLAG指定了对象的类型，若没有指定该参数，Oracle将认为之前代入的NAME参数对应到包/存储过程/函数的命名空间， 需要注意的是该参数是大小写敏感的，包括了以下各类型:
| FLAG值 | 对应对象类型 |
| P | 包/存储过程/函数 |
| Q | 序列 |
| R | 触发器 |
| T | 类型 |
| JS | Java源程序 |
| JC | Java类程序 |
| JR | Java资源 |
| JD | Java共享数据 |
| C | cursor |
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> 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 3
SQL> select address, plan_hash_value
2 from v$sql_plan
3 where sql_id = '25asu5a76nqmn';
ADDRESS PLAN_HASH_VALUE
-------- ---------------
2F51508C 2542806819
2F51508C 2542806819
2F51508C 2542806819
SQL> 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 2542806819
SQL> select * from v$sql_plan where plan_hash_value= 2542806819;
未选定行
/*执行计划消失了，而游标主体信息仍在*/
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
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 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 STATEMENT
2F51508C SORT
2F51508C TABLE ACCESS
SQL> 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(*)
----------
9
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 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(*)
----------
9
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 0 1 2542806819
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:39:28
2F540EA0 SORT 13:39:28
2F540EA0 TABLE ACCESS 13:39:28
SQL> 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:23
2F540EA0 SORT 13:40:23
2F540EA0 TABLE ACCESS 13:40:23
/*执行计划的时间戳发生了变化，达到了重新解析游标的目的*/
4.或许你不是一个位高权重的DBA，无法执行授权/撤职命令，但如果你能分析游标所涉及对象的统计信息或者执行其他一些ddl操作，那么也可以达到同样的目的:
SQL> alter system flush shared_pool;
系统已更改。
SQL>
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
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 0 1 2542806819
SQL> 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(*)
----------
9
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 0 1 2542806819
SQL> 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!
posted on 2010-08-05 12:58 Oracle和MySQL 阅读(1631) 评论(0) 编辑 收藏 举报
