Re: parallel select
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx
- Date: Tue, 14 Jul 2009 11:01:39 -0700
On Tue, Jul 14, 2009 at 8:08 AM, <genegurevich@xxxxxxxxxxxx> wrote:
> Thank you for your Email. I never knew that PX granule size is 100M. How
> can I see that? Is there a DB parameter that determites that?
There is a hidden parameter that controls this. I'm purposely not
going to mention it here to discourage its use as a tuning knob, but
it should be very obvious to those who look for it.
> I have looked at the waits for the master and slave sessions. For the
> master session I see SQL*Net message to and from client waits
>
> For the slaves I see PX Deq Credit: send blkd and PX qref latch . Wait
> time for the latter is 0. I read that the qref latch means that consumers
> are not keeping up with the producers, but I am not sure how to correct
> that.
This will likely be the case. The scanners will be able to read the
data faster than the QC can write it out. Think many-to-one fan in.
If the table is partitioned it would be beneficial to run one export
session per partition to speed this up.
> The multiblock read count is currently 32 , The array size is 15, trimspool
> is off.
I mentioned array size 200 because the default of 15 is way too small
for big spool files. Using a 1MB (128 MBRC) will cut the I/O requests
by 4x.
> The filesystems are not local, they are on SAN. The data is spooled to a
> remote server. These will affect the timing, but there is
> not much I can do about them
Be mindful that the limiting factor will be the spool rate, so if it
is filesystem with few spindles, it will likely impact the rate.
> I will try increasing the MBRC and see if that helps the performance. But
> my concern is that most of the transactions against this
> database are quick small reads and I don't want to read extra data and slow
> things down for the online users.
The PX scan will do a direct path read (physical read) from disk, but
small quick reads (assuming non-PX) could be satisfied by cache
(buffer or storage). Also, it is unlikely that you can impact the
storage too much as you only have a single writer (the QC) so the PX
scan rate will be limited by this.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
