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 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 |
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缓存:
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!
Hdr: 7538951 10.2.0.4.0 RDBMS 10.2.0.4.0 DICTIONARY PRODID-5 PORTID-226 5614566
Abstract: DBMS_SHARED_POOL IS NOT WORKING AS EXPECTED
PROBLEM:
——–
Dbms_shared_pool is not working .
SQL> select address,hash_value,version_count,invalidations from v$sqlarea
where
upper(sql_text) like ‘SELECT COUNT(*) FROM MKT_YIELD_PC’;
ADDRESS HASH_VALUE VERSION_COUNT INVALIDATIONS
—————- ———- ————- ————-
00000004F8569478 2442003789 1 0
SQL> exec dbms_shared_pool.purge (‘00000004F8569478,2442003789 ‘,’C’)
PL/SQL procedure successfully completed.
SQL> select address,hash_value,version_count,invalidations from v$sqlarea
where upper(sql_text)
like ‘SELECT COUNT(*) FROM MKT_YIELD_PC’;
ADDRESS HASH_VALUE VERSION_COUNT INVALIDATIONS
—————- ———- ————- ————-
00000004F8569478 2442003789 1 0
DIAGNOSTIC ANALYSIS:
——————–
Did a small test case on windows and linux box .
session 1
===========
conn scott/tiger
SQL> select * from dept where deptno=10;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
Session 2
===========
conn / as sysdba
SQL>select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = ‘select * from dept where deptno=10′; 2
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
——– ———- ———- ———- ————- ————-
PARSE_CALLS
———–
28F1FCB4 911274289 1 1 1 0
1
SQL> exec dbms_shared_pool.purge (’28F1FCB4,911274289′,’C’);
PL/SQL procedure successfully completed.
SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = ‘select * from dept where deptno=10’; 2
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
——– ———- ———- ———- ————- ————-
PARSE_CALLS
———–
28F1FCB4 911274289 1 1 1 0
1
======> here no rows should have been selected
again go to session 1
SQL> select * from dept where deptno=10;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
Now Go to session 2
===================
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
——– ———- ———- ———- ————- ————-
PARSE_CALLS
———–
28F1FCB4 911274289 2 1 1 0
2
=====> As per the Note 457309.1 Loads count should have increased.
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.4
Information in this document applies to any platform.
Goal
This article is intended for the DBAs who manually want to purge library cache heaps on 10.2.0.4 database.
Session 1
===========
conn scott/tiger
SQL> select * from dept where deptno=10;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
Session 2
===========
conn / as sysdba
SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = ‘select * from dept where deptno=10′;
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
——– ———- ———- ———- ————- ————-
PARSE_CALLS
———–
28F1FCB4 911274289 1 1 1 0
1
SQL> exec dbms_shared_pool.purge (’28F1FCB4,911274289′,’C’);
PL/SQL procedure successfully completed.
SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = ‘select * from dept where deptno=10’;
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
——– ———- ———- ———- ————- ————-
PARSE_CALLS
———–
28F1FCB4 911274289 1 1 1 0
1
====== Here no rows should have been selected.
.
.
Again go to session 1
.
.
SQL> select * from dept where deptno=10;
.
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
Now Go to session 2
===================
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
——– ———- ———- ———- ————- ————-
PARSE_CALLS
———–
28F1FCB4 911274289 2 1 1 0
2
Here you can see the DBMS_SAHRED_POOL.PURGE is not working as expected.
Solution
DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available
through the fix for bug 5614566. However, the fix is event protected. You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect.
Set the event 5614566 in the init.ora to turn purge on.
event=”5614566 trace name context forever”
SQL> select address, hash_value from v$sqlarea where sql_text = ‘select * from dept’;
ADDRESS HASH_VALUE
——– ———-
2671F27C 3599690174
SQL> exec dbms_shared_pool.purge(‘2671F27C,3599690174′,’C’);
PL/SQL procedure successfully completed.
SQL> select address, hash_value from v$sqlarea where sql_text = ‘select * from dept’;
no rows selected
Hdr: 8479537 10.2.0.4 RDBMS 10.2.0.4 SHRD CRSRS PRODID-5 PORTID-226
Abstract: WRONG DATA INSERTED WHEN WHEN CURSORS GETS FREQUENTLY FLUSHED FROM SHARED POOL
PROBLEM:
——–
Running an OCCI program which inserts data [ single row ], fails when we run
from a second session alter system flush shared pool quite frequently. The
problem is that we don’t get any error, instead just a wrong number will be
inserted. This is very critical for CERN.
The alter system flush shared pool should simulate a stressed shared pool.
The customer gets this error without running alter system flush shared pool.
DIAGNOSTIC ANALYSIS:
——————–
The customer has written a smal test case to show this behavior.
The testcase consists out of an OCCI prgram which calls a PLSQL procedure.
Under normal circumstances we should never get the -20999 PLSQL exeception as
the number should always fit.
++ SQL-Script :
CREATE TABLE ReqType (id INTEGER CONSTRAINT PK_ReqType_Id PRIMARY KEY, type
NUMB
ER);
CREATE SEQUENCE req_seq CACHE 300 START WITH 5345678901;
— check that the id is between the start of the sequence and 1000000000
CREATE OR REPLACE TRIGGER tr_ReqType_checkId BEFORE INSERT ON ReqType
FOR EACH ROW WHEN (new.id > 6345678901 or new.id < 5345678901) BEGIN RAISE_APPLICATION_ERROR(-20999, 'Big Id problem detected afterward ' || TO_CHAR( :new.id)); END; / I seems there is a very tiny time window where we can get this error, as we need to run the test case up to 30 minutes to reproduce the problem. The problem can only be reproduced by running from a 2 second session flush shared pool at a high rate [ see flush.sql ] Seession 1: oci program doing inserts Session 2: PLSQL block running alter system flush shared pool ever 0.2 s Additional comments: + Currrently we are not able to repro when we aging out the cursor by running : sys.dbms_shared_pool.purge(l_add_hash,'c'); Outut from cur.sql script : + the number of child cursors increase for each run [ version_count in v$sqlarea increase too ] + the reason is BIND_MISMATCH [ check v$sql_shared_cursor ] ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS ---------------- ---------- ---------- ---------- ------------- ------------- PARSE_CALLS ----------- 000000006728AE98 4007671449 2 353 31 353 0 ++Parent-Addr: 000000006728AE98 Child_cur_no: 0 BIND_MISMATCH: Y Child_cur_no: 1 BIND_MISMATCH: Y Child_cur_no: 2 BIND_MISMATCH: Y Child_cur_no: 3 BIND_MISMATCH: Y Child_cur_no: 4 BIND_MISMATCH: Y Child_cur_no: 5 BIND_MISMATCH: Y Child_cur_no: 6 BIND_MISMATCH: Y Child_cur_no: 7 BIND_MISMATCH: Y Child_cur_no: 8 BIND_MISMATCH: Y ... WORKAROUND: ----------- N/A RELATED BUGS: ------------- N/A REPRODUCIBILITY: ---------------- You need to run the test case quite a long time. On my system the error reproduce every 30 minutes. Because its a very interm. problem you may want to start the investigations by testing on my system cd users/hh/OCI/CERN/NEW + Run the OCI program by : oci wait about 60 seconds and now run flush.sql + sqlplus "/ as sysdba" @flush After a while you should see the following error db102x@suplnxd8:~/users/hh/OCI/CERN/NEW $ oci 1140003 rows insertedError caught in single row insertion while inserting 5355856102 20999 ORA-20999: Big Id problem detected afterward 26862411442968136000000000 ORA-6512: at "SCOTT.TR_REQTYPE_CHECKID", line 2 ORA-4088: error during execution of trigger 'SCOTT.TR_REQTYPE_CHECKID' 2) If you want to reproduce the problem run + sh -x cl.sh [ compile oci.cpp ] + sqlplus scott/tiger @cr [ create PLSQL objects ... ] Follow the flow from 1) to run the testcase cl.sh + Compile and link script cr.sql + create necassary objets cur.sql + script to check child cursor ... flush.sql + script to flush shared pool oci + oci test program [ Linux x86-64 ] oci.cpp + source oci test program TEST CASE: ---------- STACK TRACE: ------------ N/A SUPPORTING INFORMATION: ----------------------- N/A 24 HOUR CONTACT INFORMATION FOR P1 BUGS: ---------------------------------------- N/A DIAL-IN INFORMATION: -------------------- N/A IMPACT DATE: ------------ quite urgent RELEASE NOTES: ]]Child cursors might have been incorrectly selected for use when they should ]]have been rejected because of bind mismatches. This has now been resolved. REDISCOVERY INFORMATION: Because of the variety of symptoms possible it is difficult to give a specific rediscovery description. However, the problem is likely to be intermittent, show up only under shared pool load, *might* be reduced in frequency by using dbms_shared_pool.keep plus the fix for bug 7025450 and be a case where it is suspected that a cursor sharing problem is the case. The failing SQL will also have binds present. WORKAROUND: Sometimes using dbms_shared_pool.KEEP on the failing statements (with the fix for bug 7025450 present) might help reduce the frequency of the problem.