【IT168 服务器学院】 一个强大的分析诊断进程跟踪文件的工具ASS.AWK
　　
　　你可以使用下面方法产生系统状态跟踪文件
　　
　　process state dumps ==>一个进程的所有对象状态,如果确认哪个进程有问题,就用该方法
　　
　　system state dumps　==>整个系统的所有进程中的所有对象状态
　　
　　process state dumps
　　~~~~~~~~~~~~~~~~~~~~~
　　1.ALTER SESSION SET EVENTS ''IMMEDIATE TRACE NAME PROCESSSTATE LEVEL 10'';
　　
　　2.event = ''604 trace name processstate,level 10''
　　
　　3.oradebug setospid 进程ID
　　　oradebug dump processstate 10;
　　
　　system state dumps
　　~~~~~~~~~~~~~~~~~~~~~
　　1.ALTER SESSION SET EVENTS ''IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10'';
　　
　　2.event = ''604 trace name systemstate,level 10''
　　ass.awk的脚本
　　===========
　　ass.awk
　　===========
　　REM ------------------------------------------------------------------------------------------------
　　REM #DESC　　　: Generate a formatted report from system state dump showing who''s blocking whom
　　REM Usage　　　: See below
　　REM Description: Must run this script on the db server where the trace file resides
　　REM ------------------------------------------------------------------------------------------------
　　
　　# Usage Instructions
　　# ~~~~~~~~~~~~~~~~~~
　　#　Usage: [n]awk -f ass.awk fname.trc　 (But read the Portability Section !!)
　　#
　　#　Configuring Ass:
　　#
　　#　 By default, ''ass'' attempts to dump as much information as possible and
　　#　assumes that the output is to be printed to screen. This means that ''ass''
　　#　runs in its slowest mode. Ass can be changed/speeded up by amending the
　　#　following variables in the BEGIN section :
　　#
　　#　 interactive...........1 = show indication of processing [default]
　　#　　　　　　　　　　　　 0 = don''t show anything (faster)
　　#　 verbose...............1 = prints additional info　　　　[default]
　　#　　　　　　　　　　　　 0 = don''t show info (faster)
　　#　 eventdetail...........1 = prints additional event info for selected events
　　#　　　　　　　　　　　　　　 [default]
　　#　　　　　　　　　　　　 0 = don''t do the above (faster)
　　#　 skipbranch............1 = Skip ''branch of'' state objects cause by SQL*NET
　　#　　　　　　　　　　　　　　 loopback sessions etc (default)
　　#　　　　　　　　　　　　 0 = don''t skip ''branch of'' transactions
　　#　 seqinfo...............1 = Output sequence number for WAITING processes
　　#　　　　　　　　　　　　 0 = Do not dump seq# information.
　　#
　　# Portability
　　# ~~~~~~~~~~~
　　#　1) This uses the nawk extension of functions. Some variants of awk accept
　　#　　 this (eg HP-UX v10) and others do not. Use nawk if awk fails !!
　　#
　　#　　　Alpha OSF/1　　nawk　　　　 IBM RS/6000　 awk
　　#　　　Sun Solaris　　nawk　　　　 HPUX　　　　　awk (v10)　??? (v9)
　　#　　　Sun SunOS　　　nawk　　　　 Sequent　　　 nawk
　　#
　　#　2) The Alpha version of awk can only handle 99 fields and will return
　　#　　 a message like ''awk: Line ..... cannot have more than 99 fields''.
　　#　　 The w/a: Either change the identified line or use a different platform.
　　#
　　# Known Restrictions
　　# ~~~~~~~~~~~~~~~~~~
　　#　o The script assumes a certain structure to the System State. This means
　　#　　that this cannot be used on systemstates produced by MVS or VM.
　　#　　[To make it work the first two or three words need to be stripped from]
　　#　　[each line in the systemstate trace file.　　　　　　　　　　　　　　 ]
　　#
　　#　o This has been developed to work with Oracle7. It *may* work with Oracle
　　#　　version 6 but this has not been tested.
　　#
　　#　o The code currently does not recognise processes that are CONVERTING locks.
　　#　　Eg, I have an SX lock and I am requesting the higher SSX mode.
　　#　　This will result in the process as waiting for a lock when it owns it. Note
　　#　　that if any other process has the resource locked then both are listed
　　#　　as holding the resource but a SELF-DEADLOCK is not flagged.
　　#
　　#　o It looks like there may be a bug with listing processes that are
　　#　　blocking others because they have a buffer s.o. that others are waiting
　　#　　on.
　　#
　　# Coding Notes
　　# ~~~~~~~~~~~~
　　#　o There''s an obscure usage of building the blkres word list. It seems
　　#　　that you cannot just say : blkres[a,b] = blkres[a,b] " " newval
　　#　　You have to use a temporary variable (''tb'' in our case) to achieve this.
　　#　o Sequent doesn''t seem to like logical operators being used with regular
　　#　　expressions. Hence the ''wait event'' section had to be re-written to use
　　#　　$0 ~ /a/ || $0 ~ /b/. Just try the following test :
　　#
　　#　　　 NR == 1 && /a/ || /b/ { print }
　　#
　　# History
　　# ~~~~~~~
　　#　kquinn.uk　　v1.0.0　04/96　 Created
　　#　kquinn.uk　　v1.0.1　04/96　 Minor changes to run with nawk on OSF1 and AIX
　　#　　　　　　　　　　　　　　　 Blocking Section''s output changed slightly
　　#　kquinn.uk　　v1.0.2　04/96　 Dumps object names for library objects
　　#　　　　　　　　　　　　　　　 Now sequent-nawk aware
　　#　　　　　　　　　　　　　　　 First public release
　　#　kquinn.uk　　v1.0.3　06/96　 File I/O wait events now output file, block etc
　　#　kquinn.uk　　v1.0.4　07/96　 Parallel Query Dequeue Reason codes now output
　　#　kquinn.uk　　v1.0.5　08/96　 Added QC to QS code
　　#　　　　　　　　　　　　　　　 Added code to skip ''branch of'' state objects
　　#　kquinn.uk　　v1.0.5　03/97　 Output Oracle command based on ''oct:'' code.
　　#　　　　　　　　　　　　　　　 (Note that only the PARENT session''s command
　　#　　　　　　　　　　　　　　　　code is output).
　　#　　　　　　　　　　　　　　　 Strip carriage returns (^M)
　　#　kquinn.uk　　v1.0.6　10/97　 Indicate dead processes
　　#　kquinn.uk　　v1.0.7　09/98　 Print some more wait information for certain
　　#　　　　　　　　　　　　　　　 wait events and handle waits on the sequence
　　#　　　　　　　　　　　　　　　 enqueue.
　　#　kquinn.uk　　v1.0.8　12/98　 Minor changes
　　#　　　　　　　　　　　　　　　 Changed to accomodate new systemstate format
　　#　　　　　　　　　　　　　　　 so that we identify the start of a systemstate
　　#　　　　　　　　　　　　　　　 correctly once more.
　　#　　　　　　　　　　　　　　　 Added seq# processing for waiting processes.
　　#　　　　　　　　　　　　　　　 Dumped more info for DFS lock acquisition
　　#
　　# Current Enhancement Requests Oustanding
　　# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
　　#　o Pick out error code in PQO Queue Ref''s
　　#　o Test concatenating all array elements so that we affectively use singular
　　#　　arrays. This may speed the processing depending upon how the implementation
　　#　　of awk uses multi-dimensional arrays.
　　#
　　#################################
　　# Any failure cases or suggested improvements then please Email KQUINN.UK　　#
　　# with the details and the system state file (if relevant).　　　　　　　　　#
　　##############################