5.3. Configuring the Oracle Shared Server

You can configure Oracle Shared Server in a number of ways. You can configure it at the time the database is created, you can use the Enterprise Manager (EM) to configure it after the database has been created, or you can manually configure it by editing initialization parameters. We'll discuss the parameters necessary to configure Oracle Shared Server. We'll also give examples of how to configure Shared Server at database creation or after the database is created using EM.

Figure 5.3. The Shared Server connection process

5.3.1. Defining the Shared Server Parameters

You configure Oracle Shared Server 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.

One advantage of Oracle 10g is that all the parameters necessary to manage Oracle Shared Server can be changed dynamically. This fulfills one of your primary goals of ensuring the highest degree of database availability possible. Let's take a look at the parameters used to manage Oracle Shared Server.

5.3.1.1. Using the DISPATCHERS Parameter

The DISPATCHERS parameter defines the number of dispatchers that should start when the instance is started. This parameter specifies the number of dispatchers and the type of protocol to which the dispatchers can respond. If you configured your database using the Database Configuration Assistant, this parameter may already be configured.

You can add dispatchers dynamically using the ALTER SYSTEM command. You can also configure the parameter using the EM, which will be discussed later in this chapter in the section "Configuring Shared Server using Enterprise Manager."

The DISPATCHERS parameter has a number of optional attributes. Table 5.1 lists and describes several of these. You need to specify only ADDRESS, DESCRIPTION, or PROTOCOL for a DISPATCHERS definition. All the attributes for this parameter can be abbreviated.

The two main attributes are DISPATCHERS and PROTOCOL. For example, if you want to configure three TCP/IP dispatchers and two IPC dispatchers, you set the parameter as follows:

DISPATCHERS = "(PRO=TCP)(DIS=3)(PRO=IPC)(DIS=2)"

You must consider several factors (discussed in the following section) when determining the appropriate setting for the DISPATCHERS parameter.

5.3.1.1.1. Determining the Number of Dispatchers to Start

The number of dispatchers that you start depends on your particular configuration. Your operating system may place a limit on the number of connections that one dispatcher can handle. Consult your operating system documentation to obtain this information.

When determining the number of dispatchers to start, consider the type of work that the database sessions will be performing and the number of concurrent connections that your database will be supporting. The more data intensive the operations and the larger the number of concurrent connections, the fewer sessions each dispatcher should handle. Generally speaking, a starting point is to allow 50 concurrent sessions for each dispatcher.

You can 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)"

You can determine the number of concurrent connections by querying the V$SESSION view. This view shows you the number of clients currently connected to the Oracle server. Here is an example of the query:

SQL> select sid,serial#,username,server,program from v$session
  2* where username is not null
      SID   SERIAL# USERNAME   SERVER    PROGRAM
--------- --------- ---------- --------- ---------------
        7        13      SCOTT  DEDICATED SQLPLUS.EXE
        8        12      SCOTT  DEDICATED SQLPLUS.EXE
        9         4     SYSTEM  DEDICATED SQLPLUS.EXE

In this example, three users are connected to the server. You can ignore any sessions that do not have a user name because these would be the Oracle background processes such as PMON and SMON. If you take a sampling of this view over a typical work period, you get an idea of the average number of concurrent connections for your system. You can then use this number as a guide when you establish the starting number of dispatchers.

You can also query the V$LICENSE 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.

5.3.1.1.2. 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 to the total number of dispatchers that you want, not to the number of dispatchers that you want to add.

You use additional attributes to the DISPATCHERS parameter to configure connection pooling.

5.3.1.1.3. 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. If you manage applications that have a large number of possible client connections but also have a large number of idle connections, you might want to consider configuring this Oracle Shared Server option. Web applications are good candidates for connection pooling because they are typically composed of a large client base with small numbers of concurrent connections.

You 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. You also need to specify the TICK attribute, which sets the number of 10-minute increments of inactivity for a connection to be considered idle.

Here is an example that turns on connection pooling:

DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=1)(POOL=on)(TICK=1)
  (CONNECTIONS=500)(SESSIONS=1000)"

In this example, we want to turn on connection pooling. An idle connection is considered any connection with 10 minutes of inactivity. We want the TCP/IP dispatcher to handle a maximum of 500 concurrent connections and a maximum of 1,000 sessions per dispatcher.

5.3.1.2. 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)

Here is an example of the parameter and adjusting the parameter using the ALTER SYSTEM command:

MAX_DISPATCHERS = 5
ALTER SYSTEM SET MAX_DISPATCHERS=10;

In the ALTER SYSTEM example, the MAX_DISPATCHERS parameter is being set to 10. This will be the maximum number of dispatchers that Oracle Shared Server can start simultaneously.

5.3.1.3. 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.

5.3.1.4. 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;

5.3.1.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;

5.3.1.6. 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

Now that you understand the parameters that are needed to use the Oracle Shared Server, you need to know how to configure these parameters. You can also use the ALTER SYSTEM command to change the parameter as follows:

ALTER SYSTEM SET CIRCUITS = 300;

5.3.2. 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. We will look at these in the following sections.

5.3.2.1. 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. Figure 5.4 shows the database if you elect to configure it as a shared server. You can provide the number of shared servers to start at instance startup.

Figure 5.4. Configuring Shared Server with the DBCA

5.3.2.2. Editing Shared Server Parameters

You can also modify other aspects of the Oracle Shared Server configuration during 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 (see Figure 5.5).

The Dispatcher tab (see Figure 5.6) 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 (see Figure 5.7). 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.

Figure 5.5. The General tab in the DBCA

Figure 5.6. The Dispatcher tab in the DBCA

Figure 5.7. Configuring DISPATCHER in the Advanced Dispatcher Configuration screen

5.3.3. Configuring Shared Server Using Enterprise Manager

You can also configure the Oracle Shared Server using Enterprise Manager. Once you connect to Enterprise Manager via your web browser, click the Administration tab to display a form that allows you to manage all aspects of your Oracle environment. To modify the initialization parameters for Oracle Shared Server, click the All Initialization Parameters link in the Instance section. Figure 5.8 shows the Initialization Parameters screen.

Figure 5.8. The Enterprise Manager Initialization Parameters screen

Initially, this form lists only the parameters that are being used for the instance. If you've already configured the Oracle Shared Server, you will see the parameters described in the previous section listed with their current values. If you are configuring the Oracle Shared Server for the first time, you will not see these parameters. Click the Show All button to display all the available initialization parameters. You can then scroll down to the initialization parameter that you want to modify and type appropriate values for the parameter.

You can also select only those parameters that you want to modify. Type the partial or full name of the parameter that you want to change in the Filter field and click Go to display only the specific parameters or parameters that match the name entered. Enter the new value for the parameter in the Value field next to each parameter.

It is a good idea to validate the change you are making. To do so, click the Show SQL button to display the actual ALTER SYSTEM command that runs to make the change. Figure 5.9 shows an example of an ALTER SYSTEM command that modifies the DISPATCHERS parameter, setting the number of TCP/IP dispatchers to two.

After you make all your changes, you can choose how you want to save them. If you click the Apply button, your changes take effect immediately. The currently running instance is affected by these changes. This is the choice if you want the changes to be effective right away. If you want to save the changes but not have the changes affect the currently running instance, click the Save To File button. This saves all your currently active initialization parameter values along with your newly modified parameter values to a file. This is the best choice if you are using an Oracle initialization parameter file. You can save a copy of the modifications to a file in the location of your choice and then cut and paste the new values into your initialization file. These changes will then be in force the next time your instance starts. If the Oracle Shared Server parameters were configured dynamically using the ALTER SYSTEM command or at database creation, it isn't necessary to stop and start the server.

Figure 5.9. The Enterprise Manager SQL screen

ALTER SYSTEM SET SHARED_SERVERS = 10 SCOPE=MEMORY;