FreeLists / oracle-l / Re: Enqueue TX level 4 wait -- blocks dump

["Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>]

Hi Jonathan.
Thanks for answering.

> Do you mean that the value from ini_trans in
> dba_tables and dba_indexes were 10 and 11
> respectively ?

Exactly.

> If so, then you should expect the number of
> ITL slots in the tables and indexes to be 10
> and 11 (assuming the initrans was not altered
> on the fly after data had been entered).  If
> you want to determine the actual concurrency
> from the block dumps, you would have to guess,
> based on how close the SCNs were in the ITL
> entries.

Let me tell you what I tried to do.
For indexes for example, in many blocks all the ITLs had been used,
wouldn't that mean that at some point in time there were 11 simultaneous
transactions active? (counting also the recursive ones)

Index example.
----------------------------------------------------------------------------
--------------------------------

Block header dump:  0x55802364
 Object id on Block? Y
 seg/obj: 0xb456  csc: 0x00.d86ff6c  itc: 11  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0002.012.00019480    uba: 0x10c0170e.240b.02  C---    0  scn
0x00
00.06a5a457
0x02   xid:  0x0006.00d.000335f6    uba: 0x10c06d67.464b.0e  --U-    1  fsc
0x00
00.0d96cc25
0x03   xid:  0x0001.031.00032a0e    uba: 0x10c00b0f.456c.0a  --U-    1  fsc
0x00
00.0d96cc29
0x04   xid:  0x0003.041.00033018    uba: 0x10c03b0c.44db.0c  --U-    1  fsc
0x00
00.0d96cc3b
0x05   xid:  0x0003.002.00033015    uba: 0x10c03b0f.44db.19  --U-    1  fsc
0x00
00.0d96cde5
0x06   xid:  0x0003.039.00032c31    uba: 0x10c0cd44.447e.06  --U-   10  fsc
0x00
00.0d86ff6d
0x07   xid:  0x0003.021.00033019    uba: 0x10c03b0b.44db.0e  --U-    1  fsc
0x00
00.0d96cc21
0x08   xid:  0x0007.026.000330af    uba: 0x10c07ea9.43bd.2e  --U-    1  fsc
0x00
00.0d8ed98e
0x09   xid:  0x0002.035.00031143    uba: 0x59401d01.4483.1b  --U-    1  fsc
0x00
00.0d8ed992
0x0a   xid:  0x0006.010.000335f7    uba: 0x10c06d66.464b.11  --U-    1  fsc
0x00
00.0d96cc19
0x0b   xid:  0x0001.00d.00032a27    uba: 0x10c00b0e.456c.0d  --U-    1  fsc
0x00
00.0d96cc1d

Leaf block dump
===============
header address 9223372041150438708=0x80000001000a9d34
kdxcolev 0
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 514
kdxcofbo 1064=0x428
kdxcofeo 1118=0x45e
kdxcoavs 54
kdxlespl 0
kdxlende 0
kdxlenxt 1434461029=0x55802365
kdxleprv 1434456775=0x558012c7
kdxledsz 0
kdxlebksz 7800

----------------------------------------------------------------------------
--------------------------------
There are some index blocks which have all the ITL slots used and some of
them that don't. They have only 2 slots used.
For the table, all ITLs have been used for almost all the table's blocks.


By checking the ITC value I was trying to find an ITC higher than 11, if I
was lucky and found something like that,
this would have meant that at some point in time there were more than 11
transactions going on and that an additional
ITL slot was needed and was allocated (as maxtrans and pctfree allowed it).
am I correct?

I really appreciate your comments.
Thank you.
Regards,
Diego.



----- Original Message ----- 
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 03, 2004 10:25 AM
Subject: Re: Enqueue TX level 4 wait -- blocks dump


>
> Can you clarify your comments about ITL and ITC.
>
> Do you mean that the value from ini_trans in
> dba_tables and dba_indexes were 10 and 11
> respectively ?
>
> If so, then you should expect the number of
> ITL slots in the tables and indexes to be 10
> and 11 (assuming the initrans was not altered
> on the fly after data had been entered).  If
> you want to determine the actual concurrency
> from the block dumps, you would have to guess,
> based on how close the SCNs were in the ITL
> entries.
>
> TX/4 arises from many causes - the most likely
> one on inserts into heap tables relates to collision
> on primary keys and foreign keys.
>
> Could you have multiple processes trying to
> insert the same PK, waiting, failing, then using
> the next value from a sequence number to try
> again ? Could you have processes inserting
> child rows for parent rows that had not yet
> been created ... and so on.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Optimising Oracle Seminar
> http://www.jlcomp.demon.co.uk/seminar.html
>
> June 2004   UK  Manchester
> July 2004   Iceland
> July 2004   USA California
> Aug  2004   USA North Carolina
> Sept 2004   UK  Manchester
> Sept 2004   USA NYC
> Oct  2004   USA Boston
>
>
> ----- Original Message ----- 
> From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
> To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
> Sent: Monday, May 03, 2004 9:35 PM
> Subject: Enqueue TX level 4 wait -- blocks dump
>
>
> Hi everybody,
> I have a question on TX enqueues.
> I'm seeing a lot of TX enqueues level 4 on a table. I have also found that
> the statement that was waiting on this wait was an ***INSERT*** operation.
>
> Oracle 8i
> Table:
> ITL = 10
> 4 FREELISTS
> 4 FREELISTS GROUPS
> PCTFREE 20
>
> I dumped every table block and checked the ITC entry. I found ITC was
almost
> always 10 for every block.
> Then I checked for the average free space within the blocks, (supposing
the
> block had needed an additional ITL slot) and again I found that in every
> block there was at least 80 bytes free. (as Avsp reported).
>
> So I went for the indexes
>
> ITL = 11
> 4 FREELISTS
> 4 FREELISTS GROUPS
> PCTFREE 10
>
> and I found no problem at all in the ITC entries.(most of them were 11)
> But I found many blocks with kdxcoavs = 0. (no free space on them).
>
> Taking into consideration that we're talking about an INSERT,
> Can it be possible that the TX 4 is being caused by an ITL shortage in the
> index?
>
> (All slots are being used and there's no free space in the block, so when
a
> new INSERT needs to put its entry in that block, despite of the fact that
> this block will surely need to split because there's no free space left in
> it, the transaction first needs to get a free ITL slot and as they are all
> taken and there's no free space it has to wait in a enqueue TX 4. Is this
> the way it works on this case?)
>
> What do you think?
>
> Thanks,
> Diego.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------