Many times we observe following error which corresponds to low setting of processes parameter.
$ oerr ora 20
00020, 00000, “maximum number of processes (%s) exceeded”
// *Cause: All process state objects are in use.
// *Action: Increase the value of the PROCESSES initialization parameter.
This is mostly seen in new database installation as the processes parameter is not taken into account.
According to Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-03
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.
This nrings us to a question that “Can we set up a large value for this parameter so that user’s are not affected and we do not have to restart the system (PROCESSES is a static parameter) ?”
Answer is NO.
You need to calculate ideal value for the processes parameter by taking care of Default Background processes (Also looking at number of Archiver processes, parallel_max_servers) and total number of user connections.
This is required as otherwise you will be required to change the value of SEMMNS parameter (for UNIX only) i.e Max number of semaphores in system and along with this , it will increase the “Overhead” Memory in SGA.
Find below explanation for the above points.
1)SEMMNS parameter
Each Oracle instance needs to have a set amount of semaphores. The total amount of semaphores required is derived from the ‘processes’ parameter specified inOracle instance init.ora file. As more instances and/or databases are added the OS kernel parameter SEMMNS will need to be adjusted accordingly.
According to Metalink Note:153961.1
In Oracle7 , number of semaphores required by an instance is equal to ‘processes’ parameter in the init.ora for the instance.For Oracle8, Oracle8i, Oracle9i and Oracle10g, number of semaphores required by an instance is equal to 2 times the setting of the ‘processes’ parameter in the init.ora for the instance.
However, Oracle only momentarily grabs 2 X ‘processes’ and then releases half at instance startup.
2)Overhead Memory
Parameters like db_files, open_cursors and processes contribute to the variable part of SGA. This can be seen by issuing “Show SGA” command. You will see that “Variable Size” will be more then sum of “Shared Pool + Large Pool + Java Pool”. This is attibuted to the value of these parameters.
Please note that in case you specify a low value for SGA_MAX_SIZE, you will see Oracle bumping the value to higher value so as to accomodate high value of Overhead memory.
Staring from 10g, Overhead memory is accomodated in shared_pool_size.
e.g If you specify SHARED_POOL_SIZE as 200 MB and your internal overhead is 100 Mb, then your actual shared pool value available to instance is only 100Mb.
You can read Note:351018.1 – Minimum for SHARED_POOL_SIZE Parameter in 10.2 Version for more information.
