今天上课讲软软解析时,使用了缓存游标,在实验时有个迷惑:
会话125上观察,发现这条SQL
这应该不是缓存,只是没有关闭而已,好我在125号会话,再执行一个其它SQL语句,看是否还在open?
已经不再打开了。原来如此!
接下来,我对select * from gyj100 where id=1;执行三次
这时语句 select * from gyj100 where id=1; 真正被缓存了,这里就不再解释了。。。
在sqlplus执行SQL不关闭,执行下一条SQL时关闭上一条SQL,9i是这样,10G就不是了,11G又是这样。
我把缓存游标设为0,具体操作如下:
sys@OCM> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
会话13上执行SQL语句
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
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?
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
已经不再打开了。原来如此!
接下来我把缓存游标打开:
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;执行三次
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又是这样。