ORA-00020: maximum number of processes (string) exceeded

This is a very well known problem and it appears in alert log of the database as:

ORA-00020: maximum NUMBER OF processes (200) 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.

The resolution is even simpler by increasing static initialization parameter PROCESSES. The annoying part of this error is the warning that often directly comes from your users. Even if you monitor (and you should !!) all the ORA- error messages in alert log file you users might be faster than you to see it (obviously they are directly impacted). And even if you see it before them as it is a static parameters there is nothing you can transparently do in the background.

You are also aware that this maximum number of processes does not include only the sessions processes but also the background processes (Database writers, log writer, …) as well as parallel processes. So be cautious on the value of PARALLEL_MAX_SERVERS, even the default value can be huge on a small system.

So how could you be more proactive on this subject ?

The table to monitor is V$PROCESS, by selecting all columns in SQL Developer, for example, you will also see it contains the list of parallel processes (oracle@server1 (P000)).

So to be informed you are reaching the limit a query like below helps you:

SQL> SELECT COUNT(*) FROM v$process;
 
  COUNT(*)
----------
        35

A bit clever you can use V$RESOURCE_LIMIT:

SQL> SET lines 200 pages 1000
SQL> SELECT *
     FROM v$resource_limit
     WHERE resource_name='processes';
 
RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------
processes                                       35              41        150                                      150

The column value to monitor is MAX_UTILIZATION to be sure you are far enough INITIAL_ALLOCATION value. The MAX_UTILIZATION column automatically monitor for you the maximum number of processes your instance reached since it started… If for any reasons your database is shutdown frequently (offline backup or whatever) you may alternatively use DBA_HIST_RESOURCE_LIMIT with a more sophisticated query like:

SQL> SET lines 200 pages 1000
SQL> SELECT b.begin_interval_time,a.current_utilization,a.max_utilization,a.initial_allocation
     FROM dba_hist_resource_limit a, sys.dba_hist_snapshot b
     WHERE a.resource_name='processes'
     AND a.snap_id=b.snap_id
     ORDER BY a.snap_id DESC
 
BEGIN_INTERVAL_TIME                                                         CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
--------------------------------------------------------------------------- ------------------- --------------- ----------
10-JUN-15 02.00.43.708 PM                                                                    34              41        150
10-JUN-15 01.00.18.144 PM                                                                    36              41        150
10-JUN-15 12.00.52.542 PM                                                                    34              41        150
10-JUN-15 11.00.14.983 AM                                                                    34              41        150
10-JUN-15 10.00.04.371 AM                                                                    36              41        150
10-JUN-15 09.00.29.917 AM                                                                    34              41        150
10-JUN-15 08.00.10.482 AM                                                                    36              41        150
10-JUN-15 07.00.55.711 AM                                                                    36              41        150
10-JUN-15 06.00.36.236 AM                                                                    36              41        150
10-JUN-15 05.00.25.689 AM                                                                    34              41        150
.
.
.

If the ORA-00020 error already occurred the next fair question you will receive as a DBA is: which account(s)/program(s) have created all those sessions. Before increasing the value of PROCESSES initialization parameter it is also worth understanding if it is a real capacity increase need or a scheduling bug…

As there is no reason you have an AWR report at the time of the issue you will not be able to use queries like:

SELECT parsing_schema_name,COUNT(*) FROM dba_hist_sqlstat WHERE snap_id=51158 GROUP BY parsing_schema_name;

So the next place is listener log, even if trace level is set to off you will have enough information to communicate to your users to help them to investigate. On my production server I have found many sessions coming from our BOXI server, this helped to understand and reschedule differently:

09-JUN-2015 19:24:22 * (CONNECT_DATA=(SID=dwh)(CID=(PROGRAM=D:\BOXI_APP\BusinessObjects?Enterprise?12.0\win32_x86\crystalras.exe)(HOST=server1)(USER=boxiadm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=62748)) * establish * dwh * 0

But overall the best is to schedule the small below query at a quite high frequency (not too much to not kill the server) and monitor which account is generating all the connections (and/or parallelism):

SQL> SET pages 1000 lines 200
SQL> break ON name
SQL> SELECT NVL(pname,'USERS') AS name,DECODE(NVL(pname,'USERS'),'USERS','ALL USERS','BACKGROUND') AS TYPE,COUNT(*) AS COUNT FROM v$process WHERE program!='PSEUDO' GROUP BY pname
     UNION
     SELECT 'USERS',username,COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username
     ORDER BY name,TYPE,COUNT;

But funnily above query has to be submitted cleverly… Let me explain, I initially did a small cron job that was connecting every 5 minutes to the database and executed the query. Funny issue is that when the maximum processes has been reached my small monitoring program failed for.. ORA-00020… obviously, so had no trace at the time of the error…

So you have to be connected and to execute the above query is a pre-existing connection at the time of the processes number limitation. But as you want to constantly see the result you cannot simply use DBMS_OUTPUT.PUT_LINE. So I did it with something I already experienced i.e. pipelined function:

CREATE OR REPLACE PACKAGE pipe_processes
AS
TYPE output IS TABLE OF VARCHAR2(50);
FUNCTION function1(INTERVAL int DEFAULT 10) RETURN output pipelined;
END;
/
show error
 
CREATE OR REPLACE PACKAGE BODY pipe_processes
AS
FUNCTION function1(INTERVAL int DEFAULT 10) RETURN output pipelined
IS
date1 VARCHAR2(19);
CURSOR cursor1 IS SELECT NVL(pname,'USERS') AS name,DECODE(NVL(pname,'USERS'),'USERS','ALL USERS','BACKGROUND') AS TYPE,COUNT(*) AS COUNT
     FROM v$process WHERE program!='PSEUDO' GROUP BY pname
     UNION
     SELECT 'USERS',username,COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username
     ORDER BY name,TYPE,COUNT;
item1 cursor1%ROWTYPE;
BEGIN
  LOOP
    date1:=TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
    pipe ROW(date1);
    pipe ROW('NAME  TYPE                                COUNT');
    pipe ROW('----- ------------------------------ ----------');
	OPEN cursor1;
	LOOP
	  FETCH cursor1 INTO item1;
	  EXIT WHEN cursor1%notfound;
      pipe ROW(RPAD(item1.name,6,' ')||RPAD(item1.TYPE,31,' ')||LPAD(item1.COUNT,10,' '));
	END LOOP;
	CLOSE cursor1;
    dbms_lock.sleep(INTERVAL);
  END LOOP;
  RETURN;
END;
END pipe_processes;
/
show err

Then in a pre-existing session you have to execute (for a report every minute):

SQL> SET lines 200 pages 0 arraysize 1
SQL> SELECT * FROM TABLE(pipe_processes.function1(60));

One can argue he would do the same with Oracle Scheduler: yes of course ! It is maybe even simpler but I am desperately looking for any opportunities to use those sexy pipelined functions…

IT World

ORA-00020: maximum number of processes (string) exceeded

About Post Author

Yannick Jaquier

Related Posts:

One thought on “ORA-00020: maximum number of processes (string) exceeded”

EL FEHYLY says:

Leave a Reply Cancel Reply