修改open_cursors和session_cached_cursors的引數值

 

用oracle 的ADDM生產了報告，其中建議修改open_cursors和session_cached_cursors的引數值。

如：

FINDING 3: 15% impact (1673 seconds)
------------------------------------
Soft parsing of SQL statements was consuming significant database time.

   RECOMMENDATION 1: Application Analysis, 15% benefit (1673 seconds)
      ACTION: Investigate application logic to keep open the frequently used
         cursors. Note that cursors are closed by both cursor close calls and
         session disconnects.

   RECOMMENDATION 2: DB Configuration, 15% benefit (1673 seconds)
      ACTION: Consider increasing the maximum number of open cursors a session
         can have by increasing the value of parameter "open_cursors".
      ACTION: Consider increasing the session cursor cache size by increasing
         the value of parameter "session_cached_cursors".
      RATIONALE: The value of parameter "open_cursors" was "300" during the
         analysis period.
      RATIONALE: The value of parameter "session_cached_cursors" was "20"
         during the analysis period.

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Contention for latches related to the shared pool was consuming
               significant database time. (30% impact [3316 seconds])
         INFO: Waits for "latch: library cache" amounted to 12% of database
               time.
               Waits for "library cache pin" amounted to 4% of database time.
               Waits for "latch: shared pool" amounted to 13% of database
               time.
         SYMPTOM: Wait class "Concurrency" was consuming significant database
                  time. (36% impact [3882 seconds])

 

SQL> show parameter cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
open_cursors                         integer     300
session_cached_cursors               integer     20
SQL>

alter system set open_cursors=1500 scope=both;

alter system set session_cached_cursors=100 scope=spfile;  （初始化檔案使用了spfile）

 

open_cursors:該引數含義是同一個session同時開啟最多在使用的遊標數。
在Oracle10.2.0.1.0版本中預設為300；

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