Re: TX Enqueues

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 10 Apr 2004 21:35:46 +0100
Message-ID: <010b01c41f3c$619574a0$7102a8c0@Primary>

Another possibility is that transactions are queueing on simple row updates
on IOTs (i.e. something that would
give a mode 6 TX lock for heap table
gives a mode 4 on IOTs)

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, April 07, 2004 2:52 PM 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=1, then session b enters key=2, then session a enters key=2, then session b enters key=1)
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 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

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 Sun Apr 11 2004 - 06:39:48 CDT

Re: TX Enqueues - mode 4