一个强大的分析诊断进程跟踪文件的工具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 7. It *may* work with
# 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). #
##############################################################################
【责编:admin】
--------------------next---------------------
