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

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 <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de <mailto: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
<mailto:laurentiu.oprea06_at_gmail.com> >
  An: "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> >            

  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