Re: enqueue waits

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 24 Sep 1999 14:34:35 +0100
Message-ID: <938180498.7962.0.nnrp-10.9e984b29@news.demon.co.uk>

One point:

    Truncate is a DDL, so issues a commit before     and after operating. This means your 'set transaction..'     would immediately be disabled.

In theory you should not expect the TRUNCATE of one table to stop an INSERT into another table. But there are a couple of possibilities:

TRUNCATE is a space transaction.
If the insert forces the allocation of a new extent in the other table, that invokes a recursive space transaction - but there is only one ST lock, so the insert might have to wait until the TRUNCATE has completed.

TRUNCATE forces all dirty blocks in all related objects (i.e. the table and its indices) to be written to disc - the insert might result in a demand for (one or many) rollback segment blocks to be read from disc. Under extreme circumstances it might be possible for the writing to delay the reading long enough for one of the exotic internal enqueues to take much longer than usual. But this is just a wild hypothesis.

If the problem recurs, check the P1RAW value of the session wait. Take the 1st 4 bytes, and convert from hex to ascii, the last byte is the enqueue mode requested. (Actually if it is a normal type of enqueue, you will see it in v$lock, with REQUEST as the mode
requested). This may help you to track the problem down.

    e.g. 53540006

            x53    = 'S'
            x54    = 'T'

The wait is for an exclusive ST lock.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

rspeaker_at_my-deja.com wrote in message <7se5ed$qib$1_at_nnrp1.deja.com>...

>I was logged into the database as UserA, and issued a set transaction
>use rollback segment rbslarge;, then issued a truncate table TableA
>(which lives in tablespaceA). About the same time, UserB issued an
>insert into TableB (which lives in tablespaceB), and encountered a wait
>situation. UserB waited. and waited. and waited some more.
>v$session_wait showed about a dozen 'enqueue' wait events.
Received on Fri Sep 24 1999 - 08:34:35 CDT