oradebug分析oracle hang或慢_sqlplus_prelim

  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         

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: (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2010-08-07 21:11:10.818
*** SESSION ID:(145.36) 2010-08-07 21:11:10.818
*** 2010-08-07 21:11:10.818
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : : --每列的註解：分為cnode sid sess_srno proc_ptr ospid wait_event
    <0/148/27/0x70e5e4a8/2543/SQL*Net message from client>   --會話148(持鎖會話）
 -- <0/146/84/0x70e5f478/2607/enq: TX - row lock contention> --會話146(等待鎖會話），競爭事件為：row lock contention
Other chains found:
Chain 2 : :
    <0/144/108/0x70e5ccf0/2614/jobq slave wait>
Chain 3 : :
    <0/145/36/0x70e5fc60/2622/No Wait>
Chain 4 : :
    <0/150/2/0x70e623e8/2338/Streams AQ: waiting for time man>
Chain 5 : :
    <0/151/1/0x70e5ec90/2319/Streams AQ: qmn coordinator idle>
Chain 6 : :
    <0/158/7/0x70e61c00/2336/Streams AQ: qmn slave idle wait>
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
====================

其內容意思大概如下

cnode--節點代號,如果為rac,其值就存在,單節點的值為0

sid---session的sid

sess_srno---session的serial#

proc_ptr--系統程式指向的address

ospid ----程式號

wait_event---session的等待事件

轉摘白大師部分節選
Hanganalyze是從Oracle 8i r2(8.1.6)開始提供的，其用法十分簡單：

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level ';

或者

ORADEBUG hanganalyze

比如：

sql>oradebug setmypid;

sql>oradebug hanganalyze 3;

對於：

      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' --透過prelim選項進入已經hang住（正常方式進不了sqlplus)的資料庫

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>