Re: enq: TX - index contention

Mark,

Are you sure you've dumped the right thing.
I've just repeated your test (I think);


drop table marktest;


create table marktest (
 fld1   varchar2(10)
 ,fld2  number
 ,fld3  date
)
initrans    1
;




begin
for I in 1..500 loop
  insert into marktest values ('test',I,sysdate + I);
  end loop;
end;
/

commit;

execute dump_seg('marktest',2)
exit

(dump_seg is just my procedure to dump blocks for a segment -
default is table owned by current user, starting at first data block,
and one block).

The critical parts of the dump are:

(First block)
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.003.00016163 0x008001a7.08ac.60 --U- 335 fsc 0x0000.0622434c
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

(Second block)
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.003.00016163 0x008001a9.08ac.0f --U- 165 fsc 0x0000.0622434c
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000


Note in particular (apart from the two ITL entries in both blocks),
that the lock count is 335 for one block and 165 for the other -
your dump shows ZERO rows locked - which, coupled with the
null flag setting (----) suggests that you're not dumping the blocks
that took the insert.

This is 9.2.0.6 - and you are correct, not only do you get a 
minimum 2 entries in the ITL, if you do create table as select, 
you get 3 - even when the data dictionary says 1.

Having said that - Oracle clearly does some funnies with ITLs,
so if Les can produce a script that gives a dump with only one
ITL per block, I'll run it and see if it is a (minor-)version thing.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






----- Original Message ----- 
From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 22, 2005 8:05 PM
Subject: RE: enq: TX - index contention


Ok, Les appears to be correct in that if I populate the table the number of
ITL slots showing in the block dump now shows only 1.  Why it would show 2
when the table is empty and then only 1 when populated I do not know.  But
then it has been months since I spent any time trying to read block dumps.
Someone more familiar with them may be able to add necessary information.

*** 2005-03-22 14:49:45.138
*** SESSION ID:(29.4749) 2005-03-22 14:49:45.137
Start dump data blocks tsn: 5 file#: 10 minblk 53 maxblk 55
buffer tsn: 5 rdba: 0x02800035 (10/53)
scn: 0x0001.125e932f seq: 0x02 flg: 0x00 tail: 0x932f0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x02800035
 Object id on Block? Y
 seg/obj: 0x4520  csc: 0x01.125e932d  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.010.0001ecdf  0x00000000.0000.00  ----    0  fsc
0x0000.00000000

data_block_dump,data header at 0x110272044
<snip>
end_of_block_dump
buffer tsn: 5 rdba: 0x02800036 (10/54)
scn: 0x0001.125e932f seq: 0x02 flg: 0x00 tail: 0x932f0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x02800036
 Object id on Block? Y
 seg/obj: 0x4520  csc: 0x01.125e932d  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.010.0001ecdf  0x00000000.0000.00  ----    0  fsc
0x0000.00000000

data_block_dump,data header at 0x110272044

The new version of the script contains the following code after the create
to populate the table:
begin
for I in 1..500 loop
  insert into marktest values ('test',I,sysdate + I);
  end loop;
end;
/
commit
/

-- Mark D Powell --

--
http://www.freelists.org/webpage/oracle-l

Other related posts: