Re: parallel_force_local and parallel query

From: Cee Pee <carlospena999_at_gmail.com>
Date: Mon, 24 Feb 2020 17:31:15 -0600
Message-ID: <CAPTPB11tiukFmHB=6xMmvVkJjTwb9-4O4d7NB+3_2VTWwtLOBQ_at_mail.gmail.com>



Jonathan, that is exactly my concern about using RAC for a data warehouse. Do people who run DW on RAC (are there any?) use services for *application* partitioning along with this parameter to control the data transfer madness over interconnect? But with DML, unless good application partitioning strategy is used, parallel_force_local may not be able to do much, if one node has to request uncommitted data from a different node.

I would think in that case the application has to be configured such that all the DDL/DML/selects for a table happen in one node only.

On Mon, Feb 24, 2020 at 5:16 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

> The point of parallel_force_local is that the query coordinator and all
> its PX slave processes operate on the same node so that any PQ messages
> areinter-process on the node and no PQ messages (for the query) have to
> travel across the interconnect.
>
> If you think about a parallel hash join using hash/hash distribution, for
> example; this will use two slave sets the first slave set scans the first
> table and distributes the interesting rows to the second slave set, then
> the first slave set scans the second table and distributes it's rows in the
> same way to the second slave set. If you don't set parallel_force_local to
> true then you could (in theory, though Oracle always makes some attempt to
> avoid it) end up with the first slave set on one node and the second slave
> set on another node, and all the data flying across the interconnect before
> any joining gets done - them maybe the entire result set could go flying
> back across the interconnect because the Query coordinator is on a third
> node. A single bad parallel query could flood the interconnect and
> basically kill the system - to the extent that nodes could get ejected
> because they couldn't get their heartbeat messages through fast enough.
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Cee Pee <carlospena999_at_gmail.com>
> Sent: 24 February 2020 20:38
> To: Oracle-L Freelists
> Subject: parallel_force_local and parallel query
>
>
> Hopefully not a dumb question, By setting parallel force local to TRUE all
> the queries for a particular table and other objects happen in the local
> instance only. What happens if a different query is issued from a different
> node for the same table or object. Does the data get transferred over the
> interconnect, if data is still cached in the first node. Assume SELECTs
> only in both nodes.
>
> Thanks
> CP
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 25 2020 - 00:31:15 CET

Original text of this message