During the Database operations are active, in the larger database environments, number of concurrent connections may be accessing the Database. This increases requirement of physical resources. It is essential to use the existing resources and enable multiple concurrent connections without increasing physical resource requirements, through sharing resources groups of users. Shared Server solves this problem.
Shared server is preferred for small transactions with small result sets. Dedicated Server is to be configured for large transactions and data warehouse applications.
There is one request queue for all dispatchers, but each dispatcher has its own response queue.
Another structure needs to be configured for Shared Server is User Global Area (UGA). To accommodate UGA, Large Pool has to be configured in SGA.
The listener plays an important role in the Oracle Shared Server environment. The
listener supplies the client with the address of the dispatcher to connect to when a
user requests connections to an Oracle Shared Server. The listener maintains a list of
dispatchers available from the Oracle Shared Server.
The Oracle background process PMON (process monitor) notifies the listener as to which dispatcher is responsible for servicing each virtual circuit. The listener is then aware of the number of connections that the dispatcher is managing. This information allows the listener to take advantage of dispatcher load balancing.
Load balancing allows the listener to make intelligent decisions about which dispatcher to redirect client connections to so that no one dispatcher becomes overburdened. When the listener receives a connection request, it looks at the current connection load for each dispatcher and redirects the client connection request to the least- loaded dispatcher. The listener determines the least-loaded dispatcher for all nodes, if Real Application Clusters (RAC) are being used, followed by the least-loaded instance for the node, and finally by the least-loaded dispatcher for the instance. By doing so, the listener ensures that connections are evenly distributed across dispatchers.
The listener can either redirect the client connection to an available dispatcher or directly hand off the request to the dispatcher. The latter is performed whenever possible and is done typically when the listener and database service exist on the same node. When the listener and database service exist on different nodes, the redirection method is used.
When a client connection terminates, the listener is updated to reflect the change in the number of connections that the dispatcher is handling.
Shared Server can be configured in the following ways:
- at the time the database is createed,
- Enterprise Manager (EM) to configuure it after the database has been created
- or manually configure it by editinng initialization parameters.
A default network environment is that a Listerner TCP/P Protocol listening on the default port (1521).
For a non-default setting or additional options are required, DISPATCHERS parameter has to be manually configured.
Following parameters are to be modified:
DISPATCHERS Parameter: defines the number of dispatchers that should start when the instance is started and the type of protocol to which the dispatchers can respond. (if DBCA was used to configure the database, this parameter may already be configured.
The DISPATCHERS parameter has a number of optional attributes. All these attributes can be abbreviated.
DISPATCHERS = "(PRO=TCP)(DIS=3)(PRO=IPC)(DIS=2)"
PRO=protocol i.e, TCP and DIS=dispatchers i.e, 2.
No.of dispatchers to be started at instance startup depends on operating system and other configurations, type of work, number of concurrent connections that the database will be supporting. 50 concurrent sessions for each dispatcher is a better starting point.
Use the following formula to determine the number of dispatchers to configure initially:
Number of Dispatchers = CEIL (maximum number of concurrent sessions / connections per dispatcher)
For example, if you have 500 concurrent TCP/IP connections, and you want each dispatcher to manage 50 concurrent connections, you need 10 dispatchers. You set your DISPATCHERS parameter as follows:
DISPATCHERS = "(PRO=TCP)(DIS=10)"
V$SESSION view shows you the number of clients currently connected to the Server. V$LICENSE view also can be used to view and check the SESSION_CURRENT and SESSION_ HIGHWATER columns to see the current number of sessions and the maximum number of concurrent sessions since the instance was started.
ALTER SYSTEM SET DISPATCHERS="(PRO=TCP)(DIS=5)";
Notice that you set the number value specified in the query is Total number of dispatchers but not the number to increase.
Enable connection pooling by adding attributes to the DISPATCHERS parameter. The POOL attribute specifies that a dispatcher is allowed to perform connection pooling. Set this attribute to the value ON to enable connection pooling for a dispatcher. TICK attribute also needs to be specified, which sets the number of 10-minute increments of inactivity for a connection to be considered idle.
MAX_DISPATCHERS = (maximum number of concurrent sessions/connections per dispatcher)
The number of servers necessary depends on the type of activities that your users are performing. Oracle monitors the response queue loads, starts additional shared servers as needed, and removes these shared servers when the servers are no longer needed. Generally, for the types of high think applications that will be using shared server connections, 25 concurrent connections per shared server should be adequate. If the users are going to require larger result sets or are doing more intensive processing, you’ ll want to reduce this ratio.
Here is an example of setting the SHARED_SERVERS parameter:
SHARED_SERVERS = 3
You can start additional Oracle Shared Servers or reduce the number of Oracle Shared Servers dynamically using the ALTER SYSTEM command. You can start any number of Oracle Shared Servers up to the MAX_SERVERS setting. Here is an example of adding three additional Oracle Shared Servers to a system initially configured with two Shared Servers:
ALTER SYSTEM SET SHARED_SERVERS = 5;
Notice that you set the number to the total number of Oracle Shared Servers that you want, not to the number of Oracle Shared Servers that you want to add. Using the SHARED_SERVER_SESSIONS Parameter
The SHARED_SERVER_SESSIONS parameter specifies the total number of Oracle Shared Server sessions that are allowed for the Oracle instance. If the number of Oracle Shared Server client connections reaches this limit, any clients that attempt to connect via an Oracle Shared Server connection will receive the following error message:
ORA-00018 maximum number of sessions exceeded
Once the number of Oracle Shared Server connections falls below this number, additional shared server connections can be established. Using this parameter limits the total number of shared server sessions. Dedicated server connections are still allowed if this limit is reached. This parameter can be set in the Oracle initialization file and can be modified dynamically using the ALTER SYSTEM command. Here is an example of how you specify the initialization parameter:
SHARED_SERVER_SESSIONS = 2
Here is an example of how to dynamically modify the parameter using the ALTER SYSTEM command:
ALTER SYSTEM SET SHARED_SERVER_SESSIONS = 5;
The V$SHARED_SERVER_MONITOR view can assist in determining the maximum number of Oracle Shared Servers that have been started since the Oracle instance was started.
Here is an example of the parameter and the ALTER SYSTEM command that will change the value MAX_SHARED_SERVER value to 20: MAX_SHARED_SERVERS = 5 ALTER SYSTEM SET MAX_SHARED_SERVERS = 20;
Here is an example of the parameter: CIRCUITS = 200
These parameters can be configured as shown below, using ALTER SYSTEM command.
ALTER SYSTEM SET CIRCUITS = 300;
You can provide the number of shared servers to start at instance startup.
Configuring Shared Server with the DBCA
In the General tab, you can override the default values and choose a value for the maximum number of shared servers and dispatchers.
The Dispatcher tab allows you to configure other attributes of the Oracle Shared Server DISPATCHERS parameter. The attributes include the maximum number of sessions and connections (the SESSIONS and CONNECTIONS attributes), the service name that the database should register with the listener (the SERVICE attribute), and the name of the listener to register with if it is not the default listener (the LISTENER attribute).
The Advanced Dispatcher Configuration screen allows you to set up advanced attributes of the DISPATCHERS parameter. To open this screen, click the Advanced Options button in the Dispatcher tab. The advanced features of Oracle Shared Server are multiplexing and connection pooling and can be configured in this screen.
Multiplexing requires that the Oracle Connection Manager option be configured.