Inserts waiting on enqueue

Giganews Newsgroups
Subject: Inserts waiting on enqueue
Posted by:  vsevolod afanassiev (vsevolod.afanassi…
Date: Tue, 25 Aug 2009

We have an application that has been around for several years and
never had any issues. It uses on AIX.

Several days ago Apps Support reported that it was slow for approx 15
minutes. No action was taken and the problem went away. I had a look
and found significant enqueue waits (this is for 15 min interval):

Top 5 Timed Events
% Total
Event                                              Waits    Time (s)
Ela Time
-------------------------------------------- ------------ -----------
enqueue                                            4,371
10,394    78.56
db file scattered read                            69,723
948    7.16
db file sequential read                            88,469
835    6.31
direct path write                                  22,921
201    1.52
sbtwrite2                                          14,332
198    1.50

In the Top SQL by Buffer Gets we have following:

                                                    CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
        288,278        3,140          91.8  25.4    6.57  10349.09
INSERT INTO JOURNAL (col1, col2, col3, col4) VALUES (:1, :2, :3, :4)

Normally the same statement runs much faster. 3000 executions will
take 10 seconds.
This is conventional table with VARCHAR2 and NUMBER columns.
The table has one unique index - on column populated from a sequence.
There are no triggers or FK constraints.

Why would INSERT wait on enqueue? I could understand UPDATE or DELETE,
For INSERT to wait on enqueue following has to occur:
1. There should be significant delay between INSERT and COMMIT for
some other reason.
2. Significant number of INSERTS should violate uniqueness.

Let's say at 00:00:00 session #1 inserts value 12345 but does not
commit until 00:00:10.
At 00:00:01 session #2 attempts to insert the same value. It will wait
on enqueue till 00:00:10, at 00:00:10 the insert will fail with
ORA-00001 "unique constraint violated"

However this sequence of events seems highly unlikely. In this
application there is no significant delay between INSERT and COMMIT,
and violation of uniqueness shouldn't occur as value for unique column
comes from sequence.

INSERT statemements may wait on enqueue if multiple inserts go into
the same block and there is not enough space for transactional
entries. This table is in non-ASSM tablespace and it was created with
default values on INITRANS (1) , MAXTRANS (255), and PCTFREE (10).
Average row size is 200 bytes, blocks size is 8K, so we have approx 40
rows per block. It is my understanding that Oracle uses any free space
in the block (including space reserved by PCTFREE) for transaction
entries. Free space reserved by PCTFREE is 800 bytes, and to
accomodate 40 transactional entries we need 40x23=920 blocks. So it
seems that space is sufficient.

Anything else I need to investigate?