Shulou(Shulou.com)06/01 Report--

This article introduces you to the example analysis of hanganalyze, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The HANGANALYZE of ORACLE has an abnormal condition in the database, resulting in serious performance problems due to hang residence. The logs generated through the HANGANALYZE feature can help us quickly locate whether there are two or more process deadlocks and how many processes have been affected. To help us diagnose the problem with the database.

About HANGANALYZE:

HANGANALYZE uses internal kernel calls to determine if a session is waiting for a resource, and reports the relationships between blockers and waiters . Oracle introduced HANGANALYZE (8.1.6) at 8i and extended this feature in 9i to add cluster-wide HANGANALYZE analysis. This analysis is aimed at kernel-level resource contention, because oracle cannot detect and roll back one of the operations, which requires human intervention, and when a large number of such operations occur, the database may be completely HANG occupied.

Application scenarios:

1. The database cannot be opened by a full HANG, and the sqlplus reply cannot be connected. In this case, we need to use hanganalyze analysis and find the root cause to kill the call back process.

2. An object is occupied by numerous replies. The reply still exists after trying to kill all locks. You can only find the source session and kill the session.

How to use it:

3 Syntax Examples:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level';-(hanganalyze at answer level)

ORADEBUG hanganalyze-hanganalyze at the instance level

(cluster-wide)

ORADEBUG setmypid

ORADEBUG setinst all

ORADEBUG-g def hanganalyze

The sets the amount of additional information that will be extracted from the processes found by HANGANALYZE (ERROSTACK dump) based on the "STATE" of the node.

The levels are defined as follows:

10 Dump all processes (IGN state)

5 Level4+Dump all processes involved in wait chains (NLEAF state)-Level4+Dump all processes in the waiting chain (status is NLEAF)

4 Level3+Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)-Level3+Dump the blockers in the waiting chain (status is LEAF/LEAF_NW/IGN_DMP)

3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)-(process in LEVEL2+IN_HANG status)

1-2 Only HANGANALYZE output, no process dump at all

IN_HANG: if Session is in this state, Session encounters deadlock or is in hung state.

LEAF/LEAF_NW:LEAF/LEAF_NW: these Session are usually "blocker" or "slow" node waiting for some resources, and these node can be easily identified by the field "predecessor".

NLEAF: these sessions can often be thought of as blocked resources. It means that these Session are waiting for some Session resources. The blocker of the process can be easily defined through the field "adjlist".

IGN/IGN_DMP: this type of conversation is generally thought of as an idle session unless there is a node in its adjlist column. If it is a non-idle session, the node in its adjlist is waiting for other node to release resources.

SINGLE_NODE/SINGLE_NODE_NW: similar to an idle session

The key point that we need to pay attention to is the reply in the state of IN_HANG. Moreover, in general, oracleOracle recommends not to use tracing above level 3. If the Level is too large, it will generate a large number of trace files and affect the performance of the system.

Test (native test environment stand-alone 10g environment):

Let's first cause the database to generate some enq locks:

SQL > select s.sidwery s.serialcalendar where s.sid=l.session_id. Username journal S.logonflowers time from v$session sdirection lockedwriting object l where s.sid=l.session_id

SID SERIAL# USERNAME LOGON_TIM

-

152 20 TEST 07-DEC-11

158 38 TEST 07-DEC-11

140 27 TEST 07-DEC-11

142 18 TEST 07-DEC-11

SQL > select addr,sid,type,lmode,request,block from v$lock where sid in (152142158140)

ADDR SID TY LMODE REQUEST BLOCK

-

2CFB9BC0 140 TX 0 40

2CFB9C1C 158 TX 0 4 0

2CFB9C78 142 TX 0 4 0

2B8F3A90 152 TM 3 0 0

2B8F3B3C 140 TM 3 0 0

2B8F3BE8 158 TM 3 0 0

2B8F3C94 142 TM 3 0 0

2B92BC60 152 TX 60 1

2B954A00 158 TX 6 00

2B954F1C 140 TX 6 0 0

