make it at least one larger than the number of processes you need.

(eg: it depends. shared server, it would be less than if you use dedicated server probably)

think about how many processes you'll have - the Oracle backgrounds (give them 15) plus your user sessions (number of shared servers/dedicated servers), number of job processes/aq processes you want to permit, parallel execution servers if applicable.

well, if you were using dedicated servers, and you read my last response:

think about how many processes you'll have - 

the Oracle backgrounds (give them 15)           15

plus your user sessions 
(number of shared servers/dedicated servers)    1000

number of job processes/aq processes 
you want to permit                              10

parallel execution servers if applicable.       0
                                                ========
                                                1025

v$ tables contain a wealth of information. v$process, v$session would be of interest to you.


No idea what a "nested query" in this context is.

the number of active sessions has no bearing or relationship to the number of processes in the system.



If you are hitting ora-20 you are spawning too many processes, the only logical things to do are:

a) determine what is causing the processes - and make that thing need LESS of them.

b) increase processes of you cannot make that thing need LESS of them

You do not want thousands of unnecessary processes on your system. If you only have 10-20 active sessions, reduce your connection pool to something teeny tiny, like "min=1, max=100".

you will consume more SGA resources as that parameter is used to size many data structures.

Look at your machine, if you have say an 8 cpu machine and you have processes set to more than 200 or so - you have set it too high. Your 8 cpu machine cannot possibly do 2000 processes. You need to get your connection pool(s) under control or start employing shared server.

setting processes too high will use a little extra SGA memory for some structures that are allocated based on processes and may make some lookups take a little longer (lookups in SGA structures) but would not generally have a noticeable impact. It would be noticeable if something caused many processes to spawn however ;)

it is a function of how many of those processes are active.

all 100 - at the same time - that machine is already overwhelmed (a typical machine of today with 1-32 cpus available for processing).

5 of them - that machine can do a lot more probably - however - do you want to INCREASE PROCESSES or do you want to make your existing processes do more?

I would say "the latter, not the former".

You do not need hundreds of processes to accomplish a lot of work, if you have hundreds of processes you are able to process LESS OVER ALL work because we all know that you really cannot have more than 2 to 4 times as many active processes as CPU. So, until you have 1,000 cpus, you would not have thousands of processes.

sounds like you are using a connection pool and either

a) you have lots of app servers each with their own pools and therefore you have N*M processes where N= number of app servers and M= size of connection pool used by each.

Solution to (a) - reduce the number of app servers and/or severely restrict the size of their pools - one of them is too large for your workload.

b) you have an app server with a connection pool and your developers accidentally "forget" to return a connection to the pool when they are done (probably because of bad error handling). They are probably leaking cursors like a sieve as well - and THAT would definitely contribute to your shared pool problem.

Solution to (b) - since you already know the programmers are not smart enough to use bind variables - you can guess they are leaking cursors and connections left and right. Time to get new developers - right away. That would be my first approach :)

Second approach - get them to fix the code which is leaking connections and therefore almost certainly cursors.




the number of processes has nothing to do with the queries in the shared pool - it is one of the two above things.

no, we do not spawn processes to parse.

1) as many concurrent sessions as your machine can truly handle. It is all about math. If you have a two cpu system - how many concurrently ACTIVE sessions can you handle? About 2-8 depending on what they do. If they just burn cpu, 2. If they do lots of things that incur waits like physical IO - maybe 4, 5, 6, 7, 8. Could your two cpu machine do 20 things at a time? No, it cannot.

So, look to the number of real cpu's (cores) you have. Then - multiply that by 2, maybe 4 (depending on what they do, just imagine in your mind what would have to happen if you set it to 4 times, or 2 times and so on)

2) cpus * 1, 2 or maybe as much as 4.

The machine CAN support hundreds, thousands of them.

The machine SHOULD support some very small number of them.

why would you ask me to answer someone elses claim? why wouldn't you post on that group, in that thread, and ask them to put up or shut up?

q1) there are lots of v$ tables and enterprise manager/statspack would also report on this, yes.

q2) enterprise manager would be the ticket here, otherwise you are querying a series of v$tables...

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/manproc003.htm#i1009696