From oracle-l-bounce@freelists.org Wed Apr 7 12:41:19 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i37HfHY10486 for ; Wed, 7 Apr 2004 12:41:17 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org ([206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i37HeQo10307 for ; Wed, 7 Apr 2004 12:41:16 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4FC31634C7D; Wed, 7 Apr 2004 08:44:43 -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 15970-70; Wed, 7 Apr 2004 08:44:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9A1B6634825; Wed, 7 Apr 2004 08:44:42 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 07 Apr 2004 08:43:34 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DBC6563499D for ; Wed, 7 Apr 2004 08:43:33 -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 16686-02 for ; Wed, 7 Apr 2004 08:43:33 -0500 (EST) Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6BB62634637 for ; Wed, 7 Apr 2004 08:43:33 -0500 (EST) Received: from aabo-exchange04.bos.il.pqe (aabo-exchange04.bos.il.pqe [172.24.3.67]) by tera.umi.com (8.12.10/8.12.10) with ESMTP id i37DqrY3027648 for ; Wed, 7 Apr 2004 09:52:53 -0400 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by aabo-exchange04.bos.il.pqe with Microsoft SMTPSVC(6.0.3790.0); Wed, 7 Apr 2004 09:52:53 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain Subject: RE: TX Enqueues - mode 4 Date: Wed, 7 Apr 2004 09:52:53 -0400 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC93@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC93@bosmail00.bos.il.pqe> Thread-Topic: TX Enqueues - mode 4 Thread-Index: AcQcpgXBs7T3frNCSHyQbzJPqd/8WgAAKz2U From: "Bobak, Mark" To: X-OriginalArrivalTime: 07 Apr 2004 13:52:53.0605 (UTC) FILETIME=[9F9AB950:01C41CA7] X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 2686 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Mladen, There are certainly cases where TX enqueues can wait on mode 4, that is, where REQUEST is mode 4. Off the top of my head: - ITL slot shortage (note that this will not happen to tables on INSERT, since Oracle is smart enough to grab another block off the freelist. It still could happen on an index on the table, on an insert, though) - Overlapping uncommitted primary key values in two sessions. (session a enters key=1, then session b enters key=2, then session a enters key=2, then session b enters key=1) - Concurrent sessions overlap on usage of a bitmap index segment. (This is why OLTP and bitmap indexes do not mix.) - Too many freelists for a segment can cause a shortage of transaction freelists. (In my experience this is very rare. I've never seen it outside of a constructed experiment to prove it can happen.) Hope that helps, -Mark -----Original Message----- From: Mladen Gogala [mailto:mladen@wangtrading.com] Sent: Wed 4/7/2004 9:40 AM To: oracle-l@freelists.org Cc: Subject: Re: TX Enqueues - mode 4 On 04/07/2004 09:01:48 AM, "Fedock, John (KAM.RHQ)" wrote: > I know I have TX enqueues, with a mode = 4. From all my research, I bet it is ITL related. Transaction enqueues with LMODE=4? In my reference document, LMODE=4 is "shared", while TX enqueues are always with mode 6 like here: QL> select * from v$lock where sid=28; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST -------- -------- ---------- -- ---------- ---------- ---------- ---------- CTIME BLOCK ---------- ---------- 734B89C0 734B8ACC 28 TX 196645 100864 6 0 62 0 7345BFA8 7345BFBC 28 TM 40371 0 3 0 62 0 Here, I have a locked row. TX lock (row lock) is mode 6 (eexclusive) and DDL lock (TM) is mode 3 (Shared, row-exclusive). I don't see how can you have 4 in the LMODE field and TX in the Type field. -- Mladen Gogala Oracle DBA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: application/ms-tnef -- File: winmail.dat ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------