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

40 processes per CPU sounds like the default value of parallel_max_servers: https://docs.oracle.com/database/121/REFRN/GUID-B6CE9EA3-B846-4AA7-B668-FD3BEE433A24.htm#REFRN10158

PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5

> The number of concurrent parallel users running at default degree of
> parallelism on an instance depends on the memory initialization parameter
> settings for the instance. For example, if the MEMORY_TARGET or SGA_TARGET
> initialization parameter is set, then the number of
> concurrent_parallel_users = 4. If neither MEMORY_TARGET or SGA_TARGET is
> set, then PGA_AGGREGATE_TARGET is examined. If a value is set for
> PGA_AGGREGATE_TARGET, then concurrent_parallel_users = 2. If a value is
> not set for PGA_AGGREGATE_TARGET, then concurrent_parallel_users = 1.

PARALLEL_THREADS_PER_CPU = 2
concurrent_parallel_users = 4
It results in exactly 40 (=2 * 4 * 5).

However, I don't see that this limit is honored in my 19.9 database, so that I am able to allocate more parallel processes despite the fact that documentation says about some adjustments: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PARALLEL_MAX_SERVERS.html#GUID-B6CE9EA3-B846-4AA7-B668-FD3BEE433A24

> The lower of the two values is used as the default value of
> PARALLEL_MAX_SERVERS, and if you attempt to explicitly set
> PARALLEL_MAX_SERVERS to a value that is higher than either of the values,
> then the setting is adjusted to the lower of the two values.
>

The adjustment can be about reserved processes and entries such as these in the alert log (Second Alert_<Sid>.Log In ORACLE_HOME/dbs Directory Next To The Standard Alert Log Under ORACLE_BASE/admin (Doc ID 1322075.1)):

> Sun Feb 20 11:33:46 PST 2011
> Adjusting the default value of parameter parallel_max_servers
>

There are really two issues here:
1. the wrong results issue that Jonathan Lewis mentioned. It might be something like this: (DB36) Bug 20509482 - ORA-600 [3020], ORA-752 Wrong Results or RMAN ORA-600 [krcrfr_nohist] after Parallel Direct Load in RAC (caused by fix for bug 9962369) (Doc ID 2139374.1) 2. the DOP downgrade issue. That is a separate thing. I usually analyze the tracefile: alter session set events 'trace[px_scheduler]'; but it can be approached differently: How to View Why the Degree Of Parallelism (DOP) was Downgraded for an SQL in 12c (Doc ID 2011375.1)

On Sun, 29 Nov 2020 at 12:30, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> This is why I asked you about all your parallel parameters and what
> parameters you were leaving to default.
> In 19.3 (for example) if you don't set "processes" then the default
> number of processes is "80 * CPU_count + 40" - which almost looks like
> Oracle deciding that it has to have 40 processes for the critical
> background processes and a maximum of 80 processes per CPU is a sensible
> limit (for an OLTP system).
>
> In the same vein it's perfectly reasonable for someone in Oracle to decide
> that if 80 processes per CPU is sensible for "normal" processing then 40
> per CPU is equally sensible for the "batch-like" processes of parallel
> execution. In fact they might be thinking in terms of the impact of 20
> batch-like processes per CPU on the assumption that DOP 20 usually gets 40
> processes but only 20 of them are likely to be very busy at any one instant.
>
> Regards
> Jonathan Lewis
>
>
> On Sat, 28 Nov 2020 at 17:15, ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
>> 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)
>>
>>
>>
>>

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