RE: TX Enqueues

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 7 Apr 2004 11:27:00 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B4543A@bosmail00.bos.il.pqe>

John,

If the issue is #1 (ITL slot shortage), INITRANS can be increased, but for it to have effect on all blocks, the segment will need to be rebuilt. If it's 2 (primary key overlap on insert), then it's really an application design and/or coding issue.

To answer your original question, if, when the wait is happening, you look at the waiting session in V$SESSION, and join to V$SQL by SQL_ADDRESS and HASH_VALUE, you ought to be able to see the SQL that's causing the wait. That SQL should be doing (attempting to do) DML on a particular table. That table is the one causing you the problems.

Hope that helps,

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----
From: Fedock, John (KAM.RHQ) [mailto:John.Fedock_at_us.kline.com]=20 Sent: Wednesday, April 07, 2004 11:19 AM To: oracle-l_at_freelists.org
Subject: RE: TX Enqueues - mode 4

I suspect that my situation is related to 1 and/or 2 below. I have a =
=3D

very intensive OLTP system with many thousand EDI records being =3D inserted/updated per hour. As it grows, these TX, mode 4 issues occure
=3D

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 =3D INSERT, since Oracle is smart enough to grab another block off the =3D freelist. It still could happen on an index on the table, on an insert,
=3D
though)
Overlapping uncommitted primary key values in two sessions. (session
=3D
a enters key=3D3D1, then session b enters key=3D3D2, then session a = enters =3D key=3D3D2, then session b enters key=3D3D1)
Concurrent sessions overlap on usage of a bitmap index segment. =3D (This is why OLTP and bitmap indexes do not mix.)
Too many freelists for a segment can cause a shortage of transaction=3D20 freelists. (In my experience this is very rare. I've never seen it =3D 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:=3D09
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 =3D3D 4. From all my research, = I
=3D

bet it is ITL related.=3D20

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

QL> select * from v$lock where sid=3D3D28;
=3D20

ADDR KADDR SID TY ID1 ID2 LMODE =3D REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- =3D

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

0

62 0
=3D20

7345BFA8 7345BFBC 28 TM 40371 0 3
=3D

0

62 0
=3D20

Here, I have a locked row. TX lock (row lock) is mode 6 (eexclusive) and
=3D

DDL lock (TM)
is mode 3 (Shared, row-exclusive). I don't see how can you have 4 in the
=3D

LMODE field
and TX in the Type field.
--=3D20
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:42 CDT