Sizing the process parameter
Mohammed, February 17, 2008 - 1:58 pm UTC
Dear Mr.kyte
Greetings
Can you give me some guide lines of how to size the process paraemter.
February 17, 2008 - 2:09 pm UTC
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.
User Process session
Mohammed abuhamdieh, February 17, 2008 - 4:32 pm UTC
What you ment by number of user sessions, i may have thousands of user sessions? Please give an example in numbers like if i have 1000 session and 10 job processes/aq processes, approximatly how would be the process parameter is?
February 17, 2008 - 4:54 pm UTC
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
pratik, December 03, 2008 - 9:05 am UTC
Hi,
I have one application in which there are nested query. Thare is only one session in oracle database running on windows, for the application. But after executing few query it give me error of "ORA-12519, TNS:no appropriate service handler found".
Why it exceeds number or processes in this case? how and why processes/thread are created internally by oracle in this situations?
From where I can see list of all processes/thread that are created?
December 09, 2008 - 10:14 am UTC
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.
Determining the number of background processes
Amit, December 16, 2008 - 8:17 am UTC
To determine the number of background processes currently running in the instance ,we can use
Select count(*) from v$process where BACKGROUND=1;
Then depending on the value specified in the processes parameter minus the count, you would have your maximum allowable processes (or in case of dedicated configuration sessions too.)
maximun number of processes
A reader, October 17, 2010 - 8:25 pm UTC
Hi Sir,
My current processes setting in initialization file is 1200.
Regarding the maximum number of process parameter I see sometimes in my alert log ora-00020 error. when i query the database i see only few may be 10 to 15 session that are active. What my understanding is that the application uses jboss to connect to the database and query the database. I assume that the connection pool spawns the database with large number of connections around 1000 and uses only few of them to perform work on the database and in addition to monitoring jobs and users connecting to the database we might be hitting the maximum process. Do we need to recheck our connection pool mechanism or is there any harm in increasing the process parameter. How to determine the value for the process parameter. Please advice.
Thanks for your valuable time and suggestions
October 25, 2010 - 8:37 am UTC
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".
Too many processes
Kev Burgess, January 13, 2011 - 5:14 am UTC
So what happens if I set processes to, for example 2000 but only 500 are needed? Or sessions to 1000 when only 200 are needed?
Many thanks in advance!
January 13, 2011 - 10:40 am UTC
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.
Processes and other derivied settings
Ken, January 13, 2011 - 11:16 am UTC
Tom,
I recently switched 1 of our heaviest used databases from dedicated to shared server. All is going well but it never occurred to me to change the processes parameter.
I'm considering increasing sessions given some recent increase in use and stumbled across this thread. I could easily reduce the processes parameter from the 400 it's set to today (4 cpu machine). It sounds like I had it set too high to begin with (never got any ORA-20s though :) I had manually set sessions and transactions based on the documentation (ie: sessions = 1.5 * processes + 22), etc.
My question is given I was already manually setting the other derived parameters am I still wasting resources somewhere by having processes set too high? If so, what resources?
I had something like 250+ concurrent sessions on this database which I'm now handling with 20 shared servers and 2 dispatchers. I've got fewer than 30 total processes for this database on the server.
Hope my question makes sense and thanks in advance for any insight you have to offer.
Thanks
January 13, 2011 - 1:39 pm UTC
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 ;)
Processes and other derivied settings
Ken, January 13, 2011 - 11:19 am UTC
Sorry, I misstated the number of processes on the machine right now (after rereading the math didn't make sense). I have just under 50 processes.
Thanks
How to find out if existing memory and CPU can handle more processes?
Giridhar, January 14, 2011 - 11:45 am UTC
Tom,
Assume we have a current value of 100 processes.
We know details about server memory and CPUs.
Is there any formula or process to find out if
our server can handle more than 100 processes before we increase this value?
Thanks,
Giridhar
January 14, 2011 - 12:29 pm UTC
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.
Processes and Shared_Pool
Kev Burgess, January 25, 2011 - 2:53 am UTC
Hi Tom,
Is there a direct relationship between processes and shared_pool? I ask because our shared_pool is full of queries with contatenated variables, not bind variables. Obviously, this means our shared_pool is constantly full and having a serious impact on performance.
I have been monitoring random servers for some time and have noticed a server spawing 326 processes for only 69 users. All our Oracle servers are 10g running the same database and have (what I believe) to be the same shared_pool issue.
The process count was derived from:
SELECT COUNT(*) FROM V$PROCESS;
I believe our processes are set far too high based on what you have said but is it possible the queries residing in the shared_pool spawn their own processes? It would certainly explain alot!
Many thanks in anticipation.
February 01, 2011 - 10:33 am UTC
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.
Spawning new processes
Kev Burgess, January 25, 2011 - 3:04 am UTC
Hi Tom,
Sorry, I forgot to ask this question. If a query has already been hard-parsed into the shared_pool is it still necessary to spawn a new process to soft-parse that same query (using bind variables)?
Does this behaviour change if contatenated variables are used?
Once again, many thanks!
February 01, 2011 - 10:34 am UTC
no, we do not spawn processes to parse.
oracle processes
A reader, May 31, 2011 - 3:48 am UTC
hi tom,
i been switching from dedicated process to shared server.
But i am concern how many shared should i create and how many shared can my system support.
i been trying to google around but there isnt much direct answer to it.
can you please enlighten me on the following
1) how many shared process should I create base on amount of concurrent active sessions ?
is there any rule of thumb for that ?
how do i monitor number of active shared server at a current time ? / over a period of time ?
2) how many shared process can my machine support ?
is there a cpu/mem thing for me to calculate ?
Regards,
Noob
May 31, 2011 - 12:53 pm UTC
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.
Log File Sync Waits when increase Process Parameter
Taral, June 01, 2011 - 10:23 am UTC
June 01, 2011 - 2:46 pm UTC
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?
oracle processes
A reader, June 01, 2011 - 2:12 pm UTC
hi tom,
As what you mentioned earlier
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.
select count(*) From v$session where status = 'ACTIVE'
I have done a check, and at any point in time, the highest concurrent activity as about 8.
I have a 8 CPU system so I think setting the about of shared server to 32 should be of no problem..
--------------------
But in any case, I would like to monitor the usage of the shared server.
Q1) is there anyway that i can monitor if any session/sql/transaction is holding on to any SHARED server process for and extended period of time ?
Q2) can i check if all the SHARED process are ultilized and the current queue (list of jobs to be allocated) in the dispatcher ?
Regards,
Noob
oracle proccesses
A reader, June 02, 2011 - 12:20 am UTC
Thanks a million Tom.
Appreciate that.