用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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-573243/