oracle rac hang 11.2.0.3.7 hpux 11.31

whiterain

Hi:
        oracle rac 11.2.0.3.7
        hpux 11.31
        superdome 1
        格式化了trace文件后：

1553:                                    
     Cmd: Select
1554:waiting for 'library cache: mutex X'[Mutex 7f42]
1555:waiting for 'library cache: mutex X'[Mutex 7f42]
1556:                                    
     Cmd: Select
1557:waiting for 'SQL*Net message from client'
1558:waiting for 'library cache: mutex X'[Mutex 7f42]
     Cmd: Insert
1559:waiting for 'library cache: mutex X'[Mutex 7f42]
     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

whiterain

想发trace文件，太大了。不知道咋发？

winkey

以附件的形式发！！！

Liu Maclean(刘相兵

opatch lsinventory 再确认下

Liu Maclean(刘相兵

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

Liu Maclean(刘相兵

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