ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 修改open_cursors和session_cached_cursors的参数值

修改open_cursors和session_cached_cursors的参数值

原创 Linux操作系统 作者:mengzhaoliang 时间:2009-03-20 16:24:16 0 删除 编辑

 

用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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-573243/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2008-01-30

  • 博文量
    335
  • 访问量
    2942863