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

From: boa <root_at_localhost.net>
Date: Sun, 07 Mar 2004 07:18:12 GMT
Message-ID: <U0A2c.44466$BD3.8272155@juliett.dax.net>

VC wrote:
> Hello Joe,
>
> "Joe" <yung103_at_netvigator.com> wrote in message
> news:c2co7r$nk31959_at_imsp212.netvigator.com...
>

>>Hi Oracle Experts,
>>
>>Attached please find the statspack report I generated on 04-MAR-2004.
>>We are running Oracle eBuisness Suite on AIX.

>
> A cursory look at you statspack shows:
>
> 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 PK indexes was also a problem. Adding ITL's (INITRANS), more freelists and reversing the bytes on the index helped.

Bjørn
[snip] Received on Sun Mar 07 2004 - 01:18:12 CST