AW: setting the parameter parallel_force_local to false leads to data loss

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_at_gmail.com> An: "list, oracle" <oracle-l_at_freelists.org>      

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_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> > 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            



--
http://www.freelists.org/webpage/oracle-l