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

Editor to share with you how Oracle uses the hanganalyze command to analyze the database hang. I hope you will get something after reading this article. Let's discuss it together.

1. Several possibilities of Database hang

Oracle deadlocks or very high system loads such as cpu usage or other high lock waits can lead to system hang occupancy, such as a large number of DX locks.

Generally speaking, by system hang residence, we mean that the application is unresponsive, the ordinary sqlplus is almost inoperable, and so on.

two。 How to perform hang analysis? What is the level of hang analysis? How to choose level?

There are several kinds of level for hanganalyze:

10 Dump all processes (IGN state)

5 Level 4 + Dump all processes involved in wait chains (NLEAF state)

4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

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

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

How to choose level?

In general, it is not recommended to use hang analysis at a level above 3, as it may produce very large trace and may have some impact on the IO of the system.

Starting with oracle 9i, hanganalyze provides support for rac.

There are two ways:

1) ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level'

2) use the oradebug command

ORADEBUG setmypid

ORADEBUG setinst all

ORADEBUG-g def hanganalyze-for the usage of rac

Oradebug setmypid

Oradebug hanganalyze 3-non-rac environment

Usually when doing hang analysis, oracle suggests doing a dump of systemstate at the same time.

Oradebug SYSTEMSTATE dump level 2 level 2, which contains all the session information.

Sqlplus-prelim / as sysdba-10g can log in this way

Oradebug setospid

Oradebug unlimit

Oradebug dump systemstate 10

Add: sometimes we may also need a trace aix environment for a process, we can use the dbx command

Examples are as follows:

Dbx-a PID (where PID = any oracle shadow process)-View via ps-ef | grep xxx

Dbx () print ksudss (10)

Dbx () detach

3. How to interpret the trace file of hang analysis to obtain useful information?

* ACTION NAME: () 2010-03-1200 purl 04purl 01.497

* MODULE NAME: (sqlplus@S7_C_YZ_YZSJK (TNS V1-V3)) 2010-03-1200 purl 04purl 01.497-the module name is the same as v$session.module_name

* SERVICE NAME: (SYS$USERS) 2010-03-1200 04purl 01.497

* SESSION ID: (5184.45287) 2010-03-1200 04purl 01.497-- sid (5184) serial# (35287)

* 2010-03-1200 purl 04purl 01.497

=

HANG ANALYSIS:

=

Found 54 objects waiting for

-- from here, session 5210 blocks 54 objects

Open chains found:

Chain 1:-- from here on, the following session is blocked by the first 5210, usually one blocking the other!

--

--

Other chains found:-- the following session is also blocked by the front, but not by Open chains indirectly!

Chain 2::

Chain 3::

Chain 4::

Cycle 1:-- cycle is usually a deadlock. Generally speaking, it is very likely that it is the root cause of hang.

--

--

4. What are the differences between different versions of hang analysis? What are the similarities and differences between trace?

The following is the hanganalyze trace message format of oracle8~10g:

Oracle 8.x: [nodenum] / sid/sess_srno/session/state/start/finish/ [adjlist] / predecessor

Oracle 9i: [nodenum] / cnode/sid/sess_srno/session/ospid/state/start/finish/ [adjlist] / predecessor

Oracle 10g: [nodenum] / cnode/sid/sess_srno/session/ospid/state/start/finish/ [adjlist] / predecessor

Nodenum-- "A serial number generated by each session doing hanganalyze

Sid-"Session ID"

Sess_srno-"Serial#"

Ospid-"OS Process Id (v$process spid)

State-"State of the node"

Adjlist-- "adjacent node (Usually represents a blocker node)-- is usually a blocker.

Predecessor-- "predecessor node (Usually represents a waiter node)-- is usually blocked.

Cnode-"Node number is only available from 9i.

There are several values for state:

IN_HANG-"this state is a very dangerous state, usually characterized by a node trapped in an endless loop or hung. Generally speaking, when this happens, the temporary node of the node is in the same state, that is, adjlist

Examples are as follows:

[16] / 0/17/154/0x24617be0/26800/IN_HANG/29/32/ [185] / 19-from IN_HANG we can see that 185 is a neighbor node of 16, 185 is blocked by 16

