-- Sometimes you have no option but to use Multithreaded Servers --

A customer of mine was faced with the situation of having poor memory management even that ASMM was configured, I noticed poor buffer cache utilization 46%, low hit ratio 84% and the sqls were constantly reloading, when faced with the question of why we do not pin objects in the buffers the answer was that their Windows System is 32 bits and was maxed out due to the number of connections to the 3GB limit.

Although we all know that dedicated servers are faster that shared servers, we also know that the most expensive operation in a transaction is I/O, in the case of my customer I know his service time is about 20ms per I/O which is not the best (They use EMC Clarion). So in this case we need to sacrifice a bit of CPU to reduce I/O and do more cache utilization.

The most important factor when configuring MTS is to determinate the number of servers and dispatchers that you will need and this is a good rule of thumb.

For dispatchers of 1 per every 250 connections works well for typical systems, Being too aggressive in your estimates is not beneficial; configuring too many dispatchers can degrade performance.

For shared servers typical systems seem to stabilize at a ratio of 1 shared server for every 10 connections. On NT, exercise care when setting MTS_MAX_SERVERS to too a high value because as mentioned, each server is a thread in a common process. The optimal values for these settings can change based on your configuration; these are just estimates of what seems to work for typical configurations.

Example OLTP Application: If you expect to require 2,000 concurrent connections, begin with 200 shared servers or 1 shared server for every 10 connections. Set MTS_MAX_SERVERS to 400.

Because MTS uses your large_pool for storing MTS related UGA information , we recommend to increase and explicitly set the minimum large_pool_size. If you do not set a value for LARGE_POOL_SIZE, Oracle uses the shared pool for MTS user session memory.

Some important views that will help you to tune your servers / dispatchers ratio:

-  V$DISPATCHER  This view gives information about dispatcher processes.

-  V_$SHARED_SERVER This view gives information about shared server processes.  

-  V$CIRCUITS  This view gives information about the virtual circuits. Virtual circuits are state objects that act as repositories of all the user related state information that needs to be accessed during a database session. There is one virtual circuit per client connection.

-  V$QUEUE This view gives information about messages in the common message queue and the dispatcher message queues. 

- V$DISPATCHER_RATE This view gives information and statistics about the rate at which each dispatcher is receiving and handling messages, events, and so on.

The following initialization parameters control shared server operation:

• SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
• MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
• SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
• DISPATCHERS: Configures dispatcher processes in the shared server architecture.
• MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
• CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.

=======================================================

One of  my readers posted the followintg comment and I decided to share it, thanks Mitra for the tip

" I used to administer quite a few 32 bit oracle boxes on windows and faced the same problem. Its annoying but the solution I found is to resuce the stack size of the "oracle.exe" and the "listener.exe" with the orastack utility. I believe that the default is 1 MB and for oltp applications 128KB works great. Please try this next time and I bet it will get some extra millage for you"

Yes, reducing the Oracle stack size is definitively an option, reducing it too much will produce the error “ORA-03113: end-of-file on communication channel” errors. Oracle supplies the ORASTACK utility to allow customers to modify the default stack size of a thread / session when created in the Oracle executable.

When ORASTACK is run against specific executables it alters the part of the binary header that defines the default stack size used by the create thread API.

Below are examples of setting the stack to 500K for the main executables :



orastack oracle.exe 500000
orastack tnslsnr.exe 500000

orastack svrmgrl.exe 500000

orastack sqlplus.exe 500000