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

the parameter is set to false on system level (Rac with two instances)  

I can also remember that other customers were surprised that e.g. Multi insert in one statement, some rows have disappeared.(Unfortunately, I was not directly involved to analyze the problem. )  

So I wonder that no one from this list has encountered a similar problem: Oracle confirms that 1000 rows were inserted but fewer than that number were actually inserted.  

Best regards
Ahmed          

-----Original-Nachricht-----
Betreff: Re: setting the parameter parallel_force_local to false leads to data loss
Datum: 2021-04-26T17:58:21+0200
Von: "Tim Gorman" <tim.evdbt_at_gmail.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>, "list, oracle"
<oracle-l_at_freelists.org>
     

Were you setting the parameter with ALTER SESSION or ALTER SYSTEM?

It has always struck me as a bit odd that a parameter related to multi-session processing can be changed on a single session with ALTER SESSION. I understand that the session being changed is the QC and that is the only session that matters in this case, but still I wouldn't fully trust anything unless running ALTER SYSTEM for something involving parallelism.

Just a shot in the dark...

On 4/26/2021 2:45 AM, 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