5.4. Managing Shared Server

After you configure the Oracle Shared Server parameters, you need to understand how to view information about Oracle Shared Server. Oracle provides a set of dynamic performance views that you can use to gather information about the Oracle Shared Server configuration and the performance of the Oracle Shared Server. You can also gather information about Oracle Shared Server connections by using the lsnrctl utility.

In the following sections, we will explain how to display information about Oracle Shared Server connections using the listener utility and discuss the various dynamic performance views used to manage Shared Server.

5.4.1. Displaying Information about Shared Server Connections Using the Listener Utility

You can use the lsnrctl command-line listener utility to display information about the dispatcher processes. Remember from the previous section that the Oracle background process PMON registers dispatcher information with the listener. The listener keeps track of the current connection load for all the dispatchers.

Use the lsnrctl services query to view information about dispatchers. The following example shows a listener listening for two TCP/IP dispatchers:

D:>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 21-APR-2004
     20:50:35

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MJW01)
(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
     Handler(s):
      "DEDICATED" established:0 refused:0
          LOCAL SERVER
  Instance "MJW", status READY, has 3 handler(s) for this service...
     Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
          LOCAL SERVER

"D001" established:11 refused:1 current:1 max:1002 state:ready
         DISPATCHER <machine: MJW01, pid: 504>
          (ADDRESS=(PROTOCOL=tcp)(HOST=MJW01)
          (PORT=4152))
       "D000" established:15 refused:3 current:2 max:1002 state:ready
         DISPATCHER <machine: MJW01, pid: 3846>
          (ADDRESS=(PROTOCOL=tcp)(HOST=MJW01)
          (PORT=3845))
The command completed successfully

Notice that the listing displays how many connections each dispatcher is managing, the listening location of the dispatcher, and the process ID of the dispatcher. This example has three active connections, two belong to dispatcher D000 and one belongs to dispatcher D001. The display also shows how many total client connections were established and how many were refused by each dispatcher since the time it was started. This summary information can be helpful when looking at how well the connections are balanced across all the dispatchers. It also can be helpful to see how many connections were refused. A connection can be refused if a user supplies an invalid user ID or password or reaches the MAX_SHARED_SERVER limit.

5.4.2. Using Dynamic Performance Views for Shared Server

Oracle provides dynamic performance views, also known as the V$ views that provide the DBA with a wealth of system statistics and performance information, including information about Oracle Shared Server. These views provide information about the number of dispatchers and shared servers configured, the activity among the shared servers and dispatchers, the activity in the request and response queue, as well as the clients that are connected with shared server connections. We will look at the dynamic performance views in the following sections.

5.4.2.1. Using the V$DISPATCHER Dictionary View

The V$DISPATCHER view contains information about the dispatchers, including dispatcher activity, the number of connections the dispatchers are currently handling, and the total number of connections each dispatcher has handled since instance startup.

Here is sample output from the V$DISPATCHER view:

SQL> select name,status,messages,idle,busy,bytes,breaks from
  2 v$dispatcher

NAME STATUS   MESSAGES    IDLE BUSY  BYTES BREAKS
---- ------ --------- ------- ---- ------ ------
D000 WAIT         168  389645  108  12435       0
D001 WAIT          94  389668   48   6940       0

This example lists two dispatchers: D000 and D001. The WAIT status indicates that the dispatchers are both idle and waiting for a client request to process. The IDLE and BUSY columns display information on how many hundredths of a second each dispatcher has either been waiting to process a client request or actually processing a client request. The MESSAGES column displays the number of messages that a dispatcher has processed since instance startup and the BYTES column is the total size of all messages processed by the dispatcher. Finally, the BREAKS column displays the total number of breaks that the dispatcher has handled since instance startup. A break is an interrupt passed from a client that allows a transaction to be stopped prior to completion. For example, pressing Ctrl+C in some applications will cause a break request to be generated and passed to the dispatcher process. SQL*Plus is an example of an application that will cause a break request to be sent by pressing this keyboard combination.

5.4.2.2. Using the V$DISPATCHER_CONFIG Dictionary View

The V$DISPATCHER_CONFIG view is a new Oracle 10g view that contains configuration information about the dispatchers. This view summarizes the dispatcher configuration and gives information such as the protocol, the listener or listening address of the dispatcher, maximum settings for connection and sessions, and service names information.

The following sample shows a system configured with three dispatchers listening for TCP/IP connections to the orcl service. (The maximum connections and sessions are set at the system at 1,002.)

SQL> select conf_indx,dispatchers,connections,sessions "SESS",service
2  from v$dispatcher_config
3  where network like '%TCP%'

CONF_INDX DISPATCHERS CONNECTIONS   SESS SERVICE
--------- ----------- ----------- ------ -------
        0         3          1002   1002  orcl

5.4.2.3. Using the V$DISPATCHER_RATE Dictionary View

The V$DISPATCHER_RATE view shows statistics for the dispatchers, such as the average number of bytes processed, the maximum number of inbound and outbound connections, and the average rate of bytes processed per client connection. The columns in the table that begin with CUR show current statistics. Columns that begin with AVG or MAX show historical statistics taken at some time interval. The time interval is typically measured in hundredths of a second. The scale measurement periods used for each of the column types is contained in the columns that begin with SCALE. This information can be useful when you are taking load measurements for the dispatchers.

Here is a sample of the output from this view.

SQL>select name,cur_event_rate,cur_msg_rate,
    cur_svr_byte_rate
 from v$dispatcher_rate

NAME CUR_EVENT_RATE CUR_MSG_RATE CUR_SVR_BYTE_RATE
---- -------------- ------------ -----------------
D000           5300       4    0                 0
D001           5205       3    0                 1

This code example shows two dispatchers, D000 and D001, running on the database. The CUR_EVENT_RATE column is a measure of how quickly dispatchers are responding to client requests. From this example, we can determine that client requests are being responded to at a rate of 5,300 per minute for D000 and 5,205 per minute for D001. This is not the actual number of events handled, but the measure of the rate of requests per minute that the dispatcher is handling. The CUR_MSG_RATE is a measure of how quickly the dispatcher has been sending a client request to the Shared Servers. We can see in this example that on average D000 has been passing four requests per second to the Shared Servers for processing and that D001 has been passing three requests per minute to the Shared Servers for processing. Notice the scale for the CUR_ EVENT_RATE is events per minute and that the scale for CUR_MSG_RATE is messages per second. These measures give some indication as to how quickly the Shared Servers and dispatchers are responding to client requests. The other columns provide information on the maximum and average rates at which the dispatcher is servicing client requests.

5.4.2.4. Using the V$QUEUE Dictionary View

The V$QUEUE view contains information about the request and response queues, such as how long requests are waiting in the queues. This information is valuable when you are trying to determine if more shared servers are needed.

The following example shows the COMMON request queue and two response queues:

SQL> select * from v$queue;

PADDR    TYPE          QUEUED      WAIT     TOTALQ
-------- ---------- --------- --------- ---------
00       COMMON             0         0        152
03C6C244 DISPATCHER         0         0         91
03C6C534 DISPATCHER         0         0         71

The PADDR column lists the address of the process that owns the queue. This example shows that no items are waiting in the queue because the QUEUED column is zero. We also have not experienced any queue waits because all WAIT values are zero. We would want to make sure that the WAIT column stays close to zero so no processes are waiting in the queues. The TOTALQ column represents the total number of messages that have ever been in the queue.

5.4.2.5. Using the V$CIRCUIT Dictionary View

The V$CIRCUIT dictionary view displays information about Oracle Shared Server virtual circuits, such as the volume of information that has passed between the client and the dispatcher and the current status of the client connection. The SADDR column displays the session address for the connected session. This can be joined to the V$SESSION view to display information about the user to whom this connection belongs.

Here is a sample output from this view:

SQL> select circuit,dispatcher,server,waiter WTR,
    2 status,queue,bytes from v$circuit;

CIRCUIT  DISPATCH SERVER   WTR STATUS QUEUE  BYTES SADDR
-------- -------- -------- --- ------ ------ ----- ------
03E2A624 03C6C244 00       00  NORMAL NONE   47330 03C7AB68
03E2A724 03C6C534 03C6BC64 00  NORMAL SERVER 43572 03C79BE8

You can see from this example that two active connections are being managed by the dispatchers. The value of SERVER in the QUEUE column displayed for the second circuit tells us that the circuit is currently active and processing a user request.

5.4.2.6. Using the V$SHARED_SERVER Dictionary View

The V$SHARED_SERVER view contains information about the shared server processes. It displays information about the number of requests and the amount of information processed by the shared servers. It also indicates the status of the shared server (that is, whether it is active or idle).

Here is a sample output from this view:

SQL> select name,status,messages,bytes,idle,busy,
     requests from v$shared_server;

NAME STATUS MESSAGES  BYTES  IDLE    BUSY  REQUESTS
---- ------ --------  -----  -----  ----  --------
S000 EXEC        372  86939  98472   300        175
S001 EXEC         26  9851   98703    38         13

The sample depicts two shared servers: S000 and S001. The status of EXEC signifies that the shared servers are currently executing SQL statements. You can see that S000 has had more activity than S001. This would typically be the case in a lightly loaded system. S000 is the first shared server in our sequence and will be the first shared server used when a client request comes in if it is available. If the S000 shared server is not available, S001 is used.

5.4.2.7. Using the V$SHARED_SERVER_MONITOR Dictionary View

The V$SHARED_SERVER_MONITOR view contains information that can assist in tuning the Oracle Shared Server, including the maximum number of concurrent connections attained since instance startup and the total number of servers started since instance startup.

The following query shows an example of output from the V$SHARED_SERVER_MONITOR view:

SQL> select maximum_connections "MAX CONN",maximum_sessions "MAX SESS",
     servers_started "STARTED" from v$shared_server_monitor;

MAX CONN MAX SESS  STARTED
-------- --------- --------
115      120       10

This example shows that we have reached a maximum of 115 concurrent connections since the instance was started. It also shows that the highest number of concurrent sessions since instance startup is 120. It is important to compare the MAX SESS value returned by the query to the value of the initialization parameter SHARED_SERVER_SESSIONS. If the maximum sessions value reaches the SHARED_SERVER_SESSIONS value, users will be unable to connect via Shared Server until the number of sessions falls below this value. If this occurs, Oracle records an ORA-00018 error in the alert log, indicating that you have exceeded the maximum number of sessions. If you see that the maximum sessions value is being approached, you might want to consider increasing the SHARE_SERVER_SESSIONS value. You need to monitor this value to avoid unnecessary disruptions in client connections.

5.4.2.8. Using the V$SESSION Dictionary View

The V$SESSION view contains a wealth of information about the client session, such as the client session address, user name, session status, and the operating system user name. The SERVER column indicates whether this client is using a dedicated session or a dispatcher. The following listing shows an example of the V$SESSION view displaying the server information. (This listing ignores any rows that do not have a user name to avoid listing information about the background processes.)

SQL> select username,program,server from v$session
     where username is not null;

USERNAME          PROGRAM           SERVER
---------------  --------------   ---------
SYSTEM            SQLPLUS.EXE      NONE
SCOTT             SQLPLUS.EXE       SHARED

Notice that user Scott has a server value of SHARED. This means that Scott is connected to a dispatcher. The SYSTEM user is connected using a local connection because the server value is NONE. If a user connects using a dedicated connection, the server value is DEDICATED.

5.4.3. Requesting a Dedicated Connection in a Shared Server Environment

You can configure Oracle Shared Server connections and dedicated server connections to connect to a single Oracle server. This is advantageous if you have a mix of database activity. Some types of activities are well suited to Oracle Shared Server connections, and other types of activities are better suited to dedicated connections.

By default, if Oracle Shared Server is configured, a client is connected to a dispatcher unless the client explicitly requests a dedicated connection. As part of the connection descriptor, the client has to send information requesting a dedicated connection. Clients can request dedicated connections if the names resolution method is localnaming. You cannot use this option when using the hostnaming names resolution method. If localnaming is being used, you can make the necessary changes to the tnsnames.ora file to allow dedicated connections. You can make these changes manually, or you can use Oracle Net Manager.

5.4.3.1. Configuring Dedicated Connections Manually

If you are using localnaming, you can add a parameter to the service name entry in the tnsnames.ora file. The parameter (SERVER=DEDICATED) is added to the DBA net service name. (The SERVER parameter can be abbreviated as SRVR.) Here is an example of the entry in the tnsnames.ora file:

# tnsnames.ora Network Configuration File:
# C:oracleproduct10.1.0db_1
etworkadmin	nsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MJW01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SRVR = DEDICATED)  # Request a dedicated connection for DBA
    )
  )

5.4.3.2. Configuring Dedicated Connections Using Oracle Net Manager

You can use Oracle Net Manager to modify the connection type for a service. In Windows, Oracle Net Manager is a tool; in Unix, you open Oracle Net Manager by executing netasst.

After you start Oracle Net Manager, follow these steps:

Figure 5.10 shows an example what your Net Manager screen should look like if you configured the Dedicated Server selection properly.