ORACLE判斷OPEN_CURSOR參數(shù)設(shè)置合理性

1、.理解 V$OPEN_CURSOR, V$SESSION_CACHED_CURSOR概念：OPEN_CURSOR ，定義每個(gè) Session 最大能夠打開的游標(biāo)數(shù)量。在 init.ora文件中定義， 可以通過select *from v$parameter where name = 'open_cursors'查詢。V$OPEN_CURSOR，當(dāng)前 Session 緩存的游標(biāo)，而不是曾經(jīng)打開的游標(biāo)。V$SESSION_CACHED_CURSOR，當(dāng)前 Session 已經(jīng)關(guān)閉并被緩存的游標(biāo)。V$OPEN_CURSOR中顯示的當(dāng)前Session 游標(biāo)緩存中游標(biāo)，如果要精確查詢當(dāng)前

2、Session 打開的游標(biāo)總數(shù)，需要從V$ SESSTAT 中查詢。select a.value,s.username,s.sid,s.serial#fromv$sesstat a,v$statname b,v$session swherea.statistic# = b.statistic# ands.sid=a.sid = 'opened cursors current'Session Cache的原理：當(dāng)設(shè)定 SESSION_CACHED_CURSOR 的值之后， 當(dāng)有 parse 請(qǐng)求的時(shí)候， Oracle 會(huì)從 library cache 中查詢。

3、如果有超過 3 次同樣的 parse 請(qǐng)求，這個(gè)游標(biāo)將會(huì)存入 Session 的游標(biāo)緩存中。 對(duì)于將來同樣的查詢，就甚至不要soft parse ，直接從Session 的游標(biāo)緩存中取。驗(yàn)證：登錄兩個(gè) SQL*PLUS客戶端，分別為Session test 和 Session monitor 。1. 檢查是否參數(shù)設(shè)置，以及執(zhí)行的SQL 語句是否在 V$OPEN_CURSOR 找到。Session Test:SQL> show parameter session_cached_cursors;NAME TYPE VALUE-session_cached_cursors integer 0S

4、QL> select sid from v$mystat where rownum=1;SID-9.SQL> select sid from v$mystat where rownum=1;SID-9通過如上的執(zhí)行結(jié)果可以知道，當(dāng)前參數(shù)設(shè)置session_cached_cursors的值為 0 ，不緩存當(dāng)前Session 關(guān)閉的游標(biāo)。當(dāng)前Session 的 ID 為 9。Session Monitor:SQL> SELECT SID, n.NAME para_name, s.VALUE used2FROM SYS.v_$statname n, SYS.v_$sesstat s

5、3WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')4AND s.statistic# = n.statistic# 5 AND SID = 9;SID PARA_NAME USED- - -9 opened cursors current 19 session cursor cache count 0SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;SID USER_NAME S

6、QL_TEXT- - -9SCOTT select sid from v$mystat where rownum=1通過如上的執(zhí)行結(jié)果可以知道，當(dāng)前在V$OPEN_CURSOR 存儲(chǔ)一個(gè)游標(biāo)，對(duì)應(yīng)SQL 為 Session Test 執(zhí)行的最后一條語句。V$SESSION_CACHED_CURSOR 沒有存儲(chǔ)游標(biāo)。2. 更改參數(shù)V$SESSION_CACHED_CURSOR值。Session Test:SQL> alter session set session_cached_cursors = 1;Session altered.SQL> show parameter sessi

7、on_cached_cursors;NAME TYPE VALUE- - -session_cached_cursors integer 13. 驗(yàn)證如下結(jié)論。如果游標(biāo)被存入SESSION_CACHED_CURSOR，前提是游標(biāo)已經(jīng)關(guān)閉，游標(biāo)對(duì)應(yīng)的SQL 被執(zhí)行 3 次以上。 OPEN_CURSOR 中會(huì)存儲(chǔ)保存在 SESSION_CACHED_CURSOR 以及打開的游標(biāo)（不是精確值）。Session Test:SQL> select sid from v$mystat where rownum =1;SID-9SQL> select sid from v$mystat wher

8、e rownum =1;.SID-9SQL> select sid from v$mystat where rownum =1;SID-9Session 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 =1SQL> SELECT SID, n.NAME para_name, s.VALUE used2FROM SYS.v_$sta

9、tname n, SYS.v_$sesstat s3WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')4AND s.statistic# = n.statistic#5 AND SID = 9;SID PARA_NAME USED- - -9 opened cursors current 19 session cursor cache count 0v$open_cursor dooes not show all open cursors. it shows more

10、than that, the best option to find thenumber of open cursors is from v$sysstat.通過如上的執(zhí)行結(jié)果可以知道，即使同一個(gè)游標(biāo)被打開3 次，在 SESSION_CACHED_CURSOR的數(shù)量仍然為 0。下面，將會(huì)在Session Test 中關(guān)閉游標(biāo)（通過執(zhí)行一條其他的語句）。Session Test:SQL> select * from t where rownum!=7;no rows selectedSession Monitor:SQL> SELECT SID, n.NAME para_name,

11、s.VALUE used2 FROM SYS.v_$statname n, SYS.v_$sesstat s3 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 19 session cursor cache count 1SQL> select SID, USER_NAME,

12、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通過如上的執(zhí)行結(jié)果可以知道，游標(biāo)被打開3 次之后，如果這個(gè)游標(biāo)關(guān)閉之后，游標(biāo)會(huì)被存儲(chǔ)到SESSION_CACHED_CURSOR當(dāng)中。同時(shí)，通過OPEN_CURSOR中顯示的 SQL 可以得知，OPEN_CURSOR中會(huì)存儲(chǔ)保存在SESSION_CACHED_CURSOR以及打開的游標(biāo)（不

13、是精確值）。其他： SESSION_CACHED_CURSOR采用的是LRU 算法，如果如果有新的游標(biāo)需要緩存，而當(dāng)前游標(biāo)緩存已經(jīng)滿， 最少使用的游標(biāo)將會(huì)被清除出去。調(diào)整 SESSION_CACHED_CURSOR參數(shù)。通過如下SQL得到從緩存中取游標(biāo)以及取PARSE 的數(shù)量，為調(diào)整作參考。select cach.value cache_hits, prs.value all_parses,prs.value-cach.value sess_cur_cache_not_usedfrom v$sesstat cach, v$sesstat prs,v$statname nm1, v$statname nm2where