oracle參數(shù)open_cursors和session_cached_cursor詳解！

SQL> show parameter open_cursors --每個(gè)session（會(huì)話）最多能同時(shí)打開(kāi)多少個(gè)cursor（游標(biāo)）  

NAME                                 TYPE        VALUE 

------------------------------------ ----------- ------------------------------  

open_cursors     integer 300

SQL> show parameter session_cached_cursor --每個(gè)session（會(huì)話）最多可以緩存多少個(gè)關(guān)閉掉的cursor  

 NAME                                 TYPE        VALUE 

------------------------------------ ----------- ------------------------------ 

 session_cached_cursors     integer 20

SQL> select count(*) from v$open_cursor; --是指當(dāng)前實(shí)例的某個(gè)時(shí)刻的打開(kāi)的cursor數(shù)目 

COUNT(*) 

----------  

108

SQL> SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR 

FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P 

WHERE A.STATISTIC# = B.STATISTIC# 

AND B.NAME = 'opened cursors current' 

AND P.NAME = 'open_cursors' 

GROUP BY P.VALUE;  
      
HIGHEST_OPEN_CUR  MAX_OPEN_CUR 

------------------------ ------------  

28     300 

HIGHEST_ OPEN CUR是實(shí)際打開(kāi)的cursors 的最大值，MAX_OPEN_ CUR是參數(shù)Open_cursors的設(shè)定值，如果二者太接近，甚至觸發(fā)eRA一01000錯(cuò)誤，那么你就應(yīng)該調(diào)大參數(shù)Open_cursors的設(shè)定 值。如果問(wèn)題依舊沒(méi)有解決，盲目增大Open_cursors也是不對(duì)的，這個(gè)時(shí)候你得檢查應(yīng)用程序的代碼是否合理，比如說(shuō)應(yīng)用程序是否打開(kāi)了游標(biāo)，卻沒(méi) 有在它完成工作后沒(méi)有及時(shí)關(guān)閉。以下語(yǔ)句可以幫助你確定導(dǎo)致游標(biāo)漏出的會(huì)話：

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 curent';  

同樣，session_cached_cursors的值也不是越大越好，我們可以通過(guò)下面兩條語(yǔ)句得出合理的設(shè)置。

SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';  
      
    NAME                                          VALUE 

--------------------------------------------------------------------------  

opened cursors cumulative             15095

opened cursors current               34

session cursor cache hits             12308

session cursor cache count             775 

cursor authentications               324

SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';  
    NAME                                          VALUE 

------------------------------- --------------------------------- ----------  

parse time cpu                   332

parse time elapsed                 1190

parse count (total)                9184

parse count (hard)                 1031

parse count (failures)               3 

session cursor cache hits就是系統(tǒng)在高速緩存區(qū)中找到相應(yīng)cursors的次數(shù)，parse count(total)就是總的解析次數(shù)，二者比值越高，性能越好。如果比例比較低，并且有較多剩余內(nèi)存的話，可以考慮加大該參數(shù)。

c、使用下面的sql判斷'session_cached_cursors' 的使用情況。如果使用率為100%則增大這個(gè)參數(shù)值。

