| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using PQ in FTS
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 9.2.0.6 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?
Thanks
Amir
From: Frits Hoogland [mailto:frits.hoogland_at_gmail.com] Sent: Wednesday, March 28, 2007 5:03 AM To: john.kanagaraj_at_gmail.com Cc: Hameed, Amir; Arul Ramachandran; oracle-l_at_freelists.org Subject: Re: Using PQ in FTS If mixed access (meaning both buffered and direct access) isdone 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 9.2.0.6/linux 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.
frits
On 3/27/07, John Kanagaraj <john.kanagaraj_at_gmail.com> wrote:
Amir,
Have a look at http://oracledoug.com/px.html - 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
usage...
John
On 3/26/07, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
>
>
> No, PQ used by batch jobs.
>
>
> ________________________________
> From: Arul Ramachandran
[mailto:contactarul_at_gmail.com]
> Sent: Monday, March 26, 2007 1:08 PM
> To: Hameed, Amir
> Cc: oracle-l_at_freelists.org
> 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
queries...
>
>
> Arul
>
>
>
> On 3/23/07, Hameed, Amir < Amir.Hameed_at_xerox.com
<mailto:Amir.Hameed_at_xerox.com> > 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 9.2.0.6 and 10.2.0.2.
> >
> > Thanks
> > Amir
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
>
> --
> 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 ** -- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 28 2007 - 08:44:47 CDT
 |
 |