Re: Inserts waiting on enqueue
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 26 Aug 2009 07:11:45 +0100
Message-ID: <5_6dnftlxYmDTwnXnZ2dnUVZ8q-dnZ2d_at_bt.com>
"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news: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?
>
>
>
>
>
>
>
Date: Wed, 26 Aug 2009 07:11:45 +0100
Message-ID: <5_6dnftlxYmDTwnXnZ2dnUVZ8q-dnZ2d_at_bt.com>
"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news: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?
>
>
>
>
>
>
>
Have you checked the Enqueue Stats section to prove that the enqueue was a TX enqueue ?
Have you done any large-scale deletes (or updates that change the primary key) recently?
Is this single row inserts or array inserts (you can't get 91.8 buffer gets per inserts if its single row on a table with a single unique index unless you have a high level of concurrency or which results in a lot of undo record fetching, have hit an anomaly relating to blocks on index freelists (or other anomalies which relate to ASSM - but you're not using that)).
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Wed Aug 26 2009 - 01:11:45 CDT