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

There is the more practical aspect that I would in general strongly discourage from using default parallism. The calculated DOP is usually far higher than sensible . Besides I prefer a stable behaviour that does not depend on parameters that might be changed without the consequences fully understood.

Regards

Lothar
Am 29.11.2020 um 13:54 schrieb Mikhail Velikikh:
> 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
> <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
> <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
> <mailto: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
> <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
> <mailto: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