Re: perplexing performance on AIX

May be related:

I have a merge statement that sometimes hangs. In fact the Oracle shadow is consuming CPU but it doesn't get accounted for in V$sesstat / v$sysstat (haven't checked that but I believe that to be the case). So I see this shadow process consuming CPU, I took gdb and attached my self and took a stack trace. I found that the merge was doing an insert and for that it had to find more space in the index. While finding the space it seems to be in a tight loop in the space management code for bitmap segments (ASSM). The workaround from Oracle is to not use ASSM for the Index. So if your case one of the slaves (or coordinators) hits this problem it won't sent a completition message to the coordinator. This then could result in the behavior that you see.

Anjo.

On 5/6/08, John Smith <john40855_at_gmail.com> wrote:
>
> I have been working with an AIX LPAR which has access to 6 CPU's, defaults
> to 3. Its primarily a data warehouse instance, so we enabled parallel on it
> and about half of the tables are large, and are range-partitioned on by
> year, and in some cases sub-partitioned on accounting period (list method).
>
> There is plenty of memory (32G), and its on an EMC Clariion storage.
> However, when we run loads using merge, and in some cases, some very large
> queries, the queries run for days at a time, but the CPU usage is not all
> that high (rarely above about 60%).
>
> The AWR report (its oracle 10.2.0.3) shows a huge number of wait events on
> PX Dequeue Credit: Send Blkd. According to the oracle documentation, this
> is an idle event, but it appears to me that about 2/3 of the available time
> is spent waiting on this event. Has anyone seen this, or have any idea how
> to track down what is causing the problem? There are also a large number of
> IO wait events, but the time is not significant compared to the PX wait
> event. Is it possible that PX wait event is caused by the IO wait event?
> If so, how could I track it down?
>

-- 
Anjo Kolk
Owner and Founder OraPerf Projects
tel:    +31-577-712000
mob: +31-6-55340888

--
http://www.freelists.org/webpage/oracle-l