RE: TX Enqueues - mode 4

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@xxxxxxxxxxxx]=20
Sent: Wednesday, April 07, 2004 11:19 AM
To: oracle-l@xxxxxxxxxxxxx
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@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 =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@xxxxxxxxxxxxxxx]
Sent:   Wed 4/7/2004 9:40 AM
To:     oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: