General
cancel
Showing results for 
Search instead for 
Did you mean: 

How to calculate the PROCESS for the database

Sreenivasulu Raju.M
Occasional Contributor

How to calculate the PROCESS for the database

How to calculate the PROCESSES for the database in the cluster server?

Now we got the below error:

WRITER_1_*_1> WRT_8001 Error connecting to database...
Database driver error...
Function Name : Logon
ORA-00020: maximum number of processes (%s) exceeded
5 REPLIES
Steven E. Protter
Exalted Contributor

Re: How to calculate the PROCESS for the database

Shalom,

Within the database, sqlplus statements provide it. You'll get a post on that.

Take a look that your system isn't overrun with processes on the OS level as well. nprocs, nfiles maxuprc

That last one is set to 75 and limits the number of processes one user can have and frequently causes oracle database trouble.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Steven E. Protter
Exalted Contributor

Re: How to calculate the PROCESS for the database

Shalom again,

Try this:
http://awads.net/wp/tag/sql

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
spex
Honored Contributor

Re: How to calculate the PROCESS for the database

Hi,

Since you're running into the limit, you must be at whatever Oracle's processes parameter is set to. If using a pfile (located in ${ORACLE_HOME}/dbs), you should see a line that looks like this:

*.processes=600

If you have access to another Oracle session, the following bit of SQL will tell you the actual value:

SELECT COUNT(*)
FROM v$process;

If you force other users to log off, you will reduce the number of outstanding processes, and will in turn be able to create a new session.

PCS

Yogeeraj_1
Honored Contributor

Re: How to calculate the PROCESS for the database

hi,

This Parameter value varies from environments to environments. You may wish to analyse your system and see if it is an once-off situation due to some problems or it is occurring very often.

Normally the cause is: An operation requested a resource that was unavailable.
The maximum number of processes is specified by the initialization parameter PROCESSES.

When this maximum is reached, no more requests are processed.

Action: Try the operation again in a few minutes.

If this message occurs often, you should shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.


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.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.

Hence, using your observations, you determine the right value for your database.
Statspack reports can also help you in this task.

hope this helps!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: How to calculate the PROCESS for the database

set pages 9999
column NAME format A25
column INIT format A10
column LIMIT format A10
SELECT RESOURCE_NAME "Name", CURRENT_UTILIZATION "Current",
MAX_UTILIZATION "Max", INITIAL_ALLOCATION "Init", LIMIT_VALUE "Limit"
FROM V$RESOURCE_LIMIT
where max_utilization > 0;


Or for rac something like:

select inst_id, resource_name, current_utilization, max_utilization,
initial_allocation
from gv$resource_limit
where max_utilization > 0
order by inst_id, resource_name;

See also...

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2050.htm

metalink - Note: 135714.1 Script to Collect RAC Diagnostic Information (racdiag.sql)

http://www.adp-gmbh.ch/blog/2005/april/17.html