理解V$OPEN_CURSOR, V$SESSION_CACHED_CURSOR

adian發表於2007-06-09
這兩個概念很容易混淆。[@more@]概念:
OPEN_CURSOR,定義每個Session最大能夠開啟的遊標數量。在init.ora檔案中定義,可以透過select * from v$parameter where name = 'open_cursors'查詢。
V$OPEN_CURSOR,當前Session快取的遊標,而不是曾經開啟的遊標。 V$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執行的最後一條語句。V$SESSION_CACHED_CURSOR沒有儲存遊標。

2. 更改引數V$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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/94829/viewspace-918266/,如需轉載,請註明出處,否則將追究法律責任。

相關文章