Inserts waiting on enqueue

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Tue, 25 Aug 2009 17:58:29 -0700 (PDT)
Message-ID: <932dfa1e-330b-489e-8386-aed644701683_at_u16g2000pru.googlegroups.com>



We have an application that has been around for several years and never had any issues. It uses 9.2.0.8 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 2179811325
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, but INSERT?
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? Received on Tue Aug 25 2009 - 19:58:29 CDT

Original text of this message