Re: enq: TX - index contention
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Mar 2005 21:13:07 -0000
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 --
--
//www.freelists.org/webpage/oracle-l
Other related posts:
