perplexing performance on AIX

  • From: "John Smith" <john40855@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 6 May 2008 12:43:20 -0500

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 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?

Other related posts: