Re: enqueue waits

In article <938124878.19071.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> 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
> >

Enqueue Waits can be caused by several things. Here is a list of what I know can cause them based on readings and posts I consider reliable:

--  enqueue waits are caused by
--  1) contention for specific row
--  2) tbl locks caused by unindexed FK
--  3) ST lock contention on non-temporary temp tablespace
--  4) no available transaction slot in block
--

The number of enqueue resources is calculated by Oracle, but you may be seeing waits before you need more than it is calculating. Try computing your waits and time-outs as a percentage of your requests. My number is less than 1/2 percent. If your requests and releases statisticsts are not close (relative value here) then you may need to bump the enqueue_resources init.ora parameter up. See the Reference manual for guidelines on changing this value. The dml_locks parameter is related and may also need adjusting. I advise go slow, observe results.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 24 1999 - 08:02:13 CDT