Re: parallel_force_local and parallel query

CP,
There are a lot of oracle clients using RAC for DWH. Oracle has thousands of Exadata machines out there, they all run RAC and a big % run DWH databases.
As Jonathan said, you should know how RAC works in order to profit from it. Back to parallel_force_local=True/False, it depends on what you have underneath, and the structure of your tables. For example, if you have partition-wise hash join, Oracle is usually smart enough to read and join the partitions on the same node. If you don't have partition-wise join, you may end up in the situation Jonathan explained: reading on one node, and sending part of the data to the other.

So, depending on your hardware/schema-design, you may be better off with setting it to false or to true.
One last note: if you use the in-memory option, you should not set it to true, and you should enable autoDOP.

Regards

On Tue, Feb 25, 2020 at 12:33 AM Cee Pee <carlospena999_at_gmail.com> wrote:

> 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