RE: Using PQ in FTS

  • From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
  • To: <john.kanagaraj@xxxxxxxxx>
  • Date: Wed, 28 Mar 2007 09:44:47 -0400

One of the things that I am struggling with is how to determine the
following final statistics of the parallelized statement from the 10046
trace files: "cpu", "elapsed", "disk"
This is a instance and when I look at the 10046 trace files of
the parallelized statement (QC and its Slave processes) and compare the
statistics with the 10046 trace file of the serialized statement, I see
the following:
-- The "rows" and "fetch" count statistics from QC's trace file match
exactly with the statistics obtained from the serialized execution of
the statement. So, these are the final statistics.
-- The "disk" statistic aggregated from trace files of "QC + P000 +
...P0007" came out to be quite less than what I see from the serialized
execution. For example, the aggregated "disk" statistic from all slave
(there were eight of them) and QC processes is 3,110,518 where as it is
5,860,777 for the serialized- statement. I was expecting that in an
optimal scenario where the serialized-statement was able to find/get
some percentage of data blocks from the buffer cache during FTS, the
aggregated disk reads from the parallelized-statement would be greater
than that of the serialized-statement; or in a worst case scenario where
the serialized-statement was not able to find any data block from the
buffer cache during FTS and it had to read all blocks from the disk, the
disk reads of the parallelized-statement would be very close to the disk
reads from the serialized-statement.
Jonathan has shed some light on the "query" statistic obtained from the
QC and P00n trace files:
PX only bypasses the cache for table scans and index fast full scans.
There may be indexed access components in you plan. However, even if you
do no indexed access, the blocks that have been read direct have to be
made read-consistent. 10g has a statistic to make it clear that this
happens: "consistent gets direct".
So, how do I answer the following from the 10046 trace files:
-- How much "CPU time" was spent by a query that was run in parallel
with "x" number of slaves?
-- What was the actual "elapsed time" of a query that was run in
parallel with "x" number of slaves?
-- If the "disk" statistic is the aggregated statistic obtained from the
QC and all slave processes then why is it much smaller than that
obtained from the serialized statement?


        From: Frits Hoogland [mailto:frits.hoogland@xxxxxxxxx] 
        Sent: Wednesday, March 28, 2007 5:03 AM
        To: john.kanagaraj@xxxxxxxxx
        Cc: Hameed, Amir; Arul Ramachandran; oracle-l@xxxxxxxxxxxxx
        Subject: Re: Using PQ in FTS
        If mixed access (meaning both buffered and direct access) is
done on objects where DML is happening, it can result in buffer busy
waits because extents needs to be checkpointed before direct access can
        Seen a great deal of buffer busy waits in where
concurrent DML queries got downgraded to serial due to the parallel
automatic tuning.
        This is probably true for higher versions (because of how PX is
implemented) of the database, but haven't tested it. 
        On 3/27/07, John Kanagaraj <john.kanagaraj@xxxxxxxxx> wrote: 

                Have a look at - Be aware
that you set
                PQ's init.ora parameters properly, specially to limit
the number of PQ
                servers: Otherwise, you might end up choking on CPU on
                On 3/26/07, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
                > No, PQ used by batch jobs.
                >  ________________________________ 
                >  From: Arul Ramachandran
                > Sent: Monday, March 26, 2007 1:08 PM
                > To: Hameed, Amir
                > Cc: oracle-l@xxxxxxxxxxxxx
                > Subject: Re: Using PQ in FTS
                > I was going to say unintended block cleanouts.. but
then glanced Jonathan
                > Lewis' book page 30 where it mentions 'segment
checkpoint', that sounds 
                > almost like block cleanouts....
                > I suppose you did not mean PQ will be used by OLTP
                > Arul
                > On 3/23/07, Hameed, Amir < Amir.Hameed@xxxxxxxxx
<mailto:Amir.Hameed@xxxxxxxxx> > wrote:
                > > Folks,
                > > If there are statements where FTS on large tables is
unavoidable due to
                > > the logic in the statements, is there a downside of
using PQ to scan 
                > > those tables? Jonathan has mentioned one caution on
page 30 of his book
                > > but are there any other issues that one must be
aware of ? The RDBMS
                > > version are and
                > >
                > > Thanks
                > > Amir
                > >
                > > --
                > > 
                > >
                > >
                > >
                > --
                > Arul
                John Kanagaraj <><
                DB Soft Inc
                Phone: 408-970-7002 (W)
                ** The opinions and facts contained in this message are
entirely mine 
                and do not reflect those of my employer or customers **

Other related posts: