From oracle-l-bounce@freelists.org Tue Mar 22 14:25:45 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2MKPjjC001554 for ; Tue, 22 Mar 2005 14:25:45 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2MKPiem001550 for ; Tue, 22 Mar 2005 14:25:44 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 64F5E87F84; Tue, 22 Mar 2005 14:23:57 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25671-04; Tue, 22 Mar 2005 14:23:57 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D5B8087D7D; Tue, 22 Mar 2005 14:23:56 -0500 (EST) Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410511E823@usahm236.amer.corp.eds.com> From: "Powell, Mark D" To: "'Les.Hollis@ps.net'" , oracle-l@freelists.org Subject: RE: enq: TX - index contention Date: Tue, 22 Mar 2005 14:21:58 -0500 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 17606 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mark.powell@eds.com Precedence: normal Reply-To: mark.powell@eds.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: 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. -- Mark D Powell -- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Hollis, Les Sent: Tuesday, March 22, 2005 1:32 PM To: Powell, Mark D; oracle-l@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@eds.com]=20 Sent: Tuesday, March 22, 2005 12:11 PM To: Hollis, Les; oracle-l@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 -----Original Message----- From: Hollis, Les [mailto:Les.Hollis@ps.net]=20 Sent: Tuesday, March 22, 2005 12:48 PM To: Hollis, Les; mark.powell@eds.com; oracle-l@freelists.org Subject: RE: enq: TX - index contention Addendum.....this is cut from the Database Fundamentals II training course from Oracle Education....Power Point Slides INITRANS: Guarantees a minimum level of concurrency. It defaults to 1 for a data segment and 2 for an index segment, guarantees a minimum level of concurrency. For example, if set to 3, INITRANS ensures that at least three transactions can concurrently make changes to the block. If necessary, additional transaction slots can be allocated from the free space in the block to permit more concurrent transactions to modify rows in the block. True in both the 9.0.1 release documentation AND in the 9.2.0.x Documentation. -----Original Message----- From: Hollis, Les Sent: Tuesday, March 22, 2005 11:32 AM To: 'mark.powell@eds.com'; oracle-l@freelists.org Subject: RE: enq: TX - index contention I think not. SQL> create table junky1 (name varchar2(5)); Table created. SQL> select ini_trans from user_tables where table_name =3D 'JUNKY1'; INI_TRANS ---------- 1 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Powell, Mark D Sent: Tuesday, March 22, 2005 10:19 AM To: oracle-l@freelists.org Subject: RE: enq: TX - index contention I believe that the default number of initrans for a table has been raised to two as of version 9.0 =20 -- Mark D Powell -- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of K Gopalakrishnan Sent: Tuesday, March 22, 2005 10:10 AM To: stalinsk@gmail.com Cc: oracle-l@freelists.org Subject: Re: enq: TX - index contention Stalin: TX enqueue waits with LMODE 4 can happen for the following reasons. Quoting from 'Oracle Wait Interface' "A wait for the TX enqueue in mode 4 is normally due to one of the following reasons: ITL (interested transaction list) shortage Unique key enforcement Bitmap index entry Here, we will talk about the ITL, which is a transaction slot in a data block. The initial number of ITL slots is defined by the INITRANS clause and is limited by the MAXTRANS clause. By default, a table has 1 ITL and an index has 2 ITLs. Each ITL takes up 24 bytes and contains the transaction ID in the format of USN.SLOT#.WRAP#. Every DML transaction needs to acquire its own ITL space within a block before data can be manipulated. Contention for ITL occurs when all the available ITLs within a block are currently in use and there is not enough space in the PCTFREE area for Oracle to dynamically allocate a new ITL slot. In this case, the session will wait until one of the transactions is committed or rolled back, and it will reuse that ITL slot. ITL is like a building parking space. Everyone who drives to the building needs a parking space. If the parking lot is full, you have to circle the lot until someone leaves the building. " So I would expect any of the above conditions in your case? You may want to query the V$segment_statistics for ITL waits to eliminate the ITL issue. If you have bitmap indexes, that should be one of the casues for those excesive waits. The wait time is close to 3 seconds and I suspect that could be because of the bitmap index issue, --=20 Best Regards, K Gopalakrishnan Co-Author: Oracle Wait Interface, Oracle Press 2004 http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/ -- -- http://www.freelists.org/webpage/oracle-l