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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: enq: TX - index contention

RE: enq: TX - index contention

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 22 Mar 2005 15:05:23 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410511E825@usahm236.amer.corp.eds.com>


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.

 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
/

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Tuesday, March 22, 2005 2:41 PM
To: 'Les.Hollis_at_ps.net'; oracle-l_at_freelists.org Subject: RE: enq: TX - index contention

 I withdraw this post. I think I misunderstood his last point. I will have to run a few more block dumps and repost with any necessary corrections.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Tuesday, March 22, 2005 2:22 PM
To: 'Les.Hollis_at_ps.net'; oracle-l_at_freelists.org Subject: RE: enq: TX - index contention

Les, I am not sure how you came to your conclusion that the number of blocks in the allocation request affects the number of ITL lists allocated, but it is wrong.

My initial test showed I requested 1 ITL and 4 blocks. I got 4 blocks with two ITL slots in them. I repeated my test using a tablespace with a uniform extent size of 256K. I asked for 16k but got 32 8K blocks with two ITL slots in each.

Errors in the Oracle documentation are not uncommon. I checked the 10g SQL manual for the physical attributes clause and it still claims 1 is the default except for two exceptions: clusters and indexes.

But anybody who looks at the block dump knows better. If you still do not believe the information that I, Gopalakrishnan, or others posted then I do think there is anything anyone can say that will change your mind until Oracle updates the documentation.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hollis, Les
Sent: Tuesday, March 22, 2005 1:32 PM
To: Powell, Mark D; oracle-l_at_freelists.org Subject: RE: enq: TX - index contention

I think the confusion here IS that Oracle allocates 1 (ONE) initrans (ITL) for EACH BLOCK that is allocated to the table. By default, oracle allocates 2 BLOCKS (Database blocks) for every table (5 for every index) that gets created. So, I can well see where you would/could get 2 ITL for a newly created table based on the 2 data blocks allocated.=20

Now, if you were using LMT, I'm sure that number would be greater based on the larger number of DB blocks for the uniform size of the LMT.

However, it still only allocates ONE ILT for each block unless you specify OTHERWISE. Hence the documentation WOULD be right.

-----Original Message-----
From: Powell, Mark D [mailto:mark.powell_at_eds.com]=20 Sent: Tuesday, March 22, 2005 12:11 PM
To: Hollis, Les; oracle-l_at_freelists.org
Subject: RE: enq: TX - index contention

 OK, there appears to be some question to how many ITL lists Oracle allocates. I made the statement that Oracle allocates 2 probably starting with version 9.

Ran on Oracle version 9.2.0.5 running on AIX 5.3

UT1 >
UT1 > drop table marktest;

Table dropped.

UT1 > create table marktest (
  2 fld1 varchar2(10)
  3 ,fld2 number
  4 ,fld3 date
  5 )
  6 tablespace usr
  7 initrans 1
  8 /

Table created.
-- I specified 1 transaction work list and dictionary shows 1

UT1 > select ini_trans from user_tables where table_name =3D 'MARKTEST'   2 /

 INI_TRANS


         1

UT1 > select file_id, block_id, blocks
  2 from dba_extents
  3 where segment_name =3D 'MARKTEST'
  4 /

   FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------

        10 18 4

UT1 > spool off
-- However looking at the block dump you see 2 are allocated.
*** 2005-03-22 13:01:24.699
*** SESSION ID:(22.4090) 2005-03-22 13:01:24.693 Start dump data blocks tsn: 5 file#: 10 minblk 19 maxblk 19 buffer tsn: 5 rdba: 0x02800013 (10/19) scn: 0x0001.f3774a80 seq: 0x01 flg: 0x06 tail: 0x4a800601 frmt: 0x02 chkval: 0x7458 type: 0x06=3Dtrans data Block header dump: 0x02800013 Object id on Block? Y
 seg/obj: 0x7a0d csc: 0x01.ea1081a9 itc: 2 flg: O typ: 1 - DATA

     fsl: 0 fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01 0x0005.01c.000227ae 0x0140264e.1310.18 --U- 1 fsc 0x0000.f3774a80
0x02 0x0005.01a.00022492 0x01403ca4.1304.01 C--- 0 scn 0x0001.ea108193

data_block_dump,data header at 0x11027205c =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D <snip>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 22 2005 - 15:09:42 CST

Original text of this message

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