理解V$OPEN_CURSOR,SESSION_CACHED_CURSOR

轉載自:http://adian.itpub.net/post/22879/293659

這兩個概念很容易混淆。
概念：
OPEN_CURSOR，定義每個Session最大能夠開啟的遊標數量。在init.ora檔案中定義，可以透過select * from v$parameter where name = 'open_cursors'查詢。
V$OPEN_CURSOR，當前Session快取的遊標，而不是曾經開啟的遊標。 SESSION_CACHED_CURSOR，當前Session已經關閉並被快取的遊標。
V$OPEN_CURSOR中顯示的當前Session遊標快取中游標，如果要精確查詢當前Session開啟的遊標總數，需要從V$ SESSTAT中查詢。

select a.value,
s.username,
s.sid,
s.serial#
from
v$sesstat a,
v$statname b,
v$session s
where
a.statistic# = b.statistic# and
s.sid=a.sid and
b.name = 'opened cursors current';

Session Cache的原理：
當設定SESSION_CACHED_CURSOR的值之後，當有parse請求的時候，Oracle會從library cache中查詢。如果有超過3次同樣的parse請求，這個遊標將會存入Session的遊標快取中。對於將來同樣的查詢，就甚至不要soft parse，直接從Session的遊標快取中取。

驗證：
登入兩個SQL*PLUS客戶端，分別為Session test和Session monitor。

1. 檢查是否引數設定，以及執行的SQL語句是否在V$OPEN_CURSOR找到。

Session Test:
SQL> show parameter session_cached_cursors;

NAME TYPE VALUE
------------------------------
session_cached_cursors integer 0
SQL> select sid from v$mystat where rownum=1;
SID
----------
9
SQL> select sid from v$mystat where rownum=1;
SID
----------
9

透過如上的執行結果可以知道，當前引數設定session_cached_cursors的值為0，不快取當前Session關閉的遊標。當前Session的ID為9。

Session Monitor: SQL> SELECT SID, n.NAME para_name, s.VALUE used
2 FROM SYS.v_$statname n, SYS.v_$sesstat s
3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
4 AND s.statistic# = n.statistic# 5 AND SID = 9;
SID PARA_NAME USED
--- ------------------------------ ----------
9 opened cursors current 1
9 session cursor cache count 0
SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;
SID USER_NAME SQL_TEXT
--- ---------- -----------------------------------------------------------
9 SCOTT select sid from v$mystat where rownum=1

透過如上的執行結果可以知道，當前在V$OPEN_CURSOR儲存一個遊標，對應SQL為Session Test執行的最後一條語句。SESSION_CACHED_CURSOR沒有儲存遊標。

2. 更改引數SESSION_CACHED_CURSOR值。

Session Test:
SQL> alter session set session_cached_cursors = 1;
Session altered.
SQL> show parameter session_cached_cursors;
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
session_cached_cursors integer 1

3. 驗證如下結論。如果遊標被存入SESSION_CACHED_CURSOR，前提是遊標已經關閉，遊標對應的SQL被執行3次以上。OPEN_CURSOR中會儲存儲存在SESSION_CACHED_CURSOR以及開啟的遊標（不是精確值）。

Session Test:
SQL> select sid from v$mystat where rownum =1;
SID
----------
9
SQL> select sid from v$mystat where rownum =1;
SID
----------
9
SQL> select sid from v$mystat where rownum =1;
SID
----------
9

Session Monitor:
SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;
SID USER_NAME SQL_TEXT
--- ---------- -----------------------------------------------------------
9 SCOTT select sid from v$mystat where rownum =1
SQL> SELECT SID, n.NAME para_name, s.VALUE used
2 FROM SYS.v_$statname n, SYS.v_$sesstat s
3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
4 AND s.statistic# = n.statistic#
5 AND SID = 9;
SID PARA_NAME USED
--- ------------------------------ ----------
9 opened cursors current 1
9 session cursor cache count 0

v$open_cursor dooes not show all open cursors. it shows more than that, the best option to find the number of open cursors is from v$sysstat. 透過如上的執行結果可以知道，即使同一個遊標被開啟3次，在SESSION_CACHED_CURSOR的數量仍然為0。

下面，將會在Session Test中關閉遊標（透過執行一條其他的語句）。

Session Test: SQL> select * from t where rownum!=7;
no rows selected

Session Monitor:
SQL> SELECT SID, n.NAME para_name, s.VALUE used
2 FROM SYS.v_$statname n, SYS.v_$sesstat s
3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
4 AND s.statistic# = n.statistic#
5 AND SID = 9;
SID PARA_NAME USED
--- ------------------------------ ----------
9 opened cursors current 1
9 session cursor cache count 1
SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;
SID USER_NAME SQL_TEXT
--- ---------- -----------------------------------------------------------
9 SCOTT select sid from v$mystat where rownum=1
9 SCOTT select * from t where rownum!=7

透過如上的執行結果可以知道，遊標被開啟3次之後，如果這個遊標關閉之後，遊標會被儲存到SESSION_CACHED_CURSOR當中。同時，透過OPEN_CURSOR中顯示的SQL可以得知，OPEN_CURSOR中會儲存儲存在SESSION_CACHED_CURSOR以及開啟的遊標（不是精確值）。

其他：SESSION_CACHED_CURSOR採用的是LRU演算法，如果如果有新的遊標需要快取，而當前遊標快取已經滿，最少使用的遊標將會被清除出去。調整SESSION_CACHED_CURSOR引數。透過如下SQL得到從快取中取遊標以及取PARSE的數量，為調整作參考。

select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs,
v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic# and
nm1.name = 'session cursor cache hits' and
prs.statistic#=nm2.statistic# and
nm2.name= 'parse count (total)' and
cach.sid= &sid and prs.sid= cach.sid ;

參考：
1.Monitoring Open and Cached Cursors
2.session_cached_cursor