Re: perplexing performance on AIX
Date: Wed, 7 May 2008 11:57:28 +0200
My understanding of this event is that it happens when the slaves wait for
the coordinator (or consumer slaves) to consume the data they are ready to
send. So, I imagine two scenarios:
A) the coordinator waits on "SQL*Net message from client" (that is, is idle). Hence it does not need the data yet. The slaves, on the other hand, are not waiting for a SQL*Net message of any kind, so they book their time on "PX Dequeue Credit: Send Blkd" instead. In this case, it is a truly an idle wait you can safely ignore.
SQL*Plus script to simulate this behaviour (for any table t holding more than 10 rows):
set pages 10
set pause on
select /*+ parallel(t) */ rownum from t;
-- press Enter to get the first ten records and look at your wait events in
Database Control to see wait times accumulating
B) there are many slaves and only one coordinator. For some queries, the slaves have nothing else to do than read blocks from disk - blocks that are often in the SAN cache, so they (collectively) can read them much faster than the coordinator can do everything else the query asks it to do. The slaves have to politely wait for their master to turn its attention to them. While waiting, they book their time on "PX Dequeue Credit: Send Blkd". So from the slaves' perspective, this is again an idle wait. From the DBA perspective - while it may be an indication that the query can be parallelized better (perhaps you have one parallel-to-serial distribution too many in your execution plan?) - most of the time it just depicts the fact that many processes will usually outperform a single process.
All told, large wait times for this event only demonstrate that the bottleneck is NOT in the parallel slaves doing the waiting. You have to look for the bottleneck elsewhere. In the meantime, waiting processes tend to consume little resources, so you should not really worry about them.
HTH Vladimir Andreev
Sitz: Frankfurt am Main
Amtsgericht Frankfurt am Main: HRB 77499 Geschäftsführer: Dr. Jan Mrazek, Jan Cervinka