RE: parallel query performance

Riyaj,
 Nicely put. I think you covered it all.

 There is a note # 39599.1 on Metalink that provides a nice overview of the operation of PQ and PQ slaves.

 Thanks,  

  Regards,

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Riyaj_Shamsudeen_at_i2.com [SMTP:Riyaj_Shamsudeen_at_i2.com]
> Sent: Friday, June 08, 2001 12:28 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: parallel query performance
>
>
> Hi Elain
> I had similar questions some time ago and went through a small
> experiment/analysis to understand.
> When you start a parallel query process, following happens:
> 1. Current SCN is captured for the consistent or snapshot
> SCN.
> 2. Objects accessed by the query is flushed to the disk.
> Now, there are multiple scenarios / issues with this approach:
>
> PQ architecture: Parallel Query
>
> 1. Say, Current SCN/consistent is 150 and there is a session
> modified the block ( with SCN 140) that PQ slave interested but not
> committed.
> In this case, object level checkpoint will flush the modified
> block and hence the direct reads will read the updated block and go back
> to rollback segment to get the consistent copy of the block. So, it seems
> to me that PQ slaves may have to go back to buffer cache in few cases. Can
> anybody confirm this ?
> 2. Say, Current SCN/consistent is 150 and there is a session
> modified the block ( with SCN 140) that PQ slave interested but committed
> In this case, object level checkpoint will flush the modified
> block and hence the direct reads will read the correct block version.
>
> After the object level checkpoint, the PQ slave started reading
> the blocks. Before PQ slave can read a specific block, another session
> came in and read a block in to the buffer cache and modified the block.
> Since PQ slave does not need to see the modified block, this does not
> matter.
> So, I don't see any consistency issues with this.
>
> PX architecture: Parallel Execution.
>
> In case of PDML, there are two variations:
>
> 1. For partitioned tables: After the object checkpoint, the Query
> coordinator takes exclusive locks on the partitions. so, there is no
> issues here, like PQ.
> 2. For non-partitioned tables: PQ slaves allocates temporary
> segments above the high water mark and hence there is no issues here
> either.
>
> Now, the question is what if I have a normal DML within the same
> transaction as my PDML ? Since regular DML will read the block in to the
> buffer cache and update and since my transaction holds the locks on the
> table, how does this is coordinated ? Well, you can't have any other DML
> in the same transaction as PDML:-)
> Also, the PX message buffers are allocated in shared pool if the
> parallel_automatic_tuning is set to false and allocated in large pool if
> the parallel_automatic_tuning parameter is true.
>
> Listers, Feel free to correct me/educate me if I have missed
> something..
>
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
> i2 technologies www.i2.com
>
>
>
> "elain he" <elainhe_at_hotmail.com>
> Sent by: root_at_fatcity.com
>
> 06/08/01 11:17 AM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: parallel query performance
>
>
>
> Riyaj,
> Thanks for your reply. I have a follow up question. What happen when a
> transaction is commited and the block has not been flushed out to disk
> yet?
> If I have parallel query processes that need to read the commited blocks
> in
> the buffer cache, then what happens. Since PQ processes do not read from
> buffer cache, how do the processes access the updated blocks? The disk
> does
> not have the updated information yet.
>
> Thanks.
>
> elain
>
>
> >From: Riyaj_Shamsudeen_at_i2.com
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: parallel query performance
> >Date: Fri, 08 Jun 2001 06:40:27 -0800
> >
> >Hi Elain
> > Yes, that is true. SGA is useful if you want to cache the block,
> >so that many sessions can avoid the disk reads. But, in case of Parallel
> >Query architecture, mostly, only the parallel query slave is interested
> in
> >the block. That's why slave process directly reads the blocks in to the
> >PGA bypassing the Buffer cache. Of course, object level checkpoint occurs
> >and there are some optimization in 8i versions. That is the reason,
> there
> >is no point in increasing the buffer cache, if most of the work is done
> >through parallel query slaves.
> >
> >But, the communication buffers between the slave processes are stored in
> >the shared pool though.
> >
> >Thanks
> >Riyaj "Re-yas" Shamsudeen
> >Certified Oracle DBA
> >i2 technologies www.i2.com
> >
> >
> >
> >
> >"elain he" <elainhe_at_hotmail.com>
> >Sent by: root_at_fatcity.com
> >06/08/01 07:40 AM
> >Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: parallel query performance
> >
> >
> >Hi,
> >Is it true that when running parallel query, the parallel query slaves do
> >not read from buffer cache even though the data resides in the buffer
> >cache
> >ie the slaves read directly from disk.
> >
> >Does not make sense to me. Can someone clarify that?
> >
> >Thanks.
> >
> >elain
> >_________________________________________________________________
> >Get your FREE download of MSN Explorer at http://explorer.msn.com
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: elain he
> > INET: elainhe_at_hotmail.com
> >
>
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).