oracle 11g open_cursors 修改,修改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