ORA-00020: maximum number of processes (40) exceeded
I was just about to test how oracle manage the processes parameter if we set the bar too low for processes value
sessions = (1.5*processes)+22
Solution for the error is in below link:
http://alphaoragroup.com/2021/05/07/sql-query-to-find-process-and-session-resource-limit-oracle/
This is a test instance where i purposefully set the process value too low as 5
SQL> alter system set processes=5 scope=spfile;
System altered.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 1157631712 bytes
Database Buffers 402653184 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
I bounce the database and check the value of processes and oracle automatically adjusted the value to high to 40
SQL> select name,value from v$parameter where name='processes';
NAME VALUE
-------------------- ---------------
processes 40
The number of processes touched the surface with value 38
[oracle@orcl19x ~]$ ps -ef|grep 'ora'|wc -l
38
Some of the non mandatory background processes were spawned and not able to run
2021-05-14 13:45:25.526000 +05:30
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process Q001 submission failed with error = 20
Process QM03 submission failed with error = 20
When multiple sessions connected and the server process keeps spawn, process 41 not able to connect
C:\Windows\system32>sqlplus test/password@orcl19x
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 14 14:31:27 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name:Number of processes at current moment
SQL> select count(*) from v$process;
COUNT(*)
----------
39
Resource limit has max utilization of 40 which caused this error
SQL> col LIMIT_VALUE format a20
SQL> set lines 200
SQL> set pages 1000
SQL> select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------------------ ------------------- --------------- --------------------
processes 39 40 40
sessions 49 55 82
From this,we can conclude that oracle automatically increase the process with flexibility according to the number background processes initially spawned
Another parameter you should keep into consideration are parallel_min_servers and parallel_max_servers
kish@PRIM>show parameter parallel_m
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 40
parallel_min_percent integer 0
parallel_min_servers integer 4
Set the value of parallel_max_servers to a lower value if you find high number of PQ slaves spawn. If parallel_min_servers is set to a value, then there are reserved or preallocated number of processes for work
kish@PRIM>!ps -ef|grep 'ora_p0'
oracle 5974 1 0 21:46 ? 00:00:00 ora_p000_dbx00_1
oracle 5979 1 0 21:46 ? 00:00:00 ora_p001_dbx00_1
oracle 5981 1 0 21:46 ? 00:00:00 ora_p002_dbx00_1
oracle 5984 1 0 21:46 ? 00:00:00 ora_p003_dbx00_1