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

Leave a Reply