软软解析之缓存游标的迷惑-----已解决
今天上课讲软软解析时，使用了缓存游标，在实验时有个迷惑：
我把缓存游标设为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