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

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
 

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

  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