2B966C68 142 TX 6 0 0

Next, do a hanganalyze analysis:

SQL > oradebug hanganalyze 3

Hang Analysis in/ oracle/app/admin/orcl/udump/orcl_ora_8153.trc

Open the trace file to view:

The trace file is divided into three parts:

Basic information:

ORACLE_HOME = / oracle/app/product/10.2.0/db_1

System name: Linux

Node name: product

Release: 2.6.9-78.ELsmp

Version: # 1 SMP Wed Jul 9 15:39:47 EDT 2008

Machine: i686

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 21

Unix process pid: 8153, image: oracle@product (TNS V1-V3)

Part II: HANG ANALYSIS:

Open chains found:

Chain 1::

--

Other chains found:

Chain 2::

Chain 3::

Chain 4::

Chain 5::

Chain 6::

Chain 7::

Chain 8::

The hanganalyze report is divided into many pieces, and the session fragment information is always described in detail by a header that is extracted. The information for Oracle8i and 9i is slightly different:

Oracle 8.x chain header:

Oracle9i chain header:

:

First of all, understand the relevant meaning of each field:

Sid is Session ID.

Sess_srno is serial#.

Proc_ptr is the Process Pointer understood as the process pointer address

Ospid is OS Process ID.

Cnode is for Node Id,Oracle9i only.

Wait is a parameter indicating that it is waiting

Here we can clearly see that the answer 140is held by the hang, and the answer 152is the blocker, the source of the blocking, which is in line with the information found in the enq lock.

Part III: state of node

State of nodes

([nodenum] / cnode/sid/sess_srno/session/ospid/state/start/finish/ [adjlist] / predecessor):

[135] / 0/136/1/0x2cef0e14/7235/SINGLE_NODE/1/2//none

[136] / 0/137/1/0x2cef20c8/7233/SINGLE_NODE/3/4//none

[139] / 0/140/27/0x2cef58e4/7991/NLEAF/5/8/ [151] / none

[140] / 0/141/15/0x2cef6b98/8199/SINGLE_NODE_NW/9/10//none

[141] / 0/142/18/0x2cef7e4c/8052/NLEAF/11/12/ [151] / none

[143] / 0/144/357/0x2cefa3b4/8201/SINGLE_NODE/13/14//none

[149] / 0/150/1/0x2cf013ec/7219/SINGLE_NODE/15/16//none

[151] / 0/152/20/0x2cf03954/7362/LEAF/6/7//139

[154] / 0/155/1/0x2cf07170/7215/IGN/17/18//none

[155] / 0/156/1/0x2cf08424/7213/IGN/19/20//none

[157] / 0/158/38/0x2cf0a98c/7528/NLEAF/21/22/ [151] / none

[158] / 0/159/26/0x2cf0bc40/7986/IGN/23/24//none

[159] / 0/160/1/0x2cf0cef4/7203/IGN/25/26//none

[160] / 0/161/1/0x2cf0e1a8/7205/IGN/27/28//none

[161] / 0/162/1/0x2cf0f45c/7201/IGN/29/30//none

[162] / 0/163/1/0x2cf10710/7197/IGN/31/32//none

[163] / 0/164/1/0x2cf119c4/7199/IGN/33/34//none

[164] / 0/165/1/0x2cf12c78/7195/IGN/35/36//none

[165] / 0/166/1/0x2cf13f2c/7193/IGN/37/38//none

[166] / 0/167/1/0x2cf151e0/7191/IGN/39/40//none

[167] / 0/168/1/0x2cf16494/7189/IGN/41/42//none

[168] / 0/169/1/0x2cf17748/7187/IGN/43/44//none

[169] / 0/170/1/0x2cf189fc/7185/IGN/45/46//none

This part is also used to describe the state of a reply process.

Nodenum is hanganalyze.

The number you customize to record these conversations starts at 0.

State is the state of node

Adjlist is a neighboring node (usually represents a blocker node)

Predecessor is Predecessor node, which usually stands for a waiter node

This is the end of the sample analysis on hanganalyze. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.