Re: enq: TX - index contention

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 22 Mar 2005 21:13:07 -0000
Message-ID: <007a01c52f23$f1ea1d60$6702a8c0@Primary>

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_at_eds.com> To: <oracle-l_at_freelists.org> 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

Received on Tue Mar 22 2005 - 16:16:41 CST