Inserts waiting on enqueue
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 ElapsdBuffer 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