oradebug hanganalyze

最新推荐文章于 2017-05-02 17:10:42 发布

crazylili1000

最新推荐文章于 2017-05-02 17:10:42 发布

阅读量133

点赞数

文章标签：数据库

        Oracle数据库“真的”hang住了，可以理解为数据库内部发生死锁。因为普通的DML死锁，Oracle服务器会自动监测它们的依赖关系，并回滚其中一个操作，终止这种相互等待的局面。而当这种死锁发生在争夺内核级别的资源（比如说是pins或latches）时，Oracle并不能自动的监测并处理这种死锁。其实很多时候，数据库并没有hang住，而只是由于数据库的性能问题，处理的时间比较长而已。
        Hanganalyze工具使用内核调用，检测会话在等待什么资源，报告占有者和等待者的相互关系。另外，它还会将一些比较”interesting”的进程状态dump出来，这个取决于我们使用hanganalyze的分析级别。

$ sqlplus "/as sysdba"
SQL> oradebug setopid                  -- V$PROCESS.SPID
SQL> oradebug setorapid                 -- V$PROCESS.PID
SQL> oradebug setmyid                                -- current session
SQL> oradebug setinst all                             -- RAC
SQL> oradebug unlimit                                  -- dump file size unlimit
SQL> oradebug dump systemstate 266
SQL> oradebug hanganalyze 3
SQL> oradebug -g def hanganalyze 3         -- RAC

ORADEBUG hanganalyze
等同于
ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level ';

LEVEL参数：
     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

        如果Level过大的话会产生大量的跟踪文件并影响系统的I/O性能，建议不要采用3级以上的跟踪。Hanganalyze报告会分作许多片断，会话片断信息总是由一个header详尽描述被提取的的会话信息。

      HANGANALYZE跟踪文件分析：跟踪文件中最关键的部分是STATE OF NODES。对于9i该部分的条目结构为：
[nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor

nodenum：定义每个session的序列号
sid：session的sid
sess_srno：session的Serial＃
ospid：OS的进程ID
state：node的状态
adjlist：表示blocker node
predecessor：表示waiter node

State有如下几种状态：

IN_HANG：如果Session处于这种状态，表示Session遇到deadlock或者处于hung状态。
LEAF/LEAF_NW：这些Session通常是“blocker”或者是等待某些资源的“slow” node，通过字段“predecessor” 可以很容易标识出这些node。
NLEAF：这些Session通常被认为是“stuck”会话，意味着这些Session在等待某些 Session的资源。通过字段“adjlist”可以很容易的定义该进程的blocker。
IGN/IGN_DMP：这些Session通常是IDLE Session。

Using Oradebug for System Hangs:
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug setinst all
SQL> oradebug hanganalyze 5
SQL> oradebug -g def dump systemstate 10

get a systemstate dump, these types of dumps can be obtained anytime
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10

Getting a PROCESSTATE DUMP
SQL> oradebug setospid
SQL> oradebug unlimit
SQL> oradebug dump processstate 10

Getting a ERRORSTACKS DUMP
SQL> oradebug setospid
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3

Using ORADEBUG to Trace A Sessions SQL
SQL> ORADEBUG SETOSPID
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF

Tracing Errors Using ORADEBUG
SQL> ORADEBUG EVENT 942 TRACE NAME ERRORSTACK LEVEL 3

Using ORADEBUG to Find Semaphore and Memory Segments
$ ipcs -b
SQL> oradebug setmypid
SQL> oradebug ipc
SQL> oradebug tracefile_name
$ ipcrm
$ ipcrm -m
$ ipcrm -s

Finding Parallel SQL Processes Using ORADEBUG
SQL> oradebug setospid
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever, level 4

Tracking down ORA-04030 errors
SQL> oradebug setospid
SQL> oradebug unlimit
SQL> oradebug dump heapdump 5
Using Oradebug to Debug Spinning Processes
SQL> oradebug setospid
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3

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

How to determine the Events Set in a System
1 for session   ***** 2 for process   *****   4 for system

SQL> oradebug setmypid
SQL> oradebug dump events 4
SQL> oradebug tracefile_name

Using ORADEBUG to Release DDL locks
SQL> oradebug wakeup

Book - Page 35

10.2.0.5 oradebug help
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
IMRDEBUG                                 Invoke CGS IMR 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           [-t count] [arg1]...[argn] Invoke function with arguments

SQL> oradebug dumplist
EVENTS
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
HANGANALYZE
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
CROSSIC
CONTEXTAREA
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
MEMORY_LOG
REALFREEDUMP
FLUSH_JAVA_POOL
POOL_SIMULATOR
PGA_DETAIL_GET
PGA_DETAIL_DUMP
PGA_DETAIL_CANCEL
PGA_SUMMARY
MODIFIED_PARAMETERS
EVENT_TSM_TEST
ERRORSTACK
CALLSTACK
HANGANALYZE_PROC
TEST_STACK_DUMP
TEST_GET_CALLER
RECORD_CALLSTACK
EXCEPTION_DUMP
BG_MESSAGES
ENQUEUES
KSTDUMPCURPROC
KSTDUMPALLPROCS
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
LDAP_USER_DUMP
LDAP_KERNEL_DUMP
DUMP_ALL_OBJSTATS
DUMPGLOBALDATA
HANGANALYZE_GLOBAL
GES_STATE
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
CREATE_DUMMY_REQUEST
MMAN_ALLOC_MEMORY
MMAN_CREATE_REQUEST
MMAN_CREATE_IMM_REQUEST
DUMP_ALL_COMP_GRANULE_ADDRS
DUMP_ALL_COMP_GRANULES
DUMP_ALL_REQS
DUMP_TRANSFER_OPS
DUMP_ADV_SNAPSHOTS
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FLUSH_CACHE
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
REDOLOGS
LOGHIST
ARCHIVE_ERROR
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
UPDATE_BLOCK0_FORMAT
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE
TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_CORRUPT_REPEAT
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_FAIL_INPUT_FILENO
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
KRB_SET_TIME_SWITCH
KRBMRSR_LIMIT
KRBMROR_LIMIT
KRC_TRACE
KRA_OPTIONS
KRA_TRACE
FBTAIL
FBINC
FBHDR
FLASHBACK_GEN
DROP_SEGMENTS
KTPR_DEBUG
TREEDUMP
LONGF_CREATE
ROW_CACHE
LIBRARY_CACHE
CURSORDUMP
CURSORTRACE
CURSOR_STATS
SHARED_SERVER_STATE
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
OLAP_DUMP
SELFTESTASM
IOERREMUL
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
MMON_TEST

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/5359/viewspace-665679/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/5359/viewspace-665679/