Oracle Shared Server

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.

Oracle Shared Server Infrastructure

Several modifications take place inside the internal memory structures of the Oracle Server when using Shared Server. The Listener behaviour is also changes when using Shared Server.

PGA and SGA Changes When Using Oracle Shared Server

Two new structures are created in System Global Area (SGA) called Request Queue and Response Queue. A process called Dispatcher in Oracle Shared Server receives requests from clients and places in Request Queue. The Shared server processes these requests in fi-fo manner and places in response queues. The Dispatcher hands over the result to the corresponding clients.

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.

Steps in the Oracle Shared Server connection process after the database has been started and the dispatcher processes have been started:
• The client contacts the Oracle database server after resolving the service name.
• The listener validates the Oracle Service Name supplied by the client and hands
• off or redirects the client connection to the least-busy dispatcher.
• The listener sends information back to the client so the client can redirect the
• connection to the appropriate dispatcher process.
• The dispatcher process manages the client server request.
• PMON registers connection information with the listener.

Configuring the Oracle Shared Server

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.

Defining the Shared Server Parameters

Shared Server is configured by adding or modifying parameter values in the Oracle initialization file. These parameters identify the number and type of dispatchers, the number of shared servers, and the name of the database that you want to associate with the Shared Server. They can be changed dynamically.

Following parameters are to be modified:

DESPATCHERS

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.

Dispatchers can be added
• by dynamically using the ALTER SYSTEM command
• or by using the Enterprise Manager Database Configuration Assistant.

The DISPATCHERS parameter has a number of optional attributes. All these attributes can be abbreviated.

The two main attributes are
• DISPATCHERS and
• PROTOCOL

DISPATCHERS = "(PRO=TCP)(DIS=3)(PRO=IPC)(DIS=2)"
------------
PRO=protocol i.e, TCP and DIS=dispatchers i.e, 2.

Determining the No. of Dispatchers to Start

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.

Managing the Number of Dispatchers

You can start additional dispatchers or remove dispatchers dynamically using the ALTER SYSTEM command. You can start any number of dispatchers up to the MAX_DISPATCHERS setting, which is discussed next. Here is an example of adding three TCP/IP dispatchers to a system configured with two TCP/IP dispatchers:

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.

Configuring Connection Pooling with the DISPATCHERS Parameter

Connection pooling gives Oracle Shared Server the ability to handle a larger volume of connections by automatically disconnecting idle connections and using the idle connection to service an incoming client request. If the idle connection becomes active again, the connection to the dispatchers is automatically reestablished. This provides added scalability to Oracle Shared Server.

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.

Using the MAX_DISPATCHERS Parameter

You set the MAX_DISPATCHERS parameter to the maximum number of dispatchers that you anticipate needing for the Oracle Shared Server. In Oracle 10g, this parameter can now be set dynamically using the ALTER SYSTEM command. The maximum number of processes that a dispatcher can run concurrently is operating-system dependent. Use the following formula to set this parameter:

MAX_DISPATCHERS = (maximum number of concurrent sessions/connections per dispatcher)

Using the SHARED_SERVERS Parameter

The SHARED_SERVERS parameter specifies the minimum number of Shared Servers to start and retain when the Oracle instance is started. A setting of 0 or no setting means that Shared Servers will not be used. If dispatchers have been configured, the default value of SHARED_SERVERS is 1. This parameter can be changed dynamically, so even if shared servers are not configured when the instance starts, they can be configured without bringing the Oracle instance down and restarting it.

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:

ERROR:
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;

Using the MAX_SHARED_SERVERS Parameter

The MAX_SHARED_SERVERS parameter sets the maximum number of Oracle Shared Servers that can be running concurrently. This number can be modified dynamically using the ALTER SYSTEM command. Generally, you should set this parameter to accommodate your heaviest work times. If no value is specified for MAX_SHARED_SERVERS, the number of Oracle Shared Servers that can be started is unlimited, which is also the default setting.

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;

Using the CIRCUITS Parameter

The CIRCUITS parameter manages the total number of virtual circuits allowed for all incoming and outgoing network sessions. There is no default value for this parameter, and it does influence the total size of the SGA at system startup. Generally, you do not manually configure this parameter unless there is a need to specifically limit the number of virtual circuits.

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;

Configuring Shared Server at Database Creation

The Oracle Shared Server option can be configured when a database is created using the Database Configuration Assistant. The Database Configuration Assistant (DBCA) devotes several screens to configuring this option.

Selecting the Connection Mode

During the process of creating a database using the DBCA, you are presented with the option of selecting a connection mode. You can either select dedicated server or Oracle Shared Server.

You can provide the number of shared servers to start at instance startup.

Configuring Shared Server with the DBCA

Editing Shared Server Parameters

Other aspects of the Oracle Shared Server configuration can be modifiedduring database creation. Click the Edit Shared Server Parameters button on the Connection Mode tab to change the maximum number of Oracle Shared Servers and dispatchers, the number of dispatchers to start up, and other shared server options.

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.