Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using PQ in FTS

RE: Using PQ in FTS

From: Hameed, Amir <>
Date: Wed, 28 Mar 2007 09:44:47 -0400
Message-ID: <>

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 [] 
	Sent: Wednesday, March 28, 2007 5:03 AM
	Cc: Hameed, Amir; Arul Ramachandran;
	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 occure.         

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



		On 3/26/07, Hameed, Amir <> wrote:

> No, PQ used by batch jobs.
> ________________________________
> From: Arul Ramachandran
> Sent: Monday, March 26, 2007 1:08 PM
> To: Hameed, Amir
> Cc:
> 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 <
<> > 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 ** --
Received on Wed Mar 28 2007 - 08:44:47 CDT

Original text of this message