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…
About Post Author
EL FEHYLY says:
Merciiiii