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.
Make DBA Life Easy 