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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Cee Pee <carlospena999@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
