引數 session_cached_cursors 與 open_cursors詳解

一、OPEN_CURSORS

    就是一個session同時開啟的cursor的數量上限。

   確定open_cursors
    select max(count(*)) max_cacheable_cursors
      from (select p.kglobt18 schema# -- parsing schema number
              from sys.x$kglcursor p
             where p.kglobt12 > 2 -- enough parse_calls
            union all
            select s.kglntsnm schema# -- authorized schema number
              from sys.x$kglcursor c, sys.x$kglsn s
             where c.kglobt12 > 2
               and s.kglhdadr = c.kglhdadr)
     group by schema#

二、SESSION_CACHED_CURSORS

     就是說一個session可以快取多少個cursor，讓後續相同的SQL語句不再開啟遊標，從而避免軟解析的過程來提高效能。(繫結變數是解決硬解析的問題)，軟解析同硬解析一樣，比較消耗資源。所以這個引數非常重要。

    oracle有一個概念，那就是session cursor cache，中文描述就是有一塊記憶體區域，用來儲存關閉了的cursor。當一個cursor關閉之後，oracle會檢查這個cursor的request次數是否超過3次，如果超過了三次，就會放入session cursor cache，這樣在下次parse的時候，就可以從session cursor cache中找到這個statement, session cursor cache的管理也是使用LRU。

    session_cached_cursors這個引數是控制session cursor cache的大小的。session_cached_cursors定義了session cursor cache中儲存的cursor的個數。這個值越大，則會消耗的記憶體越多。

    另外檢查這個引數是否設定的合理，可以從兩個statistic來檢查。

SQL> select name,value from v$sysstat where name like '%cursor%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                          21577316
opened cursors current                                                 2399
session cursor cache hits                                           9992832
session cursor cache count                                             8254
cursor authentications                                               434123

SQL> select name,value from v$sysstat where name like '%parse%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                      1400526
parse time elapsed                                                 13979044
parse count (total)                                                20982181
parse count (hard)                                                  1396983
parse count (failures)                                                10958

session cursor cache hits 和parse count（total） 就是總的parse次數中，在session cursor cache中找到的次數。所佔比例越高，效能越好。如果比例比較低，並且有剩餘記憶體的話，可以考慮加大該引數。

    Oracle 9i及以前，該引數預設是0，10G上預設是20。

    設定pga端的cache list的長度，當session_cached_cursors設定為0時，pga的cache list長度為0，這時候當sga中的cursor關閉的時候它相關的library cache handle的lock位被清0，從v$open_cursor裡看不到這個被關閉的cursor，它服從於shared pool的lru機制，當shared pool需要新的buffer空間時，它將會被flush出shared pool。當session_cached_cursors設定為非0值時，pga的cache list長度為session_cached_cursors值的大小，同時pga cache list將會保留一份複製，這時候即使sga中的cursor關閉的時候它相關的library cache handle始終被加了null mode lock，當shared pool空間緊張時library cache handle始終將會被保留在shared pool中，而新的應用訪問這個cursor的時候會直接去自己的pga cache list裡面搜尋。

    cursor_space_for_time：

    當設定了session_cached_cursors為非0值後，如果cursor_space_for_time值被設為false，那麼當shared pool空間緊張時，雖然library cache handle不會被flush出去，但是它指向的library cached object（lco，其中包含了handle和children handle的地址，許可權，型別，狀態，指向kgl block的指標，其中kgl block包含了真正的程式碼段和執行計劃等內容）將會被flush出去而不管它相關的cursor有沒關閉，如果需要lco的時候將要reloads。如果cursor_space_for_time值被設為true，那麼當cursor在開啟狀態下，handle指向的lco將不會被flush出shared pool，這樣就可以降低reloads出現的頻率。不過對於sql共享做的不好的資料庫，設定cursor_space_for_time將會帶來一些問題，share pool可能會出現04031的錯誤。

    gets：

    當試圖parse一句sql時，oracle要先獲得一個handle，在handle上載入一個lock，gets表示handle request times。

    pin：

    當獲得handle後，定位到lco，然後pin住lco使它在被執行的時候不被flush出去。

二、引用ASKTOM相關內容如下

Hi Tom
 Can you explain briefly the difference between soft and hard parse?
 你能不能給我詳細介紹一下軟解析與硬解析的區別啊?
and we said...
 Here is a long winded answer, it is extracted in part from a new book oming out soon "beginning Oracle programming" that I collaborated on:
 這是一個很長的解釋,是從我與幾個同事一起合著的新書<>中提取出來的.