oracle資料庫 參數open_cursors和session_cached_cursor詳解！

標籤：union   協助   伺服器   sql   order by   com   大小   alt   to_char   

open_cursors

每個session（會話）最多能同時開啟多少個cursor（遊標）

session_cached_cursor

每個session（會話）最多可以緩衝多少個關閉掉的cursor

SQL> show parameter open_cursors           --每個session（會話）最多能同時開啟多少個cursor（遊標）    NAME                                 TYPE        VALUE  ------------------------------------ ----------- ------------------------------  open_cursors                         integer     300  SQL> show parameter session_cached_cursor  --每個session（會話）最多可以緩衝多少個關閉掉的cursor    NAME                                 TYPE        VALUE  ------------------------------------ ----------- ------------------------------  session_cached_cursors               integer     20  SQL> select count(*) from v$open_cursor;  --是指當前執行個體的某個時刻的開啟的cursor數目  COUNT(*)  ----------         108
1、open_cursors與session_cached_cursor有什麼作用？
open_cursors設定每個會話session最多能同時開啟多少個cursor（遊標）。session_cached_cursor 設定每個session（會話）最多可以緩衝多少個關閉掉的cursor。我們得看看oracle如何執行每個sql語句。

通過分析我們可以得出

a、兩個參數之間沒有任何關係，相互也不會有任何影響。

b、兩個參數有著相同的作用：讓後續相同的sql語句不在開啟遊標，從而避免軟解析過程來提供應用程式的效率。

2、如何正確合理設定參數的大小?
a、如果Open_cursors設定太小，對系統效能不會有明顯改善，還可能觸發ORA-O1000：m~imum open CUrsOrs exceeded．的錯誤。如果設定太大，則無端消耗系統記憶體。我們可以通過如下的sql語句查看你的設定是否合理：

 SQL> SELECT MAX(A.VALUE) HIGHEST_OPEN_CUR, C.VALUE MAX_OPEN_CUR        2    FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER C        3   WHERE A.STATISTIC# = B.STATISTIC#        4     AND B.NAME = ‘opened cursors current‘        5     AND C.NAME = ‘open_cursors‘        6   GROUP BY C.VALUE;            HIGHEST_OPEN_CUR MAX_OPEN_CUR      ---------------- --------------------                    28 300  
HIGHEST_ OPEN CUR是實際開啟cursors的最大值，MAX_OPEN_ CUR是參數Open_cursors的設定值，如果二者太接近，甚至觸發eRA一01000錯誤，那麼你就應該調大參數Open_cursors的設定值。如果還不能解決問題，盲目增大Open_cursors也是不對的，這時你得檢查應用程式的代碼是否合理，比如說應用程式是否開啟了遊標，卻沒有在它完成工作後沒有及時關閉。以下語句可以協助你確定導致遊標漏出的會話：

   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的值也不是越大越好，我們可以通過下面兩條語句得出合理的設定。

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就是系統在快取區中找到相應cursors的次數，parse count(total)就是總的解析次數，二者比值越高，效能越好。如果比例比較低，並且有較多剩餘記憶體的話，可以考慮加大該參數。
c、使用下面的sql判斷‘session_cached_cursors‘ 的使用方式。如果使用率為100%則增大這個參數值。