[185] / 1/16/4966/0x24617270//IN_HANG/30/31/ [16] / 16-from here, 185 blocked 16 (16 is waiter)

LEAF-"is usually considered the key object of blockers." So how to be sure? In general, it is determined whether the session is blocker or waiter based on the following predecesor.

Examples are as follows:

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

[16] / 0/17/154/0x24617be0/26800/LEAF/29/30//19-- from here, 19 is waiter, so we think 17 blocks 20.

[19] / 0/20/13/0x24619830/26791/NLEAF/33/34/ [16] / 186

LEAF_NW-"similar to leaf but may take up cpu

NLEAF-"the session of this state is usually referred to as" stuck "session. That is, resources required by other session are being holding by it.

IGN-- "the session in this state usually handles the IDLE state, unless its adjlist exists, and if so, the session is waiting for another session."

IGN_DMP-"similar to IGN.

Examples are as follows:

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

[16] / 0/17/154/0x24617be0/26800/LEAF/29/30//19

[19] / 0/20/13/0x24619830/26791/NLEAF/33/34/ [16] / 186

[189] / 1/20/36/0x24619830//IGN/95/96/ [19] / none

[176] / 1/7/1/0x24611d80//IGN/75/76//none

-from above, 189 is waiting for 19, 19 is waiting for 16, and 176 is an idle session.

SINGLE_NODE,SINGLE_NODE_NW can be considered the same as LEAF,LEAF_NW, except that there are no dependent objects.

In this section, I generate two sessions based on scott users, simulating deadlock sessions (one update, one delete)

SQL > oradebug help

HELP [command] Describe one or all commands

SETMYPID Debug current process

SETOSPID Set OS pid of process to debug

SETORAPID ['force'] Set Oracle pid of process to debug

SHORT_STACK Dump abridged OS stack

DUMP [addr] Invoke named dump

DUMPSGA [bytes] Dump fixed SGA

DUMPLIST Print a list of available dumps

EVENT Set trace event in process

SESSION_EVENT Set trace event in session

DUMPVAR [level] Print/dump a fixed PGA/SGA/UGA variable

DUMPTYPE Print/dump an address with type info

SETVAR Modify a fixed PGA/SGA/UGA variable

PEEK [level] Print/Dump memory

POKE Modify memory

WAKEUP Wake up Oracle process

SUSPEND Suspend execution

RESUME Resume execution

FLUSH Flush pending writes to trace file

CLOSE_TRACE Close trace file

TRACEFILE_NAME Get name of trace file

LKDEBUG Invoke global enqueue service debugger

NSDBX Invoke CGS name-service debugger

-G Parallel oradebug command prefix

-R Parallel oradebug prefix (return output

SETINST Set instance list in double quotes

SGATOFILE Dump SGA to file; dirname in double quotes

DMPCOWSGA Dump & map SGA as COW; dirname in double quotes

MAPCOWSGA Map SGA as COW; dirname in double quotes

HANGANALYZE [level] [syslevel] Analyze system hang

FFBEGIN Flash Freeze the Instance

FFDEREGISTER FF deregister instance from cluster

FFTERMINST Call exit and terminate instance

FFRESUMEINST Resume the flash frozen instance

FFSTATUS Flash freeze status of instance

SKDSTTPCS Helps translate PCs to names

WATCH Watch a region of memory

DELETE watchpoint Delete a watchpoint

SHOW watchpoints Show watchpoints

CORE Dump core without crashing process

IPC Dump ipc information

UNLIMIT Unlimit the size of the trace file

PROCSTAT Dump process statistics

CALL [arg1]... [argn] Invoke function with arguments

SQL > oradebug hanganalyze 3

Hang Analysis in/ oracle/admin/orcl/udump/orcl_ora_2622.trc

SQL > exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

-bash-3.2$ more / oracle/admin/orcl/udump/orcl_ora_2622.trc

/ oracle/admin/orcl/udump/orcl_ora_2622.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = / oracle/product/10.2.0/db_1

System name: Linux

Node name: truerhel5

Release: 2.6.18-164.el5

Version: # 1 SMP Tue Aug 18 15:51:48 EDT 2009

Machine: x86_64

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 21

Unix process pid: 2622, image:oracle@truerhel5 (TNS V1-V3)

* SERVICE NAME: (SYS$USERS) 2010-08-07 21 Suzhou 1110.818

* SESSION ID: (145.36) 2010-08-07 21 purl 1110.818

* 2010-08-07 21! 11 purl 10.818

=

HANG ANALYSIS:

=

Open chains found:

Chain 1::-- Notes for each column: divided into cnode sid sess_srno proc_ptr ospid wait_event

-- session 148 (locked session)

-session 146( waiting for lock session), contention event is: row lock contention

Other chains found:

Chain 2::

Chain 3::

Chain 4::

Chain 5::

Chain 6::

Extra information that will be dumped at higher levels:

[level 4]: 1 node dumps-- [REMOTE_WT] [LEAF] [LEAF_NW]

[level 5]: 5 node dumps-- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]

