RE: TX Enqueues - mode 4
- From: "Fedock, John (KAM.RHQ)" <John.Fedock@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Apr 2004 11:18:43 -0400
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@xxxxxxxxxxxxxxx]
Sent: Wednesday, April 07, 2004 9:53 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx]
Sent: Wed 4/7/2004 9:40 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
