一个强大的分析诊断进程跟踪文件的工具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).
#
##############################################################################
