admin 管理员组

文章数量: 1184232

用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

本文标签： 参数 Oracle sessioncachedcursors opencursors

版权声明：本文标题：oracle 11g open_cursors 修改,修改open_cursors和session_cached_cursors的参数值 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1754939539a3052323.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。