From oracle-l-bounce@freelists.org Tue Mar 22 12:33:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2MIXKht020086 for ; Tue, 22 Mar 2005 12:33:20 -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 j2MIXJem020079 for ; Tue, 22 Mar 2005 12:33:19 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 51B07884A5; Tue, 22 Mar 2005 12:31:22 -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 05416-02; Tue, 22 Mar 2005 12:31:22 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C655988412; Tue, 22 Mar 2005 12:31:21 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: enq: TX - index contention Date: Tue, 22 Mar 2005 11:31:45 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: enq: TX - index contention Thread-Index: AcUu+xxOWlPkyLUHRJKBCfy3NFtGzwACaHPw From: "Hollis, Les" To: , X-OriginalArrivalTime: 22 Mar 2005 17:29:33.0891 (UTC) FILETIME=[B68BB130:01C52F04] X-archive-position: 17587 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Les.Hollis@ps.net Precedence: normal Reply-To: Les.Hollis@ps.net 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.2 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: 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 -- http://www.freelists.org/webpage/oracle-l