一个强大的分析诊断进程跟踪文件的工具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/1nawk IBM RS/6000 awk
#Sun Solarisnawk HPUXawk (v10)???
(v9)
#Sun SunOSnawk 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.ukv1.0.004/96 Created
#kquinn.ukv1.0.104/96 Minor changes to run with nawk on OSF1 and AIX
# Blocking Section's output changed slightly
#kquinn.ukv1.0.204/96 Dumps object names for library objects
# Now sequent-nawk aware
# First public release
#kquinn.ukv1.0.306/96 File I/O wait events now output file, block etc
#kquinn.ukv1.0.407/96 Parallel Query Dequeue Reason codes now output
#kquinn.ukv1.0.508/96 Added QC to QS code
# Added code to skip 'branch of' state objects
#kquinn.ukv1.0.503/97 Output Oracle command based on 'oct:' code.
# (Note that only the PARENT session's command
#code is output).
# Strip carriage returns (^M)
#kquinn.ukv1.0.610/97 Indicate dead processes
#kquinn.ukv1.0.709/98 Print some more wait information for certain
# wait events and handle waits on the sequence
# enqueue.
#kquinn.ukv1.0.812/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).#
##############################################################################
right"(出处:清风软件下载学院)