alter system set sga_target=0 scope=both;即可关闭SGA的自动管理功能。关闭以后需要手工给db_cache_size和shred_pool_size设置大小。但引起这个等待的原因很多,包括BUG也会引起。我也碰到过。我碰到后把SGA的自动管理功能关闭以后并没有解决。下面是引起这个事件的BUG报告 Bug 7462072 - Unnecessary "cursor: pin S wait on X" waits [ID 7462072.8] --------------------------------------------------------------------------------
修改时间 14-JUN-2010 类型 PATCH 状态 PUBLISHED Bug 7462072 Unnecessary "cursor: pin S wait on X" waits This note gives a brief overview of bug 7462072. The content was last updated on: 14-JUN-2010 Click here for details of each of the sections below.Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions < 11 Versions confirmed as being affected •10.2.0.3 •10.2.0.4
Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in •10.2.0.4.3 (Patch Set Update) •10.2.0.4 Patch 13 on Windows Platforms •10.2.0.5 (Server Patch Set) Symptoms: Related To: •Mutex Contention •Performance Affected (General) •Waits for "cursor: pin S wait on X" •(None Specified) Description There are certain scenarios where a session can wait for event 'cursor: pin S wait on X' even though the mutex was never acquired in exclusive mode. This causes an unnecessary 10ms wait for this waitevent.This same issue can affect 11g - see bug 7234778 for details of the 11g fix. HOOKS "WAITEVENT:cursor: pin S wait on X" LIKELYAFFECTS XAFFECTS_A201 AFFECTS=10.2.0.1 XAFFECTS_A202 AFFECTS=10.2.0.2 XAFFECTS_A203 AFFECTS=10.2.0.3 XAFFECTS_A204 AFFECTS=10.2.0.4 XAFFECTS_A2041 AFFECTS=10.2.0.4.1 Patch Set Update XAFFECTS_A2042 AFFECTS=10.2.0.4.2 Patch Set Update XAFFECTS_A2043 AFFECTS=10.2.0.4.3 Patch Set Update XAFFECTS_A2044 AFFECTS=10.2.0.4.4 Patch Set Update XAFFECTS_A2045 AFFECTS=10.2.0.4.5 Patch Set Update XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_MUTEX TAG_PERF FIXED_A2043 FIXED_A205 FIXED_WIN:A204P13 Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice. References Bug:7462072 (This link will only work for PUBLISHED bugs) Note:245840.1 Information on the sections in this article ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 相关的-------------------------------------------------------------------------------- 产品 --------------------------------------------------------------------------------•Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 返回页首
等待相应的sql语句是什么?
你使用的自动sga管理吧?
Bug 7462072 - Unnecessary "cursor: pin S wait on X" waits [ID 7462072.8] --------------------------------------------------------------------------------
修改时间 14-JUN-2010 类型 PATCH 状态 PUBLISHED
Bug 7462072 Unnecessary "cursor: pin S wait on X" waits
This note gives a brief overview of bug 7462072.
The content was last updated on: 14-JUN-2010
Click here for details of each of the sections below.Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected •10.2.0.3
•10.2.0.4
Platforms affected Generic (all / most platforms affected) Fixed:
This issue is fixed in •10.2.0.4.3 (Patch Set Update)
•10.2.0.4 Patch 13 on Windows Platforms
•10.2.0.5 (Server Patch Set)
Symptoms: Related To:
•Mutex Contention
•Performance Affected (General)
•Waits for "cursor: pin S wait on X"
•(None Specified)
Description
There are certain scenarios where a session can wait
for event 'cursor: pin S wait on X' even though the
mutex was never acquired in exclusive mode. This causes
an unnecessary 10ms wait for this waitevent.This same issue can affect 11g - see bug 7234778 for
details of the 11g fix.
HOOKS "WAITEVENT:cursor: pin S wait on X" LIKELYAFFECTS XAFFECTS_A201 AFFECTS=10.2.0.1 XAFFECTS_A202 AFFECTS=10.2.0.2 XAFFECTS_A203 AFFECTS=10.2.0.3 XAFFECTS_A204 AFFECTS=10.2.0.4 XAFFECTS_A2041 AFFECTS=10.2.0.4.1 Patch Set Update XAFFECTS_A2042 AFFECTS=10.2.0.4.2 Patch Set Update XAFFECTS_A2043 AFFECTS=10.2.0.4.3 Patch Set Update XAFFECTS_A2044 AFFECTS=10.2.0.4.4 Patch Set Update XAFFECTS_A2045 AFFECTS=10.2.0.4.5 Patch Set Update XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_MUTEX TAG_PERF FIXED_A2043 FIXED_A205 FIXED_WIN:A204P13 Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.
References
Bug:7462072 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 相关的--------------------------------------------------------------------------------
产品
--------------------------------------------------------------------------------•Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
返回页首