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 //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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------