Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to drill down on a TX lock

Re: How to drill down on a TX lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 6 Aug 2001 11:01:33 +0100
Message-ID: <997091919.15576.0.nnrp-13.9e984b29@news.demon.co.uk>

Still have to reply through the news group:

You said:
Hey Jon - more interesting question then before. Oracle told me how to dump a
block, however reading it is proprietary etc., I have a situation where a JDBC
thread is attempting to do an insert and is requesting a share lock (4) but is
being stopped. The table in question (afaik) has an initrans of 1 and maxtrans of 255. From looking at metalink, running out of ILT slots may cause
this phenomena. How can I verify whether the block is so full that it can't
get another slot?

Answer:



If you can see the process waiting on a TX mode 4, then do a processstate dump. This will dump the process state, including the block dump of the block being waited on - a critical sample follows:

connect internal
oradebug setospid (spid value from v$process) alter system dump processstate 10

NOTE - the block dump in the process state is the same format as the 'ordinary' block dump. Running block dumps after the event can be misleading, as you can find blocks which have one or several ITL entries, and no free space (tosp), but this doesn't prove conclusively that they were the blocks that actually ran into the problem



Piece of SO dump (jpl comment)
          where: kcbwh1: kcbchg1, why: 0
        buffer tsn: 4 rdba: 0x01400cac (5/3244)
        scn: 0x0000.0108d6e3 seq: 0x00 flg: 0x00 tail: 0xd6e30600
        frmt: 0x02 chkval: 0x0000 type: 0x06=trans data


--------------------------------------------------
Start of block dump (jpl comment)

Block header dump: 0x01400cac
Object id on Block? Y
seg/obj: 0x7ffb csc: 0x00.108d6e3 itc: 1 flg: O typ: 1 - DATA

     fsl: 0 fnx: 0x0 ver: 0x01



ITL list (jpl comment)
Itl           Xid                  Uba         Flag  Lck
Scn/Fsc
0x01 xid: 0x0003.022.000001a6 uba: 0x00c012ef.00ba.39 C--- 0 scn 0x0000.0108d6e1

data_block_dump


tsiz: 0x1fb8
hsiz: 0x22
tsiz: 0x1fb8
hsiz: 0x22

pbl: 0xc24da044
bdba: 0x01400cac
flag=-----------
ntab=1
nrow=8
frre=-1
fsbo=0x22
fseo=0x22
avsp=0x0
                      <<< this is what you are after
tosp=0x0        <<<--- total free space in block (hex).
                    <<< needs ca. 24 bytes to create ITL
0xe:pti[0]      nrow=8  offs=0
0x12:pri[0]     offs=0x1bc8
0x14:pri[1]     offs=0x17d7
0x16:pri[2]     offs=0x13e6
0x18:pri[3]     offs=0xff5



--
Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for September 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html

Doug C wrote in message ...
>I have a feeling this might be somewhat complicated and involve doing block
>dumps.  Irregardless.. can anyone provide an example?  Let's say I've got 4
 rows
>in a table locked exclusively with a TX lock.  What must I do to ascertain
 which
>rows are locked.  Dumping every block in the table is acceptable in this
>situation because the table is pretty small.  Any suggestions, examples?
>
>Thanks,
>D
Received on Mon Aug 06 2001 - 05:01:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US