enqueue waits 
Author Message
 enqueue waits
hi all

i noticed that in my instance the enqueue waits increase with the hour,
at this moment the value is 11741, i get the hint increase the
ENQUEUE_RESOURCES
in initxxx.ora.
the value off this parameter is 265, how large can i make this parameter and
does this
parameter depends from another parameter ( DML_LOCKS? =200)

anyone with a suggestion ?

thanks




Wed, 18 Jun 1902 08:00:00 GMT
 enqueue waits

If your version has a ddl_locks parameter, keep your setting
below (dml_locks + ddl_locks + 20).

If your version does not have a ddl_locks parameter, keep
your setting below (dml_locks + 720).

Do not arbitrarily set this parameter way up.  Inch it up
gradually and examine the waits after each adjustment.

The value of the enqueue_resources parameter is calculated
roughly from the value of the sessions parameter.

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!



Wed, 18 Jun 1902 08:00:00 GMT
 enqueue waits
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.



Wed, 18 Jun 1902 08:00:00 GMT
 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

Quote:

> 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.



Wed, 18 Jun 1902 08:00:00 GMT
 enqueue waits


Quote:
> 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



- Show quoted text -

Quote:
> > 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.



Wed, 18 Jun 1902 08:00:00 GMT
 enqueue waits
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 {*filter*} 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.***.com/

Quote:

>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.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. enqueue wait event

2. Waited Too Long For Row Cache Enqueue Lock

3. waited too long for row cache enqueue lock

4. DBMS_AQ.Enqueue gives incorrect enqueue time

5. Users Waiting - What resource are they waiting on

6. q: buffer busy wait(data block wait) when P3=130

7. PL/SQL log sync waits, log file parallel write waits and redo writes

8. Process waiting after issuing OPEN CURSOR, onstat shows a thread in waiting condition await_MC1

9. Wait/No wait option, transaction and ODBC

10. Advanced Queuing: remote enqueuing/dequeuing

11. enqueue timeouts ?

12. how to get a msgid from enqueue in Visual Basic (using oo4o)


 
Powered by phpBB® Forum Software