SQL> SELECT ‘session_cached_cursors‘ PARAMETER,        2         LPAD(VALUE, 5) VALUE,        3         DECODE(VALUE, 0, ‘ n/a‘, TO_CHAR(100 * USED / VALUE, ‘990‘) || ‘%‘) USAGE        4    FROM (SELECT MAX(S.VALUE) USED        5            FROM V$STATNAME N, V$SESSTAT S        6           WHERE N.NAME = ‘session cursor cache count‘        7             AND S.STATISTIC# = N.STATISTIC#),        8         (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘session_cached_cursors‘)        9  UNION ALL       10  SELECT ‘open_cursors‘,       11         LPAD(VALUE, 5),       12         TO_CHAR(100 * USED / VALUE, ‘990‘) || ‘%‘       13    FROM (SELECT MAX(SUM(S.VALUE)) USED       14            FROM V$STATNAME N, V$SESSTAT S       15           WHERE N.NAME IN       16                 (‘opened cursors current‘, ‘session cursor cache count‘)       17             AND S.STATISTIC# = N.STATISTIC#       18           GROUP BY S.SID),       19         (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘open_cursors‘);            PARAMETER              VALUE      USAGE      ---------------------- ---------- -----      session_cached_cursors    20       100%      open_cursors             300        16%  

當我們執行一條sql語句的時候,我們將會在shared pool產生一個library cache object,cursor就是其中針對於sql語句的一種library cache object.另外我們會在pga有一個cursor的拷貝,同時在用戶端會有一個statement handle,這些都被稱為cursor,在v$open_cursor裡面我們可以看到當前開啟的cursor和pga內cached cursor.

session_cached_cursor
這個參數限制了在pga內session cursor cache list的長度,session cursor cache list是一條雙向的lru鏈表,當一個session打算關閉一個cursor時,如果這個cursor的parse count超過3次,那麼這個cursor將會被加到session cursor cache list的MRU端.當一個session打算parse一個sql時,它會先去pga內搜尋session cursor cache list,如果找到那麼會把這個cursor脫離list,然後當關閉的時候再把這個cursor加到MRU 端.session_cached_cursor提供了快速軟分析的功能,提供了比soft parse更高的效能.

OPEN_CURSORS是一個十分有趣的參數，經常有DBA發現自己的系統中的OPEN CURSORS十分大。我們看一個例子：

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

我們看到這個系統的OPEN_CURSORS參數設定為3000，而會話中當期開啟CURSOR最大的會話居然達到了2620。在一般人的眼裡，CURSOR使用後就關閉了，OPENED CURSORS的數量應該不會太多，難道應用程式出現了CURSOR泄漏，有些應用使用了CURSOR沒有關閉？實際上我們對OPEN CURSOR的概念一直存在誤解。認為只有正在FETCH的CURSOR是OPEN狀態的，而一旦FETCH結束，CLOSE CURSOR後，CURSOR就處於關閉狀態了。因此一個會話中OPEN狀態的CURSOR數量應該很少。事實上不是這樣的，某些CURSOR在程式中是已經CLOSE了，但是Oracle 為了提高CURSOR的效能，會對其進行緩衝，這些緩衝的CURSOR，在程式中的關閉只是一個軟關閉，事實上，在會話中並未關閉，而是放在一個CURSOR緩衝區中。

在Oracle  9.2.0.5之前，OPEN_CURSORS參數的作用是雙重的，一方面是限制一個會話開啟的CURSORS的總量。另外一方面，OPEN_CURSORS參數也作為PL/SQL CURSOR的緩衝。在PL/SQL中，如果某個CURSOR關閉了，這個CURSOR不會馬上硬關閉，而是首先儲存在CURSOR緩衝中。如果這個會話當前開啟的CURSOR數量還沒有達到OPEN_CURSORS參數的值，那麼就可以先保持OPEN狀態。如果當前開啟的CURSOR數量已經達到了OPEN_CURSORS參數的限制，那麼首先會關閉一個被緩衝的，實際當時並未開啟的CURSOR。如果緩衝池中的所有CURSOR都是實際開啟的，那麼就會報ORA-1000，"maximum open cursors exceeded"。

Oracle  9.2.0.5以後，OPEN_CURSORS參數不再承擔PL/SQL緩衝的工作,PL/SQL中的SQL也可以使用SESSION_CACHED_CURSORS的會話緩衝了。這個參數就成為了一個純粹的限制。

雖然如此，OPEN_CURSORS參數仍然和CURSOR的緩衝機制密切相關，因為這個參數限制了當前某個會話開啟CURSOR的最大值。設定一個較大的OPEN_CURSORS參數，可以避免出現ORA-1000，同時也可以讓會話緩衝更多的CURSOR，改善SQL解析的效能。不過這個參數設定的較大會佔用較大的PGA空間，消耗一定的實體記憶體。因此這個參數也不是設定的越大越好，一般的OLTP系統中，1000－3000就足夠了。在共用伺服器模式的系統中，這個參數的設定要略微保守一些，因為這個參數越大，佔用的SGA空間也就越大。

另外要注意的是，從Oracle  9.0開始，這個參數就已經是動態了，可以隨時動態調整。

oracle資料庫 參數open_cursors和session_cached_cursor詳解！