Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: November 30, 2015 - 6:57 am UTC

Last updated: December 01, 2022 - 3:27 am UTC

Version: 11.2.0.3.0

Viewed 50K+ times! This question is

You Asked

Dear Tom,

I have load testing on DR database now i have 4000 processes (show parameter processes), now i want to increase it on DR site and DR Database in Snapshot standby mode only.

Doubts-
1. Can you explain what is the use of processes in oracle like a single user can logged into the database with 4000 times at a time or over all 4000 users connected to the database when 40001 user try to loggin it will error like - ORA-00020: maximum number of processes exceeded

2. Can i increase processes parameter seperatly on DR side or it should be the same on DC also ?

3. If i will increase the processes on Seperatly if possible then need to increase sessions and transactions parameters also or it may not necessary ?

Thanks

and Connor said...

It is the total number of processes (foreground + background) that are allowed. Assuming you are using dedicate connection mode, this equates (more or less) to the number of sessions on your database. This could be one user connecting 4000 times, or 4000 users each connecting once, or any combination thereof.

Dont forget that you have approx 40-60 background processes needed just to run the database.

If you have not explicitly set the sessions parameter, it will be adjusted along with processes.

Hope this helps

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle, November 30, 2015 - 8:46 am UTC

Thanks for quick reply,

Means total 4000 users can conncted to my database at a time if 4001 user try to connect then it shows an error am i right ?
Because load test team is heating n number of users now i check
SQL>select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------ ---------------
processes 1853 3096
sessions 1863 3108

Please give some knowledge about next 2 doubt.


Thanks

Processes isn't about Users.

Paul, November 30, 2015 - 5:21 pm UTC

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams220.htm

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.
default : Derived: (1.1 * PROCESSES) + 5

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

So to get 4000 sessions you would need PROCESSES=3632

Do you really have 4000 user connections (SESSIONS) to the database or is this a Web Application that is (hopefully) using Connection Pooling so that 4000 'concurrent' uses are sharing 100 connections to the DB?

Either way, look at the relationship between PROCESSES and SESSIONS and make sure your o/s can handle that kind of user load. That is a lot of overhead.

Chris Saxon
December 01, 2015 - 5:31 am UTC

Thanks for the contribution

oracle, December 05, 2015 - 5:21 am UTC

Thanks for Reply,

As i checked the processes,session and transactions in that the processes not reached till max but but in alert log there is an error like -

SQL>select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
--------------------------- ------------ ---------------
processes 1853 3096
sessions 1863 3108

opiodr aborting process unknown ospid (10523) as a result of ORA-609
opiodr aborting process unknown ospid (10527) as a result of ORA-609
Tue Dec 01 13:11:51 2015
opiodr aborting process unknown ospid (10525) as a result of ORA-609
Tue Dec 01 13:14:03 2015
Errors in file /oracle/app/product/diag/diag/rdbms/testdr/test/trace/test_mmon_4325.trc (incident=3424153):
ORA-00445: background process "m001" did not start after 120 seconds
Tue Dec 01 13:14:41 2015
Incident details in: /oracle/app/product/diag/diag/rdbms/testdr/test/incident/incdir_3424153/test_mmon_4325_i3424153.trc
Tue Dec 01 13:15:22 2015
Archived Log entry 27009 added for thread 1 sequence 26871 rlc 823633846 ID 0x87c06928 dest 3:
RFS[2]: No standby redo logfiles created
Tue Dec 01 13:15:44 2015
RFS[2]: Opened log for thread 1 sequence 26872 dbid -2063589514 branch 823633846
Tue Dec 01 13:16:22 2015
Sweep [inc][3424153]: completed
Sweep [inc2][3424153]: completed
Tue Dec 01 13:16:22 2015
Dumping diagnostic data in directory=[cdmp_20151201131622], requested by (instance=1, osid=4325 (MMON)), summary=[incident=3424153].

Thanks


Connor McDonald
December 05, 2015 - 4:14 pm UTC

are you asking a question or making a statement or ?

A reader, December 08, 2015 - 4:31 am UTC

I am not asking any question not making any statement i have a dount So i have wrote it to be clear because i got this error in alert.
So, can you please guide me what is related to this error when it comes ?

Thanks
Connor McDonald
December 08, 2015 - 6:30 am UTC

Take the incident trace file that was created, and get in touch with Oracle support.

My previous post was that this appears to be unrelated to the 'max' processes etc. Its more that a process could not start.

Document 1121357.1 might be useful as well.

Ramki, August 22, 2017 - 9:15 am UTC

hi,
In this thread "November 30, 2015 - 5:21 pm UTC"
You said as below
Do you really have 4000 user connections (SESSIONS) to the database or is this a Web Application that is (hopefully) using Connection Pooling so that 4000 'concurrent' uses are sharing 100 connections to the DB?

Can you tell me how with connection pool we can reduce number of process. say we have configured connection pools of 100 connection objects. And 1000 concurrent user want to connection to application . so each user connecting to application make getConnection() call from connection pool. so for 1000 user connection pool can't serve here. Or its possible with connection pool to serve 1000 concurrent user

Connor McDonald
August 23, 2017 - 7:06 am UTC

There's (typically) a big difference between:

"1000 concurrent users"

and

"1000 simultaneously active users"


A connection pool of size 'n' lets 'n' people all be performing a database operation *right at that moment*. From the user's perspective, they all have a connection to the database. But users are typically 99% idle, 1% busy, ie, they click a button, the database does some work, the results come back, and they are idle (reading the screen, etc etc).

Try this

select * from V$METRIC_HISTORY
where metric_name like 'Average Active Sessions';

and you'll see on your database a figure that represents over time the number of concurrently active connections over time.


just curious

Shyju CR, November 30, 2022 - 6:16 am UTC

If we change processes parameter in spfile and did a role switch, would it take new parameter once switch over completes? or it need a restart to get changes in effect?
Connor McDonald
December 01, 2022 - 3:27 am UTC

'processes' is only considered at instance startup. If during a role switch (or any kind of activity) you bounce the instance, then yes, the parameter will be seen