[level 6]: 1 node dumps-- [NLEAF]

[level 10]: 13 node dumps-- [IGN]

State of nodes

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

[143] / 0/144/108/0x70f5dcf8/2614/SINGLE_NODE/1/2//none

[144] / 0/145/36/0x70f5f130/2622/SINGLE_NODE_NW/3/4//none

[145] / 0/146/84/0x70f60568/2607/NLEAF/5/8/ [147] / none

[147] / 0/148/27/0x70f62dd8/2543/LEAF/6/7//145

[149] / 0/150/2/0x70f65648/2338/SINGLE_NODE/9/10//none

[150] / 0/151/1/0x70f66a80/2319/SINGLE_NODE/11/12//none

[154] / 0/155/1/0x70f6bb60/2315/IGN/13/14//none

[155] / 0/156/1/0x70f6cf98/2313/IGN/15/16//none

[157] / 0/158/7/0x70f6f808/2336/SINGLE_NODE/17/18//none

[159] / 0/160/1/0x70f72078/2305/IGN/19/20//none

[160] / 0/161/1/0x70f734b0/2303/IGN/21/22//none

[161] / 0/162/1/0x70f748e8/2301/IGN/23/24//none

[162] / 0/163/1/0x70f75d20/2299/IGN/25/26//none

[163] / 0/164/1/0x70f77158/2297/IGN/27/28//none

[164] / 0/165/1/0x70f78590/2295/IGN/29/30//none

[165] / 0/166/1/0x70f799c8/2293/IGN/31/32//none

[166] / 0/167/1/0x70f7ae00/2291/IGN/33/34//none

[167] / 0/168/1/0x70f7c238/2289/IGN/35/36//none

[168] / 0/169/1/0x70f7d670/2287/IGN/37/38//none

[169] / 0/170/1/0x70f7eaa8/2285/IGN/39/40//none

=

END OF HANG ANALYSIS

=

The meaning of its content is as follows

Cnode-- node code. If it is rac, its value exists, and the value of a single node is 0.

Sid of sid---session

Serial# of sess_srno---session

The address pointed to by the proc_ptr-- system process

Ospid-process number

Wait event for wait_event---session

Partial excerpts from Master Zhuan Bai

Hanganalyze has been available since Oracle 8i R2 (8.1.6) and is very simple to use:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level'

Or

ORADEBUG hanganalyze

For example:

Sql > oradebug setmypid

Sql > oradebug hanganalyze 3

For:

10 Dump all processes (IGN state)

5 Level 4 + Dump all processes involved in wait chains (NLEAF state)

4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

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

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

-bash-3.2$ sqlplus-prelim'/ as sysdba'-- use the prelim option to access a database that already resides in hang (sqlplus cannot be accessed normally)

SQL*Plus: Release 10.2.0.1.0-Production on Sat Aug 7 21:17:42 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL > show parameter sga

ORA-01012: not logged on

SQL > conn / as sysdba

Prelim connection established

SQL >

After reading this article, I believe you have a certain understanding of "how Oracle uses hanganalyze commands to analyze database hang". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.