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

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 < 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