Re: parallel_max_servers and the number of sessions involved in a SQL

Going back to the original problem, then - if you search MOS for "wrong results parallel 12.1.0.2" you'll find there are plenty of bugs that might be relevant.
Is this a very recent upgrade to 12.1.0.2? It hasn't been the ideal target for an upgrade for several years (unless you're constrained by the application provider, of course).

Regards
Jonathan Lewis

On Sat, 28 Nov 2020 at 19:42, ahmed.fikri_at_t-online.de < ahmed.fikri_at_t-online.de> wrote:

>
>
>
>
> on 11.2.0.4 it is a little different: only the first call behaves like the
> 12.1 (number of sessions = 40 * cpu +1), the second call -> 4 * cpu +1.
>
>
>
> Regarding my problem, I just saw that the parallel_force_local parameter
> is set to false (the default). We have a RAC with two instances, one
> should be used by the applications and the second should only be used by
> the DR. It seems that Oracle has a bug when both instances are used in
> one statement. Even so, the database was designed in such a way that the
> application node is separated from the standby database.
>
> I hope setting this parameter to true solves my problem.
>
>
>
>
>
> Best regards
>
> Ahmed
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: AW: parallel_max_servers and the number of sessions involved in a
> SQL
>
> Datum: 2020-11-28T18:16:44+0100
>
> Von: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
>
> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>, "
> jlewisoracle_at_gmail.com" <jlewisoracle_at_gmail.com>, "list, oracle" <
> oracle-l_at_freelists.org>
>
>
>
>
>
>
>
> sorry my bad. Indeed, there is a correlation with the process number and
> the pga (which also logical is):
>
> After setting the pga_aggregate_target to 10M (an extreme value) and
> processes to 1500, I got this correlation:
>
>
>
> cpu (host) n_max
> 1 40
> 2 80
> 3 120
> 4 160
> 5 200
> 6 240
> 7 280
> 8 320
>
>
>
> The question now is where the value 40 comes from. (I'll also try this
> test on 11.2)
>
>
>
> However, my real problem I'm working on is that after migrating to
> 12.1.0.2, some direct loads don't work properly (CTAS in parallel and
> append INSERT).
> It seems that some sessions involved in the parallel processing disappear
> (without leaving any traces or error messages). Consequently in some load
> statements not all data is copied. And this happens randomly.
> After setting the parameter parallel_max_servers to 1 (disabling parallel
> processing) everything works as espected. However, changing this parameter
> on production is not possible.
>
> Maybe someone from this list encountered similar problems in 12.2.0.1 (red
> hat 6!)
>
>
>
>
>
> Best regards
>
> Ahmed
>
>
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: AW: parallel_max_servers and the number of sessions involved in a
> SQL
>
> Datum: 2020-11-28T17:19:54+0100
>
> Von: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
>
> An: "jlewisoracle_at_gmail.com" <jlewisoracle_at_gmail.com>, "list, oracle" <
> oracle-l_at_freelists.org>
>
>
>
>
>
>
>
> Hi,
>
>
>
> it is a PDB within a CDB:
>
>
> *Remarks*
> *PHYSICAL_MEMORY_BYTES* *16GB*
>
> *parallel_threads_per_cpu* *2 and 4* *no effect by changing this
> parameter*
> *memory_target* *0*
> *sga_target* *4456MB*
> *pga_aggregate_target* *50M, 500M and 1500M* *no effect by changing pga*
>
> *Extents* *119 and 167* *(just insert /*+append*/ into big_tbl select *
> from big_tbl – several times)*
> *Blocks* *49152 and 98304* *doubling the size of the tbl has no impact on
> the result*
> *processes* *320 and 640* *doubling the number of processes has no effect
> on the result*
> *sessions*
> *984?*
> *db_file_multiblock_read_count* *128*
>
>
>
> I played around with the above parameters and I got the result consistentl:
>
>
>
> number_of_sessions = 1+max(floor(min(parallel_max_servers,n_max)),1)
> ------------ select count(1) from v$session whee sql_id = 'the sql_id of
> the query';
>
> where
>
> cpu (host) n_max
> 1 40
> 2 80
> 3 120
> 4 160
> 5 200
> 6 228
> 7 228
> 8 228
>
>
>
> I'll try the same on 11.2.0.4...
>
>
>
> Best regards
>
> Ahmed
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: Re: parallel_max_servers and the number of sessions involved in a
> SQL
>
> Datum: 2020-11-28T15:57:19+0100
>
> Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com>
>
> An: "list, oracle" <oracle-l_at_freelists.org>
>
>
>
>
>
>
>
> You're testing with 1 to 8 CPUs and a parallel tablescan (or index fast
> full scan) with a requested DOP of 1024, and finding that the actual degree
> of parallelism appears to vary with the number of CPUs.
>
> There are many factors that might be relevant - to start with
> a) is this a PDB within a CDB, or non-container setup
> b) have you collected system stats, or used the call to calibrate_io to
> tell the optimizer something about the effectiveness of parallel query
> c) have you allowed ALL the parallel-related parameter to default, or have
> you set some of them explicitly
> d) have you set the PROCESSES parameter explicitly, or do you allow it to
> default
> e) have you set the pga_aggregate_target to a non-zero value, what about
> the sga_target or memory_target
>
> f) how large is the table you're scanning in extents, and in blocks
> g) how many data files are there in the database, and in the tablespace
> holding the table
> h) how many logical devices (that Oracle might be able to detect) is the
> database / tablespace spread over
>
> -- (f) might be relevant but I don't really think (g) and (h) are likely
> to be relevant, though it's always worth checking.
>
>
> When you run each experiment do you check ALL the parallel parameters each
> time to see what has changed from previous tests.
>
>
> Just to confirm my understanding - when you've said "sessions" above, you
> mean sessions involved in the tablescan rather than the total number of
> sessions execution the query which would be double that plus 1; hence the
> "n_max" figures you're quoting that reach a maximum of 228 is the apparent
> limit on your use of parallel server processes, not the degree of
> parallelism of the query, which would have been half that.
>
>
> Regards
> Jonathan Lewis
>
> On Fri, 27 Nov 2020 at 21:19, ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
>> Hi Laurentiu,
>>
>>
>>
>> I managed to write a function that returns the SID for the session that
>> read the block in the select statement. The function looks like this:
>>
>>
>>
>> select count(distinct sid) from
>> table(my_function_accept_ref_curssor_and_get_exactely_the_sid_getting_the_row(CURSOR(SELECT
>> /*+parallel(t 1024) */ object_id from usr_dummy.big_tbl t)))
>>
>>
>>
>> this returns:
>>
>> number_of_sessions = max(floor(min(parallel_max_servers,n_max)/*2*
>> ),1)
>>
>>
>>
>> And while executing the sql I also verified the number of sessions from
>> the v$session:
>>
>>
>>
>> SELECT COUNT(DISTINCT SID) a FROM v$session WHERE status = 'ACTIVE' AND
>> sql_id = '3w8kg71wmsy4g';
>>
>> and this returns (so two times the above value + 1)
>>
>> number_of_sessions = 1+max(floor(min(parallel_max_servers,n_max)),1)
>>
>>
>>
>> I got the formula using a script that I wrote. The script performs the
>> tests by changing the parallel_max_servers from 1 until 2 x n_max.
>>
>>
>>
>> So I'm still trying to explain how does oracle choose these limits:
>>
>>
>>
>> cpu (host) n_max
>> 1 40
>> 2 80
>> 3 120
>> 4 160
>> 5 200
>> 6 228
>> 7 228
>> 8 228
>>
>>
>>
>> The background to my investigations is that after my client upgraded
>> Oracle from 11.2.0.4 to 12..1.0.2 a regression test failed (some
>> calculations are wrong, after setting the parallel_max_servers to 1, the
>> calculation is again correct).
>>
>>
>>
>> It looks like a bug in oracle.
>>
>>
>>
>> Best regards
>>
>> Ahmed
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> -----Original-Nachricht-----
>>
>> Betreff: Re: parallel_max_servers and the number of sessions involved in
>> a SQL
>>
>> Datum: 2020-11-27T21:35:21+0100
>>
>> Von: "Laurentiu Oprea" <laurentiu.oprea06_at_gmail.com>
>>
>> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
>>
>>
>>
>>
>>
>>
>> Hello Ahmed,
>>
>> If your target is to limit the dop you have to either:
>> -> mention DOP number in parallel hint : /*+ parallel(n) */
>> -> explore PARALLEL_DEGREE_LIMIT
>>
>> Also DOP of 8 don`t translate into 8 sessions. You have involved the
>> coordinator, producers/consumers, parallel servers already allocated (you
>> might experience DOP downgrade). (you can check the sql monitor report for
>> comprehensive details )
>> You might want to avoid relying on autodop algorithm.
>>
>> În vin., 27 nov. 2020 la 21:48, ahmed.fikri_at_t-online.de <
>> ahmed.fikri_at_t-online.de> a scris:
>>
>>> Hi all,
>>>
>>>
>>>
>>> I'm trying to understand how Oracle defines the limit on the number of
>>> sessions involved in a SQL statement (a select on big table using a
>>> parallel hint)
>>> I ran some tests and I found the following:
>>>
>>>
>>>
>>> number_of_sessions =
>>> greatest(floor(min(parallel_max_servers,n_max)/2),1)
>>>
>>>
>>> where
>>> cpu (host) n_max
>>> 1 40
>>> 2 80
>>> 3 120
>>> 4 160
>>> 5 200
>>> 6 228
>>> 7 228
>>> 8 228
>>>
>>>
>>>
>>> for my test I used 12.2.0.1 installed on a virtual box (the host machine
>>> has 12 cpus)
>>> can someone explain the n_max/cpu relationship?
>>>
>>>
>>>
>>> Best regards
>>>
>>> Ahmed
>>>
>>>
>>>
>> 
>

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