软软解析之缓存游标的迷惑-----已解决   今天上课讲软软解析时，使用了缓存游标，在实验时有个迷惑：   我把缓存游标设为0，具体操作如下：

[html] sys@OCM> show parameter session_cached_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ session_cached_cursors integer 0 会话13上执行SQL语句[html] gyj@OCM> select sid from v$mystat where rownum=1;
SID ---------- 13 gyj@OCM> select * from gyj100 where id=1;
ID NAME ---------- -------------------------------------------------- 1 gyj1 会话125上观察，发现这条SQL[html] sys@OCM> select sid from v$mystat where rownum=1;
SID ---------- 125 sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 0000000076EB4F18 13 GYJ 000000006FE37970 3279472569 amavkw71rjjxt select * from gyj100 where id=1 OPEN 这应该不是缓存，只是没有关闭而已，好我在125号会话，再执行一个其它SQL语句，看是否还在open?[html] gyj@OCM> select * from gyj100 where id=2;
ID NAME ---------- -------------------------------------------------- 2 gyj2 gyj@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 0000000076851CB0 139 GYJ 000000006DCAC688 797053639 389005srs44q7 select * from gyj100 where id=2 OPEN gyj@OCM> select * from gyj100 where id=3;
ID NAME ---------- -------------------------------------------------- 3 gyj3 yj@OCM> select * from gyj100 where id=3;
ID NAME ---------- -------------------------------------------------- 3 gyj3 gyj@OCM> select * from gyj100 where id=3;
ID NAME ---------- -------------------------------------------------- 3 gyj3 gyj@OCM> select * from gyj100 where id=3;
ID NAME ---------- -------------------------------------------------- 3 gyj3 gyj@OCM> select * from gyj100 where id=3;
ID NAME ---------- -------------------------------------------------- 3 gyj3 sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 0000000076851CB0 139 GYJ 000000006DC3BF10 3087917848 gq623zuw0vsss select * from gyj100 where id=3 OPEN gyj@OCM> select * from gyj100 where id=4;
ID NAME ---------- -------------------------------------------------- 4 gyj4 sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 0000000076851CB0 139 GYJ 000000006DC45E60 3922265506 8hznm9bnwk1d2 select * from gyj100 where id=4 OPEN 已经不再打开了。原来如此！接下来我把缓存游标打开：[html] sys@OCM> alter system set session_cached_cursors =200 scope=spfile;
System altered. sys@OCM> startup force;
ORACLE instance started. Total System Global Area 388354048 bytes Fixed Size 2228584 bytes Variable Size 276827800 bytes Database Buffers 104857600 bytes Redo Buffers 4440064 bytes Database mounted. Database opened. yj@OCM> select * from gyj100 where id=1;
ID NAME ---------- -------------------------------------------------- 1 gyj1 sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 000000007684BD60 141 GYJ 000000006F9F8E10 2819066422 8aspttkn0g2jq select * from gyj100 where id=1 OPEN gyj@OCM> select * from gyj100 where id=2;
ID NAME ---------- -------------------------------------------------- 2 gyj2 sys@OCM> sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 000000007684BD60 141 GYJ 000000006F9BC9C8 797053639 389005srs44q7 select * from gyj100 where id=2 OPEN 接下来，我对select * from gyj100 where id=1;
执行三次[html] gyj@OCM> select * from gyj100 where id=1;
ID NAME ---------- -------------------------------------------------- 1 gyj1 gyj@OCM> select * from gyj100 where id=1;
ID NAME ---------- -------------------------------------------------- 1 gyj1 gyj@OCM> select * from gyj100 where id=1;
ID NAME ---------- -------------------------------------------------- 1 gyj1 sys@OCM> sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 0000000076848DB8 142 GYJ 000000006F8CF9F0 3279472569 amavkw71rjjxt select * from gyj100 where id=1 DICTIONARY LOOKUP CURSOR CACHED yj@OCM> select * from gyj100 where id=2;
ID NAME ---------- -------------------------------------------------- 2 gyj2 sys@OCM> sys@OCM> select * from v$open_cursor where sql_text like '
%select * from gyj100 where id=%'
;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACT SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ ------------ ----------- ---------------------------------------------------------------- 0000000076848DB8 142 GYJ 000000006F899AA8 797053639 389005srs44q7 select * from gyj100 where id=2 OPEN 0000000076848DB8 142 GYJ 000000006F8CF9F0 3279472569 amavkw71rjjxt select * from gyj100 where id=1 SESSION CURSOR CACHED

 

这时语句 select * from gyj100 where id=1; 真正被缓存了，这里就不再解释了。。。     在sqlplus执行SQL不关闭，执行下一条SQL时关闭上一条SQL，9i是这样，10G就不是了，11G又是这样。