oracle rac hang 11.2.0.3.7 hpux 11.31
Hi:oracle rac 11.2.0.3.7
hpux 11.31
superdome 1
格式化了trace文件后：
1553:
Cmd: Select
1554:waiting for 'library cache: mutex X'
1555:waiting for 'library cache: mutex X'
1556:
Cmd: Select
1557:waiting for 'SQL*Net message from client'
1558:waiting for 'library cache: mutex X'
Cmd: Insert
1559:waiting for 'library cache: mutex X'
Cmd: Insert
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate. (The holder may have released the resource before we
dumped the state object tree of the blocking process).
o Lines with 'Enqueue conversion' below can be ignored *unless*
other sessions are waiting on that resource too. For more, see
http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html#enqcnv)
Resource Holder State
Mutex 7f42 250: 250: is waiting for Mutex 435c7f42
Rcache object=c0000062bb720dc8, 325: 325: is waiting for 250:
Mutex 435c7f42 ??? Blocker
Rcache object=c0000062bd090b20, 443: 443: is waiting for 250:
LOAD: c000003b9e270530 626: 626: is waiting for 250:
Rcache object=c0000038640b3c30, ??? Blocker
LOAD: c000004c021f7930 345: 345: is waiting for 250:
Rcache object=c0000062bd0862d0, 45: 45: is waiting for 250:
LOAD: c000005ad4415858 675: 675: is waiting for 250:
Rcache object=c0000062bd20a858, 1128: 1128: is waiting for 250:
LOAD: c000003e58b733d0 908: 908: is waiting for 250:
LOAD: c00000471e93adb8 681: 681: is waiting for 250:
LOAD: c0000035513caa08 964: 964: is waiting for 250:
LOAD: c000004273bcc8b8 110: 110: is waiting for 250:
Rcache object=c0000062bb7211f8, 313: 313: is waiting for 250:
Rcache object=c0000062bd08f630, ??? Blocker
Rcache object=c0000062bb71f8d8, 1085: 1085: is waiting for 250:
Rcache object=c0000062bd204550, ??? Blocker
Rcache object=c0000062bd203060, ??? Blocker
LOAD: c0000037025a2708 1243: 1243: is waiting for 250:
LOAD: c000003de317f250 ??? Blocker
LOAD: c000005432cd7fc0 1334: 1334: is waiting for 250:
LOAD: c0000035d15cdac0 ??? Blocker
LOAD: c000005a2955dd90 1349: 1349: is waiting for 250:
LOAD: c0000036444ff920 ??? Blocker
LOAD: c0000039820865e0 136: 136: is waiting for 250:
LOAD: c000004ce9308438 1408: 1408: is waiting for 250:
Rcache object=c0000062bd1f2250, ??? Blocker
LOAD: c0000058aa5a7950 1457: 1457: is waiting for 250:
LOAD: c0000041997a55b0 1466: 1466: is waiting for 250:
LOAD: c0000042e594fb48 1473: 1473: is waiting for 250:
Object Names 想发trace文件，太大了。不知道咋发？
以附件的形式发！！！ opatch lsinventory 再确认下 Mutex Type Location Sleeps Wait Time (ms)
Library Cache kglhdgh1 64 424,223,273 0
Library Cache kglhbh1 63 201,814 0
where =kglhdgh1 的library cache: mutex X
一开始我看你的cursor_sharing=exact ，但后来发现有一些SYS_B_0，即实际SQL有用cursor_sharing=FORCE
ODM FINGDING:
Bug 12797420 - "library cache: mutex X" waits on DB instance handle with CURSOR_SHARING (Doc ID 12797420.8)
该bug 号称在11.2.0.3.3 Database Patch Set Update中已修复
"library cache: mutex X" waits may be seen with many sessions after the
DB instance handle if CURSOR_SHARING is set to FORCE (or SIMILAR).
kglhdgh waits only DBInstance handle
Rediscovery Notes:
"library cache: mutex X"
Wait parameters show waits for the DB instance handle or its hash chain
CURSOR_SHARING is enabled
The top mutex sleep location is "kglhdgh1"
Workaround
None other than avoid cursor_sharing.
Getting a Fix
Use one of the "Fixed" versions listed above
(for Patch Sets / bundles use the latest version available as
contents are cumulative - the "Fixed" version listed above is
the first version where the fix is included)
@INTERNAL PROBLEM DESCRIPTION:
@The problem i noticed with their test was that the contention was always on a
@bucket mutex and the contents of the bucket included the DB instance handle (k
@glsghd). Many of the stacks indicated that the waits were coming from kglLockI
@nstance. This is a get by hash lookup and this was the cause of the kglhdgh co
@ntention.
@
@In kkspcsc0, we first get a NULL lock on the mapped handle(the actual sql text
@) and then lookup the base handle(the one with literal replacement done). Thei
@r library cache dump was huge(10G) with lots of different handles for the same
@ sql with different binds. So this was causing them to be loaded most of time.
@ And everytime before we call the load callback, we get the DBInstace lock in
@kglLoadonLock. The DBInstance lock is only needed in the case of an ADG. Getti
@ng the instance lock wasnt even necessary for the mapped handles.
@
@INTERNAL FIX DESCRIPTION:
@The fix i had was to pass in a kglds flag to indicate instance lock get needs
@to be skipped.
@
@BACKPORT FEASIBLE:
@Yes
@
@FORWARD MERGE REQUIRED:
@No (merged to main branch)
@
REDISCOVERY INFORMATION:
If you see kglhdgh contention on the DB instance handle and cursor_sharing is s
et to FORCE, this could be a possible cause
页:
[1]