软软解析之缓存游标的迷惑-----已解决(二)
_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 DICT