CP,
In Exadata, lots of things are offloaded to storage cells, from projections
to predicates to joins (bitmaps for hash joins), to some aggregations
(depending on the version), ..., but this does not change the how the
parallel operations are done, and what I said about partition-wise joins
remains true.
As for in-memory, if you're using RAC, you must NOT use
parallel_force_local=true. In RAC, you generally (unless you're on exadata
and decided to duplicate the inmemory area) have say some partitions' data
in the in-memory area of node 1, some other partitions in node 2, ... so if
you force local parallel query, it will not be able to use the in-memory of
the other nodes, and it will have to read from disk.
Regards
On Tue, Feb 25, 2020 at 9:02 PM Cee Pee <carlospena999@xxxxxxxxx> wrote:
Thanks Ghassan. I thought they moved the compute power closer to the data
in exadata; ie, lots of processing were happening in the storage itself and
the aggregates were sent to the DB server from storage for further
processing. If that is the case there may not be much transfers happening
via cache fusion in exadata - a guess on my part.
Can you explain why I should not parallel_force_local to true when using
in memory option?
On Tue, Feb 25, 2020 at 3:23 AM Ghassan Salem <salem.ghassan@xxxxxxxxx>
wrote:
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
