Doug,
I don't know of any way of proving ITL contention
other than doing a block dump of the block in question
while it is hanging.
Are you saying that the info for ROW_WAIT_FILE# and
ROW_WAIT_BLOCK# in V$SESSION doesn't exist or is -1
for the session waiting on the TX lock in mode 4?
Are there any triggers on the table that could be
causing the locking issue?
There are some share locks held against distributed
tx's. Do you have any distributed tx's or anything
else (e.g. XA) that could be using the two-phase
commit process? Normally these locks aren't held long
enough to be noticeable, but I imagine that if there
were a problem with the tx and it were left in an
in-doubt status the locks would remain until RECO
cleaned it up. You might check DBA_2PC_PENDING and
DBA_2PC_NEIGHBORS.
HTH,
- Anita
- Doug C <dcowles_at_i84.net> wrote:
> I have an insert statement that is hung waiting for
> a type 4 lock. I have dug
> around a bit and found that a common problem is not
> enough space for ITL in
> block headers.
>
> However, the table in question is set at Oracle
> defaults - 10% free 40% used,
> initrans 1, maxtrans 255 on a 4K block size.
> (Oracle 8.1.7 on AIX 4.3.3)
>
> There are only two sessions in the entire database.
> So, I find it hard to
> believe that a block would be on the free list (40%)
> without having enough room
> to accomodate what I assume are 2, (only 2),
> interested transactions.
>
> So - I pose the following -
> 1) Is there a way to prove or disprove an ITL
> problem? There is no specific
> block, row, object in v$session being waited on.
> The v$session_wait indicates
> an enqueue
>
> 2) Can anyone suggest any other scenarios where a
> transaction which shows an
> active insert would be sitting around waiting for a
> type 4 (S) lock
> indefinitely.
>
> To my knowlege, the blocking sid has nothing more
> than a large pile of inserts
> on the same table that are uncommited. Also,
> primary key contention is not an
> issue because sequences are used for the primary
> key. All foreign keys are
> indexed.
>
> Thanks,
> Doug
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Doug C
> INET: dcowles_at_i84.net
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Aug 06 2001 - 01:48:13 CDT