SQL> SELECT 'session_cached_cursors' PARAMETER, 

       LPAD(VALUE, 5) VALUE, 

       DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE 

   FROM (SELECT MAX(S.VALUE) USED 

   FROM V$STATNAME N, V$SESSTAT S 

   WHERE N.NAME = 'session cursor cache count' 

   AND S.STATISTIC# = N.STATISTIC#), 

   (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors') 

   UNION ALL 

   SELECT 'open_cursors', 

      LPAD(VALUE, 5), 

      TO_CHAR(100 * USED / VALUE, '990') || '%' 

   FROM (SELECT MAX(SUM(S.VALUE)) USED 

   FROM V$STATNAME N, V$SESSTAT S 

   WHERE N.NAME IN ('opened cursors current', 'session cursor cache count') 

   AND S.STATISTIC# = N.STATISTIC# 

   GROUP BY S.SID), 

   (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');  
      
PARAMETER              VALUE      USAGE 

---------------------- ---------- -----  

session_cached_cursors 20     100%

open_cursors      300     16% 

當(dāng)我們執(zhí)行一條sql語(yǔ)句的時(shí)候,我們將會(huì)在shared pool產(chǎn)生一個(gè)library cache object,cursor就是其中針對(duì)于sql語(yǔ)句的一種library cache object.另外我們會(huì)在pga有一個(gè)cursor的拷貝,同時(shí)在客戶端會(huì)有一個(gè)statement handle,這些都被稱為cursor,在v$open_cursor里面我們可以看到當(dāng)前打開(kāi)的cursor和pga內(nèi)cached cursor.

session_cached_cursor
這個(gè)參數(shù)限制了在pga內(nèi)session cursor cache list的長(zhǎng)度,session cursor cache list是一條雙向的lru鏈表,當(dāng)一個(gè)session打算關(guān)閉一個(gè)cursor時(shí),如果這個(gè)cursor的parse count超過(guò)3次,那么這個(gè)cursor將會(huì)被加到session cursor cache list的MRU端.當(dāng)一個(gè)session打算parse一個(gè)sql時(shí),它會(huì)先去pga內(nèi)搜索session cursor cache list,如果找到那么會(huì)把這個(gè)cursor脫離list,然后當(dāng)關(guān)閉的時(shí)候再把這個(gè)cursor加到MRU 端.session_cached_cursor提供了快速軟分析的功能,提供了比soft parse更高的性能.

更新2：

OPEN_CURSORS是一個(gè)十分有趣的參數(shù)，經(jīng)常有DBA發(fā)現(xiàn)自己的系統(tǒng)中的OPEN CURSORS十分大。我們看一個(gè)例子：

SQL>select sid,value from v$sesstat a,v$statname b where a.statistic#=b.statistic# and name='opened cursors current' order by 2;

 SID       VALUE 

---------- ---------- 

5430     93 

3527     95 

4055     96 

4090     97 

2012     98 

1819     98 

5349     102 

1684     103 

1741     116 

4308     169 

1970     170 

1369     181 

4208     184 

887      214

5215     214 

3518     214 

868      214 

1770     215 

4050     215 

1809     231 

3010     235 

762      237 

731      471 

4013     1066 

2648     1152 

2255     1172 

2322     2620

我們看到這個(gè)系統(tǒng)的OPEN_CURSORS參數(shù)設(shè)置為3000，而會(huì)話中當(dāng)期打開(kāi)CURSOR最大的會(huì)話居然達(dá)到了2620。在一般人的眼里，CURSOR使用后就關(guān)閉了，OPENED CURSORS的數(shù)量應(yīng)該不會(huì)太多，難道應(yīng)用程序出現(xiàn)了CURSOR泄漏，有些應(yīng)用使用了CURSOR沒(méi)有關(guān)閉？實(shí)際上我們對(duì)OPEN CURSOR的概念一直存在誤解。認(rèn)為只有正在FETCH的CURSOR是OPEN狀態(tài)的，而一旦FETCH結(jié)束，CLOSE CURSOR后，CURSOR就處于關(guān)閉狀態(tài)了。因此一個(gè)會(huì)話中OPEN狀態(tài)的CURSOR數(shù)量應(yīng)該很少。事實(shí)上不是這樣的，某些CURSOR在程序中是已經(jīng)CLOSE了，但是oracle 為了提高CURSOR的性能，會(huì)對(duì)其進(jìn)行緩沖，這些緩沖的CURSOR，在程序中的關(guān)閉只是一個(gè)軟關(guān)閉，事實(shí)上，在會(huì)話中并未關(guān)閉，而是放在一個(gè)CURSOR緩沖區(qū)中。

在Oracle  9.2.0.5之前，OPEN_CURSORS參數(shù)的作用是雙重的，一方面是限制一個(gè)會(huì)話打開(kāi)的CURSORS的總量。另外一方面，OPEN_CURSORS參數(shù)也作為PL/SQL CURSOR的緩沖。在PL/SQL中，如果某個(gè)CURSOR關(guān)閉了，這個(gè)CURSOR不會(huì)馬上硬關(guān)閉，而是首先保存在CURSOR緩沖中。如果這個(gè)會(huì)話當(dāng)前打開(kāi)的CURSOR數(shù)量還沒(méi)有達(dá)到OPEN_CURSORS參數(shù)的值，那么就可以先保持OPEN狀態(tài)。如果當(dāng)前打開(kāi)的CURSOR數(shù)量已經(jīng)達(dá)到了OPEN_CURSORS參數(shù)的限制，那么首先會(huì)關(guān)閉一個(gè)被緩沖的，實(shí)際當(dāng)時(shí)并未打開(kāi)的CURSOR。如果緩沖池中的所有CURSOR都是實(shí)際打開(kāi)的，那么就會(huì)報(bào)ORA-1000，"maximum open cursors exceeded"。

Oracle  9.2.0.5以后，OPEN_CURSORS參數(shù)不再承擔(dān)PL/SQL緩沖的工作,PL/SQL中的SQL也可以使用SESSION_CACHED_CURSORS的會(huì)話緩沖了。這個(gè)參數(shù)就成為了一個(gè)純粹的限制。

雖然如此，OPEN_CURSORS參數(shù)仍然和CURSOR的緩沖機(jī)制密切相關(guān)，因?yàn)檫@個(gè)參數(shù)限制了當(dāng)前某個(gè)會(huì)話打開(kāi)CURSOR的最大值。設(shè)置一個(gè)較大的OPEN_CURSORS參數(shù)，可以避免出現(xiàn)ORA-1000，同時(shí)也可以讓會(huì)話緩沖更多的CURSOR，改善SQL解析的性能。不過(guò)這個(gè)參數(shù)設(shè)置的較大會(huì)占用較大的PGA空間，消耗一定的物理內(nèi)存。因此這個(gè)參數(shù)也不是設(shè)置的越大越好，一般的OLTP系統(tǒng)中，1000－3000就足夠了。在共享服務(wù)器模式的系統(tǒng)中，這個(gè)參數(shù)的設(shè)置要略微保守一些，因?yàn)檫@個(gè)參數(shù)越大，占用的SGA空間也就越大。

另外要注意的是，從Oracle  9.0開(kāi)始，這個(gè)參數(shù)就已經(jīng)是動(dòng)態(tài)的了，可以隨時(shí)動(dòng)態(tài)調(diào)整。