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@xxxxxxxxx>
An: "ahmed.fikri@xxxxxxxxxxx" <ahmed.fikri@xxxxxxxxxxx>
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@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> <ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> > 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
