PARALLEL_MIN_SERVERS Parameter Behaviors

Set PARALLEL_MIN_SERVERS = 0 to reduce unnecessary parallel processes when instance up

By Default, when 12.2.0.1 instance starts up, there are more than necessary parallel processes created.

In this test case, there are 133 parallel processes created when instance starts up.

SQL>select * 
      from v$px_process_sysstat 
     where statistic like '%Server%'


STATISTIC                      VALUE      CON_ID
------------------------------ ---------- ----------
Servers In Use                    0          0
Servers Available               133          0
Servers Started                 133          0
Servers Shutdown                  0          0
Servers Highwater                10          0
Servers Cleaned Up                0          0
Server Sessions               12577          0

7 rows selected.

Look at the default parameter values related to parallel process:

SQL> show parameter parallel_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
parallel_max_servers                 integer     352
parallel_min_servers                 integer     128
parallel_servers_target              integer     512
parallel_threads_per_cpu             integer       2
...
.

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
cpu_count                            integer     32

According to Oracle Doc, the default value for :

PARALLEL_MAX_SERVERS = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
PARALLEL_MIN_SERVERS = CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2
PARALLEL_SERVERS_TARGET = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2

If MEMORY_TARGET or SGA_TARGET parameter is set, then the number of concurrent_parallel_users = 4.
If neither MEMORY_TARGET or SGA_TARGET is set, then 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.

Let’s change PARALLEL_MIN_SERVERS to a lower than default value from 128 to 32. It does not work as specified. There are still 131 parallel processes started when instance bounced.

SQL> show parameter PARALLEL_MIN_SERVERS

NAME                                 TYPE         VALUE
------------------------------------ ----------- -----------
parallel_min_servers                  integer     32

SQL> select * from v$px_process_sysstat where statistic like '%Server%';

STATISTIC                      VALUE CON_   ID
------------------------------ ---------- ----------
Servers In Use                    0           0
Servers Available               131           0
Servers Started                 131           0
Servers Shutdown                  0           0
Servers Highwater                 6           0
Servers Cleaned Up                0           0
Server Sessions                 183           0

7 rows selected.

Let’s change  PARALLEL_MIN_SERVERS to 0. it works perfectly because there are only 5 parallel processes started when instance bounced.

SQL> show parameter PARALLEL_MIN_SERVERS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
parallel_min_servers                  integer     0

SQL> select * from v$px_process_sysstat where statistic like '%Server%';

STATISTIC                       VALUE      CON_ID
------------------------------ ---------- ----------
Servers In Use                    0          0
Servers Available                 5          0
Servers Started                   5          0
Servers Shutdown                  0          0
Servers Highwater                 5          0
Servers Cleaned Up                0          0
Server Sessions                  32          0

7 rows selected.

So in order to reduce the number of parallel processes when instance starts up, make PARALLEL_MIN_SERVERS = 0, then the number of parallel processes will be down to smaller. Bouncing instance is not required in 12.2.0.1.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.