RE: TX Enqueues

From: Fedock, John (KAM.RHQ) <John.Fedock_at_us.kline.com>
Date: Wed, 7 Apr 2004 11:18:43 -0400
Message-ID: <5042FDC675779848A113F70D54F587490702B046@KAMRICEXCLUS.us.kline.com>

I suspect that my situation is related to 1 and/or 2 below. I have a = very intensive OLTP system with many thousand EDI records being = inserted/updated per hour. As it grows, these TX, mode 4 issues occure = more and more.

-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com] Sent: Wednesday, April 07, 2004 9:53 AM
To: oracle-l_at_freelists.org
Subject: RE: TX Enqueues - mode 4

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=3D1, then session b enters key=3D2, then session a enters = key=3D2, then session b enters key=3D1)
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=20 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_at_wangtrading.com]
Sent:	Wed 4/7/2004 9:40 AM
To:	oracle-l_at_freelists.org
Cc:=09
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 =3D 4. From all my research, I =
bet it is ITL related.=20

Transaction enqueues with LMODE=3D4? In my reference document, LMODE=3D4 is "shared", while TX enqueues are always with mode 6 like here:

QL> select * from v$lock where sid=3D28; =20
ADDR KADDR SID TY ID1 ID2 LMODE = REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- =

CTIME BLOCK
---------- ----------
734B89C0 734B8ACC 28 TX 196645 100864 6 = 0

62 0
=20
7345BFA8 7345BFBC 28 TM 40371 0 3 = 0

62 0
=20

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.
--=20
Mladen Gogala
Oracle DBA

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_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_at_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
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_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
-----------------------------------------------------------------

Received on Wed Apr 07 2004 - 13:41:21 CDT