In this Document

Applies to:

Goal

Explain the steps necessary and the diagnostics to collect for a hang situation with messages such as :

in the alert log.

Fix

Issue Description

This issue occurs because the PMON process is unable to get a resource for a fixed period of time. This can be a serious issue and hang the database since PMON will be unable to perform. its normal maintenance tasks and if the blocker is not freed will halt database activity.

Diagnostics

In these cases it is necessary to collect the following diagnostics:

• Current RDA information. This will provide the Alert Log which is useful to identify the time of the messages and also to determine if there are any other errors at the time which might explain why PMON is unable to continue. Additionally an up to date current RDA provides a lot of additional information about the configuration of the database and performance metrics that may provide useful background to the problem.
• Systemstates and hanganalyze trace at the time of the problem
• AWR reports from immediately before, during and after the problem.
• PMON trace file from the time of the problem

Apart from the PMON Trace, the diagnostics are very similar to the data required for a 'standard' hang situation. To collect this information see:

Interpretation

• PMON Trace
  This file is the key start point for the analysis of the problem. It shows what resource PMON is unable to get and what is likely holding it. When used in conjunction with systemstates at the time of the probem, the cause of the issue can be determined and the reason for the holder keeping the resource for so long can be established.
  Example trace:
  
  

  *** SESSION ID:(115.1) 2009-12-09 06:37:09.015
  PMON unable to acquire latch 38ef52390 Child library cache level=5 child#=29 
          Location from where latch is held: kgldtld: 2child: 
          Context saved from call: 3305698714
          state=busy, wlstate=free
      waiters [orapid (seconds since: put on list, posted, alive check)]:
       49 (174, 1260337028, 174)
       29 (174, 1260337028, 174)
       27 (174, 1260337028, 174)
       70 (171, 1260337028, 171)
       9 (134, 1260337028, 134)
       waiter count=5
      gotten 1905490 times wait, failed first 80 sleeps 0
      gotten 18325 times nowait, failed: 4
    possible holder pid = 26 spid=202

  
  In this example, the trace tells us that the PMON process is unable to acquire the "Child library cache" latch:
  
  

  PMON unable to acquire latch 38ef52390 Child library cache level=5 child#=29 

  
  The possible holder process is shown as:
  

    possible holder pid = 26 spid=20

  
  With an accompanying systemstate, this holding process can be found and its activity checked to see whether the activity is appropriate.
  The second section of the PMON trace dumps part of the process state for the HOLDING process with a short stack.
  
  

  ----------------------------------------
  SO: 443803fb8, type: 2, owner: 0, flag: INIT/-/-/0x00
    (process) Oracle pid=26, calls cur/top: 4439f7178/443845450, flag: (0) -
              int error: 0, call error: 0, sess error: 0, txn error 0
    (post info) last post received: 549 0 4
                last post received-location: kslpsr
                last process to post me: 403c01830 1 6
                last post sent: 0 0 24
                last post sent-location: ksasnd
                last process posted by me: 403c01830 1 6
    (latch info) wait_event=0 bits=20
          Location from where call was made: kgldtld: 2child: 
          Context saved from call: 3305698714
      waiting for 38ef524d0 Child library cache level=5 child#=27 
          Location from where latch is held: kglpin: 
          Context saved from call: 0
          state=busy, wlstate=free
          waiters [orapid (seconds since: put on list, posted, alive check)]:
           58 (174, 1260337028, 174)
           26 (174, 1260337028, 174)
           46 (174, 1260337028, 174)
           waiter count=3
          gotten 1098798 times wait, failed first 71 sleeps 13
          gotten 19775 times nowait, failed: 918
          possible holder pid = 37 spid=16089
      on wait list for 38ef524d0
      holding (efd=16) 38ef52390 Child library cache level=5 child#=29 
          Location from where latch is held: kgldtld: 2child: 
          Context saved from call: 3305698714
          state=busy, wlstate=free
          waiters [orapid (seconds since: put on list, posted, alive check)]:
           49 (174, 1260337028, 174)
           29 (174, 1260337028, 174)
           27 (174, 1260337028, 174)
           70 (171, 1260337028, 171)
           9 (134, 1260337028, 134)
           waiter count=5
      Process Group: DEFAULT, pseudo proc: 390f99190
      O/S info: user: oracle, term: UNKNOWN, ospid: 2024
      OSD pid info: Unix process pid: 2024, image: oracle@ipaddress
      Short stack dump: 
  ksdxfstk()+36

  
  You can see that this is dumping information from Process 26 (the holder) here :
  
  

    (process) Oracle pid=26, calls cur/top: 4439f7178/443845450, flag: (0) -

  
  From lower down in this Process information, the holder is holding the 'Child library cache' latch:
  

   holding (efd=16) 38ef52390 Child library cache level=5 child#=29

  
  (this is what is blocking PMON) confirming that this is the holder process we are looking for (it is possible that due to other activity that the holder has changed).
  
  The next thing to determine is what the holder is actually doing - it will either be waiting for something (in which case it is not the base level holder) or on the CPU (in which case it is the blocker that is blocking the other process(es)).
  This holder session (pid 26) is waiting for:
  

   waiting for 38ef524d0 Child library cache level=5 child#=27
  

  which is a different Child (child#=27) of a different library cache latch (38ef524d0) .
  The possible holder of this child# 27 is:
  

   possible holder pid = 37 spid=16089

  
  This means that the holder that is blocking PMON is being blocked by another session (pid 37).
  The PMON trace does not recursively dump all holders until we reach the base holder, so in order to determine the final blocking in this case systemstates and hanganalyze dumps are required to find this; PMON trace does not contain all the information required to drill down to the root causein this case.
  
  
  Note: If the holder blocking PMON (pid 26) had been active on cpu then the PMON trace alone may have been sufficient to diagnose the issue since this would have been the base level blocker. However, in order to determine if there is any process movement, multiple systemstates are required, so it is always prudent to collect these so as to have all the required information.
  
  Additionally, you may be able to determine some more information just from the PMON trace. The fact that PMON has detected a long wait and flagged it with a trace and alert log entry is a good indication that something abnormal is occurring and there is a problem of some sort with the blocker. In this case PMON is waiting for 'Child library cache' so it follows that it is trying to parse something and do something that relates to the storing of a cursor (or similar) in the library cache area of the shared pool. The holder is holding this resource, so was there anything at the time of the problem that might have been going on to cause a parse or similar activity to take a long time? This sort of analysis may assist with finding a potential cause.

Known Bugs:

References