Re: need help on statspack report, Parse CPU to Parse Elapsd %

From: boa <root_at_localhost.net>
Date: Sun, 07 Mar 2004 14:41:01 GMT
Message-ID: <1wG2c.44482$BD3.8276621@juliett.dax.net>

VC wrote:

> Hello Bjorn,
>
> "boa" <root_at_localhost.net> wrote in message
> news:U0A2c.44466$BD3.8272155_at_juliett.dax.net...
>

>>>1.  High enqueue wait time.  The usual cause is that several sessions

>
> are
>

>>>trying to lock the same row (SELECT FOR UPDATE probably?) and the

>
> average
>

>>>wait time is 3 seconds per enqueue.  Other reasons may be bitmap indexes
>>>update/unindexed foreign keys/lack of ITL slots,  in the order of

>
> decreasing
>

>>>probability.
>>>
>>
>>Another reason may be freelist shortage. We have an application where
>>lots of users insert new rows to the same tables. The PK is generated
>>from a sequence. We had lots of enqueue waits and some Buffer Busy
>>Waits/Data Block waits. Adding more freelists as well as more ITL's
>>solved most of the enqueue waits.
>>

>
>
> The enqueue waits have got nothing to do with freelists. The OP does not
> have buffer busy waits in the top 5 wait events therefore tweaking freelists
> appers to be quite unnecessry. ITLs may or may not be a problem which will
> become clear after checking for more comon causes which I mentioned in my
> earlier posting. The 3 seconds enqueue timeouts indicate a possible an
> application problem.
>

I guess you're right, just wanted to share my experience. Almost all of our enqueue waits, and we had a lot, disappeared when we added more freelists to a few busy tables. I tracked both the number of enqueue waits and buffer busy/data block waits for weeks, as well as the correlation between them. The events correlated a lot, even if we didn't see buffer busy wait events on the Top 5 list.

Bjørn

>

>>Bjørn
>>[snip]

>
>
>
Received on Sun Mar 07 2004 - 08:41:01 CST