Analysing systemstate dumps

In my last article I discussed how to dump the systemstate using various methods. These files are quite huge and are sometimes difficult to decipher. You can parse the file using an awk script in
LTOM
provided by Oracle’s center of expertise. LTOM is a lite onboard monitor which is a diagnostic tool.

Once you download and install LTOM you can find a script called ass109.awk in tom_base/tom/src which parses the systemstate into a more readable format

buffalo>pwd
/usr/opt/oracle/bin/tom_base/tom/src
buffalo>ls -al ass*
-rwxr-x--- 1 oracle dba 34549 Nov 25 2003 ass109.awk


We will attempt to analyse a systemstate dump generated by the following error

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=42
System State dumped to trace file /u00/app/oracle/admin/F8900DEV/udump/f8900dev1_ora_12057.trc
Mon Dec 11 09:58:05 2006

buffalo >awk -f ass109.awk f8900dev1_ora_12057.trc > rowcache.out

If you look at rowcache.out
42: last wait for 'ksdxexeotherwait' [Rcache object=0x483a70e4,]
The holder of 0x483a70e4 is
Rcache object=0x483a70e4, 48: 48: is waiting for Latch 5213eeb4

If you look at rowcache.out in conjunction with the raw trace file it can be concluded that process 42 has requested row cache lock on dc_profiles which is held by process 48.
Process 48 is in turn waiting on the “Child object queue header” latch which is held by process 44
Process 44 which is a DataPump Worker is in turn waiting on 'SGA: allocation forcing component growth' which is seen when using ASMM (sga_target)