Hi,
The link below said number 32000 is the max limit number of processes
that can be set in Oracle 10g :
http://forums.oracle.com/f
The number of Oracle processes I'm referring to are illustrated below :
SQL>show parameter processes;
NAME TYPE VALUE
--------------------------
. . . . . . .
processes integer 900
Q1:
What's the max value of Oracle processes/sessions that we can set for
Oracle 9i ( Version 9.2.0.8.0 ) ? (ie what's the max value for ??? in the commands :
SQL> alter system set processes=??? scope=SPFILE;
SQL> alter system set sessions=??? scope=SPFILE;
Q2:
On our HP-UX B11.11 with 4Gb RAM, our current sem* kernel
parameter settings are shown in bracketts below and it can
support up to about 1000 Oracle sessions and if we increase the
number of Oracle sessions much higher, we have to repair the
spfile.
My current values are indicated in bracketts below, so if I were
to increase them say by four fold, does that mean I can increase
the max limit # of Oracle sessions/processes in the same proportion,
ie
SQL> alter system set processes=4000 scope=SPFILE
New value I
plan to set to: (current value):
----------------- -------------------
semmni 8400 (currently 2000)
semmns 16800 (currently 2000)
semmnu 4196 (currently 1000)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The maximum value of Oracle processes depends also on the available memory.
Every process consumes RAM (Oracle warns about 5 MB, some experts say it is 2 MB).
So these processes will eat the RAm and also use ports (Listener redirects the ports to
establish socket connection).
The best practice is to try to remove the dead connections adding to SQLNET.ORA file the parameter:
SQLNET.EXPIRE_TIME= 30
every 30 minutes the dead connections will be removed.
I do not recommend increasing your Oracle PROCESSES by fourfold. That would be 3600, which is extremely high for a single instance, and I have never seen that high of a setting.
You may need to look at other ways of scaling:
1) Faster hardware to reduce the invididual process lifetime
2) RAC - To spread the processes out over multiple nodes
Thanks chaps.
Hi Tomcatkey, is the value below correct or are you trying to mean 6400?
(64 is too low as my servers' current value is 2000)
semmni = 64
Hi Schwertner:
I did try out the "SQLNET.EXPIRE_TIME= 20" when you suggested to me the last time & it
did not help. We've written scripts to monitor number of Oracle sessions and when it
surges above 120 (about once-thrice every fortnight, # of Oracle processes wud go
above 120), we asked users to finish their last transaction & stop and we've seen that
within 5 seconds, # of Oracle processes could dive from 800+ to just 35 within 5 secs.
So don't think there's any dead/idle processes involved here, they must be active
processes based on what we've noticed
Hi mrjoltcola:
I understand setting the number of Oracle processes too high may prevent Oracle from
starting up subsequently if the sem* parameters are not increased sufficiently. Today,
on the HP-UX UAT server, we've just increased a dozen over kernel parameters (including
the sem*) & this time, we're able to increase number of Oracle processes to 3200 (previously
1600 would have failed & my DBA colleague has to do some sort of repair to spfile or
inittab.ora)
Q4 :
Btw, does anyone has the steps to recover (ie repairing the spfile/inittab.ora) in case I
increased # of Oracle processes too high and upon subsequent restart of Oracle, it
won't allow us to restart due to insufficient sem* values??
Hi, just because you were able to boot does not mean Oracle will ever successfully reach 3200 simultaneous processes before the system stops dead. What I meant was, by the time you have that many sessions, you'll likely have a non-responsive system. But good luck, let us know how it goes.
Q4:
1) Make a init.ora file copy right now that you can use as a safe copy.
SQL> create pfile='/backup/initORA1.or
2) Use RMAN and autobackups and RMAN will backup the spfile for you
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Oracle uses another style of management (shared server) when it goes to so much processes:
http://download.oracle.com
But shared server is not a panacea - you have to investigate what are your users doing when they
use the resource:(http://searchora
As you may know, in an Oracle instance using shared server, Oracle sessions do not have a dedicated server process to execute their SQL statements. Instead, each statement is passed by a dispatcher to one of the shared server processes associated with the instance. Such an architecture is useful in systems that have to support many hundreds or thousands of concurrent sessions, as it reduces the number of server processes on the Oracle host. There is slightly more CPU overhead associated with the use of shared servers, but it should be more than offset by the memory and CPU savings achieved by reducing the operating system's process management overhead.
Shared server is most effective when sessions spend a lot of their time in an idle state (as may be the case for OLTP applications). Systems with sessions that have a higher ratio of active-to-idle time (such as DSS) may not benefit as much from Shared Server. Remember, though, that it is a simple matter to specify a dedicated server process for a session even in the presence of Shared Server: Simply create a database service name that contains the term SERVER=DEDICATED, and use that service name at login time. Check the Oracle Net manual for details.
Also be aware a mixed strategy is also possible.
Thanks again chaps.
I'm not a DBA, just a Unix sysadmin.
To clarify further, instead of using Rman, can I take a backup of the spfile & init.ora
using Unix "cp" command to make a copy to somewhere?
We have observed on our problem Filenet HP-UX server, the # Oracle processes could
climb to 200-300 or 800-850 and then suddenly came down to less than 50 : so on such
incidents, no disruption to services but if it exceeds our current limit of 900, then entire
Oracle would be dead and we have to restart Oracle. So, I'm hoping that by increasing
the limit to 3200, it would never hit this new limit of 3200 and would come down. It hit
900 about once every 2-3 weeks. Comparatively on our Itanium (also 4Gb RAM) SAP
server, the # Oracle processes never go above 120.
I'll keep this thread open till after 17 Aug when we've implemented on our Prod Filenet
server (currently only done on Uat Filenet server).
MrJoltcola/anyone,
>by the time you have that many sessions, you'll likely have a non-responsive system
If 3200 is considered high, why is it Oracle 10g allows a limit of up to 32000 as given by :
http://forums.oracle.com/f
You can backup every Oracle file, but never try to edit the SPFILE. It will be corrupted.
Editable are mirrored copies - the parameter files.
850 sessions is too much. There should be a reason for this and you can try to investigate
who is producing these connections. If the services exceeded the number of the processes allowed
Oracle will not be "dead", only new connection will be rejected.
Possibly the explanation is that you run out of RAM size and swappings began ....
4GB RAM is too little for such kind of installation.
So I think you need further investiogation who, why and for what reason does these connections.
sunhux,
You do not backup the spfile or controlfile with UNIX commands unless the database is down. RMAN or sqlplus is the approved procedure for hot backups of these files.
>>If 3200 is considered high, why is it Oracle 10g allows a limit of up to 32000 as given by
Its just my personal opinin. Years ago, 500 was high.
The same logic can be applied to why a 64-bit UNIX kernel allows use of a terabyte of memory, just because an integer value can hold an amount, does not mean the system in question will perform well with that amount.
The same goes for Oracle. You can use very large SGAs, but the larger you go, the more the performance may degrade due to buffer cache LRU list management, etc.
Regarding sessions, technically, if you tried, you could use 1 session to take all of the available CPU / disk IO resources of Oracle. Or you may have 1000 sessions idle doing nothing, but each of those idle sessions requires memory and semaphores and also equals context switching.
Oracle allows you to configure it to use all of the available power of your OS / hardware. My comment is just that 3200 is high for the average setup, but it does depend on what each session is doing, how much memory you have, and how many CPUs you have.
After a lengthy discussion & experiment, I found that increasing the
following values would allow us to increase our Oracle processes
limit to 3200.
Of course, there are other parameters that were tuned too, namely
maxuprc, maxswapchunks, etc (as recommended by Oracle page)
but found that I should not increase maxdsize and maxdsiz_64bit
as recommended, otherwise Filenet's workflow client on Windows
server won't connect to the Unix DB server. No error/clue.
Anyway, after increasing the following values below (& other kernel
parameters), the number of oracle processes never go above 250
(it's bound to exceed 500 once per week previously).
So probably some of those settings has hold back the number of Oracle
processes from increasing but the following values below certainly allow
me to increase Oracle processes further :
New value I
plan to set to: (current value):
----------------- -------------------
semmni 8400 (currently 2000)
semmns 16800 (currently 2000)
semmnu 4196 (currently 1000)
I will note that I have oracle that is providing service for web pages where I'm specifying PROCESSES=5000, though most normal operational conditions have more like 500 session pool, I used to have it set to 1000 and had semi-fatal app errors occuring when it ran out of available sessions, so I have the similar inclination to "put it in the sky" and set a processes value that is perhaps a factor of 10 more than you normally intend to use, just to handle the demand spikes, or the occasional bad app code that opens sessions and fails to close them in timely manner. Let that Java app code be the point of failure, don't let it be Oracle :-)
Business Accounts
Answer for Membership
by: tomcatkevPosted on 2009-08-04 at 02:28:11ID: 25011778
Generally you've identified the correct relationships and SHM tunables that need to be increased to allow Processes to be increased.
I'm using Solaris rather than HPUX, but I might tend to suggest putty really high estimates in for the Semaphore Kernel parms, particularly if you want to try and set something near Oracle maximum, then set the corresponding SHM kernel parameters closer to the maximum. There is a VERY SMALL AMOUNT of kernel memory to be saved by specifying anything less than the maximums, and hey, I'll presume that if you want to have 32767 processes, you probably have a decent 32G+ memory configuration and probably aren't concerned if the kernel grows by a few MB to give you maximum flexibility for scalability. It is what we do :-)
semmns = 32767 (maximum specifiable hpux value, maximum total of all Oracle Processes for all instances on the box)
semmni = 64 (maximum number of Oracle instances on the box)
And with apology, I don't recognize semmnu as important to mess with.
A few others worthy to note that you don't mention...
semmsl = 32767 (maximum number of processes per instance / per semaphore identifier)
shmmax = 0xffffffffffff (or some value that is bigger than the maximum memory your server can be expanded to)