Re: enqueue waits

Hi Roy,
Truncate is a DDL action. This means in normal circumstances dictionary tables like obj$ and tab$ will be locked. Insert is a DML action. It needs to acquire a Table Manipulation lock, which is probably accompanied by a row level lock on the dictionary tables. IMO, this has nothing to do with rollback, a truncate will not be logged anyway.

Hth,

--
Sybrand Bakker, Oracle DBA
<rspeaker_at_my-deja.com> wrote in message news:7se5ed$qib$1_at_nnrp1.deja.com...
> Hi,
>
> I am running Oracle 8.0.5 on AIX. I have a question about enqueue
> waits. The instance has 10 rollback segments, 10 MB each (thanks to
> shrinking back to the optimal size), and 1 LARGE RBS (rbslarge) that is
> about 2.5 times large than the regular ones.
>
> 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.
>
> As soon as my truncate finished, UserB's insert finished and the enqueue
> waits went away. My question is this ... I did not create the rbslarge
> as a private rbs, so I assume it defaults to public, meaning anybody can
> hit it in the round-robin rbs cycle. Is it possible that both me and
> UserB grabbed rbslarge, and even though his data was in a separate
> table, in a separate tablespace, on a separate disk than mine, we were
> contending for rollback? There are no entries in the alert log or trace
> files indicating any type of rollback contention.
>
> Any info is appreciated ... I'm still fuzzy on the whole enqueue thing.
>
> Thanks,
> Roy
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 23 1999 - 17:13:22 CDT