Hi,
Let me explain. Months ago in a production environment after migrating from
AIX (no RAC) to Linux RAC with two nodes (12.1.0.2), we discovered
(unfortunately the business user discovered this odd problem)
that some caclulations are wrong and the results differ from call to call.
After invistigation, I have seen that some insert statements (direct load
and also the conventional) do not write all rows to the datafile (without
any error and randomly - from call to call the number of missing rows
differ).
I suspected parallel_local_force parameter (and it was just luck - not
based on something concrete). After setting the parameter to true,
everything worked fine (it was a big discussion as some people was again
setting this parameter to true). But since we had no another choice and we
didn't observe any performance issues, we set the parameter to true and
forgot about the whole thing.
Until this week a new environment was created with the old value
(parallel_local_force = false) and while trying to insert some
configuration data (as consecutive inserts, each select is a join between
DUAL table and another table and should insert only one row). I observed
again this problem and immediately remembred the problem I had last time,
after setting the parameter to true (on system level). The problem
disapeared.
My observation is after consecutive insert statements separated by a
commit, rows are randomly missed.
The insert statements (in the last case) don't use parallel processing, I
have even tested while setting parallel_max_servers = 1.
Unfortunately, I can't test much on this subject. My hope was that someone
faced something similar here.
Best regards
Ahmed
-----Original-Nachricht-----
Betreff: Re: setting the parameter parallel_force_local to false leads to
data loss
Datum: 2021-04-26T19:35:56+0200
Von: "Jonathan Lewis" <jlewisoracle@xxxxxxxxx>
An: "list, oracle" <oracle-l@xxxxxxxxxxxxx>
Presumably the inserts are "insert as select" where the selects are
executing as parallel queries. Are the inserts also parallel (parallel DML
enabled) or are the inserts carried out serially by the query coordinator.
If you're always expecting exactly 1,000 rows does this mean your select
has a clause "where rownum <= 1000"? A predicate like that MIGHT be the
root cause of the error.
Does your query plan include (before you set parallel_force_local) the
operation: PX SEND BROADCAST LOCAL
Does the query access partitioned tables
Does the query involve a UNION [ALL]
Are the query and its plan short enough to post without making people want
to stop looking the moment they see them.
Regards
Jonathan Lewis
On Mon, 26 Apr 2021 at 10:45, ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> <ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> > wrote:
Hi all,
does someone has experienced a problem with the parallel_force_local
parameter?
In a new created envirement, the database is EE 12.1.0.2.0 64 bit
(Redhat), I observed following: In a PL/SQL Block several inserts +
commit after each insert are performed. Sometime not all data are
commited:
Expected inserted rows is 1000. I repeated the test 5 times (each time
the table is truncated) and I got this:
1. only 127 rows are inserted (the sum of sql%rowcount = 1000)
2. 920 rows are inserted (the sum of sql%rowcount = 1000)
3. 1000 rows are inserted (the sum of sql%rowcount = 1000)
4. 1000 rows are inserted (the sum of sql%rowcount = 1000)
5. 1000 rows are inserted (the sum of sql%rowcount = 1000)
In the past I observed the same problem in our production environement
(after a migration from AIX to Linux) our regression test had same
problem, after each call a different result. My gut feeling I suspected
the parallel_force_local parameter that was set to false. After setting
the parameter to true everything works as expected (reg test is
consistent).
Best regards
Ahmed
