Increase the number of dedicated server processes

DB=ORACLE 8.1.7
OS=WIN NT

A couple of weeks ago, I had a problem whereby connection to the database was failing. I got the following error message in listener.log on the server:

TNS-12540 TNS:internal limit restriction exceeded
Cause: Too many TNS connections open simultaneously.
Action: Wait for connections to close and retry.

After some investigation, I thought it had to be one of two things...

The obvious one: I had unwitingly set a limit on the number of TNS connections allowed

or: There is a maximum number of dedicated server processes that can be spawned

Does anyone know how to increase either of these? I take it there is no overhead in doing so, only that more server processes\TNS connections will be generated.

P.S. We did toy with the idea that it could have been that we had exceeded LICENCE_MAX_SESSION (????), but increasing that didn't make any difference.

This is the error stack from listener.log if that helps:

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error

I am running low on free SHARED_POOL too. I suppose that would have something to do with it!! I currently have 2GB RAM and 1.4GB is allocated to the buffer cache and 100MB allocated to shared pool. It has been fine with these settings until recently, so I am reluctant to change them with good reason.

Any other ideas?

Reduce the SGA!

Look at what I found for you:

"The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 7.3 to 9.2
Microsoft Windows NT Terminal Server
Microsoft Windows (32-bit)
Oracle database instance, listener, clients attempting to connect via the listener.
Errors
ORA 12500 "TNS: listener failed to start a dedicated server process"
ORA-12540 "TNS: internal limit restriction exceeded"
ORA-12560 "TNS: protocol adapter error"
TNS 510 "Internal limit restriction exceeded"
32-BIT WINDOWS ERROR: 8: EXEC FORMAT ERROR
Goal
QUESTIONS TO ASK WHEN the following errors are thrown to the client when it tries to connect from a client to the db instance

running on Windows OS, via the listener -

TNS-12500: TNS: listener failed to start a dedicated server process
TNS-12540: TNS: internal limit restriction exceeded
TNS-12560: TNS: protocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error

This is an issue specific to 32 bit windows operating systems.
Fix
Basically, the above error typically indicates the inability of the listener to spawn a shadow server process for the client that requests a dedicated connection. This is purely a resource issue, and indicates the shortage of memory for the oracle.exe process to grow larger in size, and has reached its maximum possible size in physical memory (RAM).

Though this is a resource issue, there are quite a few things that can be done to avoid the above errors from occuring, and ensuring that clients have memory available for shadow processes.

Before proceeding, we have to summarize a few important points regarding how memory is allocated to processes in a Windows system.

Basically, Windows has two kinds of kernels - one that runs in the default mode of bootup, and the other is a special one that runs in the 3G-switch mode. The former is the default mode of operation of Windows. In a 32 bit OS, the maximum memory that any process can address will be 2 to the power 32, ie, 4 GB. The memory of each process has two components - user space and kernel space. In windows, in the default mode, the kernel space of every process can go upto a maximum of 2 GB, and its user space can take up the other 2 GB of addressable memory. When the 3G switch is enabled, a special type of windows kernel is loaded during boot time, which lets the kernel space per process address only upto 1GB of memory, and lets the user space extend upto a maximum of 3GB. This 3GB includes all addressable memory, including virtual memory.

In order to avoid the 32-bit Windows Error: 8: Exec format error, The following points need to be considered -


++ At the time the error occurs, what is the value of the memory occupied by the Oracle.exe process (in case only one instance is running) or processes (in case multiple instances are running) as seen in the task manager?

++ Dedicated sessions are always allocated memory OUTSIDE the SGA. The values given to the data buffers of the SGA are fixed, and preallocated to the SGA. Thus, out of the addressable user space for the oracle.exe process, this allocated memory is already unavailable to user sessions. Thus, the SGA size should not be set very high without any special need to do so.

Lowering the SGA size will free up more addressable user space for the dedicated shadow server processes that are spawned by the listener. Typically, user sessions take around 1.5 MB of memory. This comes outside the SGA, but within the oracle.exe process. If there are multiple instances, each instance will have its own oracle.exe running on the system.

++ What is physical RAM on the system?

If the RAM is less than 2GB, the above errors could appear quite early on, ie, with not a very high number of users. Reducing the SGA would be the only option left usually, in such cases, if dedicated sessions cannot be done away with.

++ What is the value of the swap space set on your system? In some cases, increasing the swap space solves the problem. This usually happens due to low value of physical RAM, and a low swap space setting. The oracle.exe wouldn't have yet reached its maximum limit of 4GB of total addressable space, but just no memory would be available.

++ To find out the exact number of dedicated versus shared sessions at the time the error occurs, we can execute the following query -

select server,count(server) from v$session group by server;

In the above output, SHARED and NONE indicate shared sessions and idle shared sessions respectively, and DEDICATED indicates dedicated sessions established with the db instance.

++ What is the PROCESSES parameter set to in the init.ora file? You can find out its value by issuing at the sql prompt, the following -

show parameter processes

If the number of sessions are not really high, and still the exec format error is being obtained, this parameter could have been set to a very low value. Setting this to a higher value could help solve the issue. This parameter doesn't take in any resources, and is just a number indicating the maximum number of processes that can be associated with the db instance. Hence, setting this to a large value will not cause any adverse effects.


++ Is the db instance configured for MTS (Multi threaded server)?

When a client establishes a shared session, it doesn't get a shadow server of its own, but is allocated one from a pool of server processes. Thus, this reduces the need for memory quite heavily, and enables larger number of simultaneous client sessions to the db instance.

++ Is the db instance server machine configured with the 3G switch, that enables any process on Windows to address upto 3GB of physical memory instead of the default of 2GB?

Only some versions of Windows support the 3G switch. Please contact your system administrator for more details on this. When the 3G switch is enabled, your oracle.exe's user space can go upto 3GB.


++ How many instances are running on the server machine alongwith the db to which you are unable to connect?

You may consider running different instances on different machines if load on all of them is very high, and the above errors keep surfacing.

Typically, the above errors start surfacing after around 3 to 4 weeks of continuous operation of the database. At this time, the oracle.exe (this is the user space size) size reaches around 1.7 GB as seen in the task manager. The rest of the 0.2 to 0.3 GB is lost due to internal memory fragmentation due to the connections/disconnections during these weeks of operation. If the 3G switch is enabled, these errors arise when the size of the oracle.exe reaches around 2.5 GB typically.


In any case, the above suggestions can be tried out, and they should keep the errors at bay for a while. For better scalability, it is suggested that the db instance be configured for MTS. It should be noted that when a db is configured for MTS, clients can still connect in dedicated mode by simply putting in the (SERVER=DEDICATED) in their tnsnames.ora's connect string."

Thats fantastic stuff. Thanks very much for your help.

:D