Shared Server -- Part III

To continue with the shared server series, as mentioned in my last post, I will here be taking up the issues of configuration of memory, and the management of shared servers using available data dictionary views and initialization parameters.
How much memory should you configure for shared servers?
Well, that may be based upon the memory requirements of a representative session multiplied by the expected number of user connections you expect to connect simultaneously. Querying data dictionary views v$session, v$sesstat, v$statname gives us the memory usage of a representative user session. Here's how I work it out :

system@ORCL-SQL>>SELECT p.NAME,
       s.VALUE
FROM   v$sessTat s,
       v$sTatName p
WHERE  s.sTatIsTic# = p.sTatIsTic#
       AND p.NAME IN ('session uga memory',
                      'session uga memory max')
       AND s.sId IN (SELECT sId
                     FROM   v$Session
                     WHERE  UserName = 'SCOTT');


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 86720
session uga memory max 152184

The session uga memory is representative of the amount of memory that Scott is using in the session while uga memory max represents the currently maximum amount of memory that has been used at any time during Scott's session. If Scott is your representative user, then all you need to do is to multiply Scott's memory usage by the number of users you expect to simultaneously connect to the database. For example, if you guesstimate that 35 users may be connecting at a given time then you can configure about 35*(152184/1024) i.e., about 520 KB of memory.
Oracle recommends using the large pool instead of the shared pool for configuring shared servers.The mechanics of using the large pool for configuring shared servers is pretty straight forward--all you need to do is configure a large pool, if it is there, memory will be allocated out of it, otherwise the shared pool will be used.
Now, let's have a look at the initialization parameters that need to be configured, set, or modified while configuring an instance to use shared server:

* dispatchers
* shared_servers
* shared_server_sessions
* max_shared_servers
* max_dispatchers
* local_listener
* large_pool
* large_pool_size

As I had pointed out in my last post, dispatchers is the key initialization parameter that need to be set in order to meaningfully implement shared servers. Of course, if you have created a shared server database, using a tool like the database creation assistant (DBCA), then you already have a dispatcher configured to cater to the TCP/IP protocol, but in practice you may want to keep dispatchers configured to handle other protocols as well.

The initialization parameter shared server specifies the number of server processes that you want to create when an instance is started up. If system load decreases, this minimum number of servers is maintained. Therefore, you should take care not to set this parameter too high at system startup.

shared_server_session specifies the total number of shared server architecture user sessions to allow. Setting this parameter enables you to reserve user sessions for dedicated servers.

max_shared_servers, as the name implies, limits the number of shared servers running simultaneously.

max_dispatchers specifies the maximum number of dispatcher processes allowed to be running simultaneously. The default is 5 and it applies only if dispatchers have been configured for the system.The value of max_dispatchers should equal the maximum number of concurrent sessions divided by the number of connections for each dispatcher. For most systems, a value of 250 connections for each dispatcher provides good performance.For example, if you have 1000 concurrent sessions running, and your dispatcher alows a maximum of 300 connections each, the number of dispatchers to configure would be ceil(1000/300) i.e. 4.

The initialization parameter local_listener specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the tnsnames.ora file or other address repository as configured for your system.

The initialization parameter large_pool lets you specify the size (in bytes) of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers.

The initialization parameter large_pool_size is an interesting parameter. In case you are using a shared server connection, Oracle adds about 250k of memory for each session if you have configured dispatchers. The final derived value also includes a port-specific amount of memory for backup I/O buffers. So, there is always the potential for the total derived value to be too large to actually allocate, or it may impact the performance negatively. In such cases it is always advisable to set large_pool_size to a sufficiently small value so that the database may start.

Ok, once you have created a shared server connection and configured an optimal number of dispatchers for your system, how do you make the thing work? Well, you do nothing! Once you have configured shared servers for an instance, new connections begin to use it of it's own accord. You don't have to make any changes to your Oracle Net files for using shared server.If it is there it will be used. In fact, in a shared server environment, if you ever require to use a dedicated server connection, one way to do it would be to make a separate entry in the tnsnames.ora file(assuming that you are using local naming). I demonstrate below an example of this.While I am manually editing the tnsnames.ora file, I suggest that you use the Net Configuration Assistant to create a new net service name and then add attribute (SERVER=DEDICATED) to the list of CONNECT_DATA attributes.

SAIBAL.GHOSH=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL=TCP) (HOST=saibal.ghosh.org) (PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=saibal) (SERVER=DEDICATED)))

Another less flexible way to do it would be through the sqlnet.ora file. Just set the parameter USE_DEDICATED_SERVER=ON and you are on your way. However, this approach affects every session you initiate and will not serve your purpose, if you need to use dedicated server only occasionally, like say, for example when you are using RMAN. The better approach definitely would be to create a new net service name just for this purpose.

Now, let's take up the V$ views that return information and statistics about shared server connections and processes,

V$SHARED_SERVER: This view returns process name, process address, status and other useful statistics for each shared server process that is currently running as part of the instance.

V$DISPATCHER: This view returns information, such as the dispatcher's name, network address, process address, status for each dispatcher process associated with the instance.

V$DISPATCHER_RATE: Returns rate statistics for each dispatcher.

V$CIRCUIT: Returns a single row for each shared server connection to the instance. A circuit, being a connection through a specific dispatcher and shared server process. This view returns information relating to each shared server circuit to a specific dispatcher, shared serevr process, and session.

V$QUEUE: Returns information for each queue in the instance. Each dispatcher will have one response queue associated with it, and there will always be one common request queue for the instance. Thus, the number of rows returned by V$QUEUE will always equal the number of dispatchers plus one.

So, that concludes my three-part primer on shared servers If there are any further clarifications needed on any aspect of this series, I will be glad to respond as need be. In my next post, which again will be a multi-part series, I intend to take up a performance tuning issue: Access Paths and it's related topics.