Re: parallel_force_local and parallel query
Date: Mon, 24 Feb 2020 23:42:10 +0000
Message-ID: <LNXP265MB156275A24C8F3EFF2B1B39D0A5EC0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
Generally speaking a DW setup on RAC should be engineered so that the DML for an object (or segment, to be a little fussier) should take place only on a single node so that you don't have to worry about the overheads of large volumes of cache fusion. This tends to be easier to manage on DWs than on OLTP systems because DWs tend to get their data in using a small number of bulk transactions.
Even restricting the DML to a single node you have to be careful not to allow other nodes to execute ad hoc queries for the changing data otherwise the DML node either has to do a load of read-consistency work and fire the CR blocks across the interconnect, or write dirty blocks down to disc and allow the other nodes to read the changed blocks back (into their PGAs for parallel queriies) and then called for the undo blocks from the first node to create their own CR copies.
People do do DW on RAC - but both DW and OLTP require careful thought to minimise the competition for the same data blocks between nodes,
Regards
Jonathan Lewis
From: Cee Pee <carlospena999_at_gmail.com> Sent: 24 February 2020 23:31
To: Jonathan Lewis
Cc: Oracle-L Freelists
Subject: Re: parallel_force_local and parallel query
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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Cee Pee <carlospena999_at_gmail.com<mailto: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:42:10 CET