Re: perplexing performance on AIX
- From: Flado <vandreev@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 7 May 2008 11:57:28 +0200
Hi,
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
www.adastracorp.com
--
Adastra GmbH
Sitz: Frankfurt am Main
Amtsgericht Frankfurt am Main: HRB 77499
Geschäftsführer: Dr. Jan Mrazek, Jan Cervinka
Other related posts:
