Now let’s get into something slightly more complicated.
DBSLOG to help find out what’s going on when a session “hangs”
I’ve placed a lock on a record in the item master. The bottom of the dbs.log looks like this:
------ QPS Input Row -------
Bind :1 : string : [0x8185d20] '` '
----- DBMS Where Input ----
Bind nr 1 : item : string : '` '
SQL> ora_multi_execute( 0x81cb0d8 ) do 0, prefetch 1
There is a lot of information in these few lines.
First: Bind nr 1 : item : string : ‘` ‘
is the binding of “item” to the string “` ” which is a dummy item in my database.
Second: SQL> ora_multi_execute( 0x81cb0d8 ) do 0, prefetch 1
Tells us the hex value for the cursor further up in the dbs.log file that we’re executing.
Now to find out the query that’s locked we just use that hex value 0x81cb0d8 and search backwards in the file:
SQL> ora_parse( "SELECT /*+ index(a ttiitm001200$idx1) */ t$item,t$dsca,t$dscb,t$dscc,t$dscd,t$wght,t$seak,
t$seab,t$kitm,t$citg,t$ctyp,t$csel,t$csig,t$cvat,t$txta,t$uset,t$cuni,t$stgu,t$cwar,t$kltc,t$obpr,t$kpsl,t$npsl,t$pics,
t$abcc,t$lcod,t$uscu,t$usab,t$slmp,t$serv,t$sfst,t$maxs,t$csps,t$cspd,t$cfmd,t$scst,t$stoc,t$blck,t$ordr,t$allo,
t$hall,t$quot,t$ltdt,t$opol,t$osys,t$omth,t$oqmf,t$mioq,t$maoq,t$umer,t$fioq,t$ecoq,t$reop,t$oint,t$ddfq,t$oltm,
t$sftm,t$fodt,t$ocst,t$auso,t$cpha,t$oqdr,t$repi,t$scdl,t$orip,t$pcrp,t$stpm,t$mrpc,t$plmm,t$mrpo,t$eitm,t$bfcp,
t$bfep,t$bfhr,t$ndrp,t$nnts,t$qpnt,t$unom,t$runi,t$scpf,t$crmp,t$tmfc,t$roun,t$ncst,t$llcd,t$llci,t$mrpi,t$stmr,
t$cuqp,t$cupp,t$cpgp,t$csgp,t$pcgp,t$ccur,t$ltpp,t$prip,t$avpr,t$ltpr,t$suno,t$qual,t$purc,t$txtp,t$cuqs,t$cups,
t$cpgs,t$csgs,t$cmgp,t$rbgp,t$pris,t$ltsp,t$prir,t$umsp,t$lmsp,t$ccde,t$ctyo,t$txts,t$cpcp,t$copr,t$matc,t$oprc,
t$cuid,t$actf,t$ltcp,t$stva,t$buyr,t$cplb,t$cppp,t$ccit,t$ccfu,t$ccco,t$prre,t$copt,t$cprp,t$itmt,t$proi,t$cont,t$cntr,
t$czed,t$reli,t$assi,t$potc,t$ffsi,t$qbsi,t$osyc,t$ufra,t$nobd,t$blcm,t$dcnt,t$exkb,t$itm2,t$Refcntd,t$Refcntu
FROM baan.ttiitm001200 WHERE t$item=:1 FOR UPDATE WAIT 9000 " )
SQL> ora_parse( 0x81cb0d8 )
You see the ora_parse (0x81cb0d8) which matches up with the ora_multi_execute
So you know that in this particular case, the session is “hanging” because it cannot acquire a lock on ttiitm001200.
You use the same process to identify slow queries, wrong queries, etc.
