Diagnostics For Database Hang

Many times Oracle DBA's are in a situation when the database is hung and does not seem to be responding. In some scenarios, state is such that you cannot even connect to the sqlplus session. Majority people restart the database (Sometimes I wonder if this is due to the fact that most of us started working on Computer's on Microsoft Windows 🙂 ) and then log a ticket with Oracle support . They inturn happily inform us that "They do not have any diagnostic information to diagnose and resolve the issue and we need to wait for next occurrence to collect some diagnostic information)

Based on my experiences , I am writing this article to assist my fellow Oracle DBA's to diagnose the problem and collect the required information. So Let's Start.

1) First of all we need to ensure that this is really a database hung situation and not a slow database condition. This can be done by asking some questions to users.

a) Is a particular user complaining of database hang or its the condition for all the users. If one or few user are reporting then are these users executing a batch job?

b)Are you able to make new connections to database?

c)Also check if any initialisation parameter has been changed recently?

d)Check if any resource manager plan is in effect.

One more way to establish if database is hung is t0 try to query v$session_wait view to find events being waited on

select sid,event,seq#,p1,p2,p3 from V$session_wait where wait_time=0 and event not like '%message%';

This will give the events for all the waiting session. In case you see something like 'log file switch (archiving required)' then this problem is caused by archiving issue. See if there is free space in archiving destination.

Suppose this gives events like row cache enqueue or latches, then we need to gather Hanganalyze and Systemstate for the support.

Else it could be that you are experiencing a slow database. In this case use AWR or statspack to diagnose the issue. Look out for top timed events. In case you see Library Latch or shared pool latch consuming lot of time, then look at the Hard parses per sec section in Load profile.

2)Look at database alert log and see if any messages are present. In case you are facing Latching or Enqueue issues, then you might see errors like below

PMON failed to acquire latch, see PMON dump

Errors in file /u01/BDUMP/test10_pmon_12864.trc:

In this case you are required to upload tracefile (reported in alert log) to Oracle support.

Note : -Make sure that max_dump_file_size is set to unlimited so that the tracefile contains the complete data.

Coming back to Hanganalyze and Systemstate. Find details for them below

A)Hanganalyze

HANGANALYZE is used to determine if a session is waiting for a resource, and reports the relationships between blockers and waiters.

Use following syntax and take hanganalyze from two sessions at interval of 1 min

SQL>sqlplus "/ as sysdba"

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug hanganalyze 3

SQL>oradebug tracefile_name

Last command will report the tracefile name which has to be uploaded to Oracle support.

Alternatively you can use

SQL>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3';

In case you wish to understand how to interpret hanganalyze file, then use Metalink Note:215858.1: Interpreting HANGANALYZE trace files to diagnose hanging and performance problems

B)Systemstate

Systemstate is used to dump the process information which is useful for Oracle support to diagnose why the sessions are waiting.

For 9.2.0.6 and above gather systemstate as below

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug dump systemstate 266

SQL>oradebug tracefile_name

Last command will report the tracefile name which has to be uploaded to Oracle support. Perform this 2-3 times at interval of 1 min.

Again you can use

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266';

For Oracle 9.2.0.5 and less use level 10 instead of 266

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266';

Level 266 includes short stacks (Oracle function calls) which are useful for Oracle Developers to determine which Oracle function's are causing the problem. This is also helpful in matching existing bugs.

In case you are unable to connect to database then capture systemstate using below note

Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

Apart from this, following information can also be captured

a)Database alert log

b)AWR report /statspack report for 30-60 min during database hang

c)Output of OS tools to ensure that everything is fine at OS level.

E.g

$vmstat 2 20

This will capture 20 snapshots at 2 seconds interval. Look for CPU contention or swapping issues

In Addition to above , you can use utility called LTOM which has got some predefined rules based on which it determines that database is in Hung situation and takes systemstate and hanganalyze dump automatically .

Please refer to following Metalink note for more details

Note:352363.1: LTOM - The On-Board Monitor User Guide

In case you are able to narrow down to a blocking session manually, then you can very well take errorstack for the blocking process as below

connect / as sysdba

oradebug setospid 1234

oradebug unlimit

oradebug dump errorstack 3

wait 1 min

oradebug dump errorstack 3

wait 1 min

oradebug dump errorstack 3

oradebug tracefile_name

* In case the ospid for blocking session is 1234

Last command will report the tracefile name which has to be uploaded to Oracle support.

In case you are able to capture the above information, you stand 99% chance of getting solution . I have kept 1 % for the cases when Oracle Support will ask for setting up some events and waiting for Next Hang Occurence for getting more information.

6 thoughts on “Diagnostics For Database Hang

  1. Hi Piyush,

    Very good information on database hang.
    But i have few questions on this:

    -What does p1,p2,p3 represents in v$session_wait and what are their significanse here.
    – I have faced this situation many a times but i was not able to login to database when the hang occured, so what to do in that case, i.e how to generate dumps.

  2. Aarya,You can use P1,P2,P3 values to diagnose the parameters for wait event. e.g You see event ‘latch free’ as considerable wait event, then
    P1 = Latch address
    P2 = Latch number

    Now you can identify the latch name from V$LATCHNAME
    SELECT * FROM v$latchname WHERE latch# =P2;
    Moreover there can be situation where many sessions are waiting on same latch anf their P1 and P2 values are same say it points to Shared Pool Latch. From this we can be sure that some latching issue is causing the Database Hang.I will try to take up a case study sometime so that it can be more clear.And for your second question, you can refer to
    Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

    It is already mentioned in the post.In case of 10g, you can use prelim to connect to database.
    e.g
    sqlplus -prelim “/ as sysdba”

    Cheers
    Amit

  3. Very good information on database hang.
    But i have few questions on this:

    If database hung,we will not generate any reports,not running queries also.In this situation,what is the first step we ill take?

  4. You need to first check database alert log for any errors/issues. Sometimes errors like ORA-600 and ORA-7445 are reported which could be causing the hang. Also when hangs are caused by latching or enqueue issues such as “PMON failed to acquire latch”, then you will see that systemstate will be dumped automatically. This can be uploaded to metalink.

    Also use OS utilities like top,vmstat if there is CPU contention and OS logs (/var/log/messages on linux).
    In case your Database is hung in such a way that you cannot run queries, then you need to take Systemstate dump. You can use below note for same in 8i/9i

    Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

    In case of 10g, you can use prelim to connect to database.
    e.g
    sqlplus -prelim “/ as sysdba” and then take systemstate and hanganalyze

    -Amit

Leave a Reply