16.4 Shared Servers

---

When using a dedicated server configuration connections are granted exclusively to a specific user request. Therefore, one dedicated server process plus a chunk of connection cache in memory is allocated to each connection. As a result dedicated servers cannot be scaled up to a large number of users. The solution to high resource usage is sharing of those resources using shared servers and dispatchers. A shared server process can be shared between multiple user connections where dispatcher processes dispatch requests and results between a shared server process and a client connection. In effect shared processes are never idle because they are shared or switched between different user connections.

Some functions, mostly database administration functions, are best served and sometimes only served by dedicated server connections. When using a dedicated server configuration process connection requests are routed by the Listener process. The Listener passes the client process an address for an available dedicated server process, which may have to be spawned. In the case of a shared server configuration the Listener will pass the client process the address of an available dispatcher process (spawned if none is available), which in turn sends the client process the address of a shared server process. The client process then communicates with the database server using a shared server process.

16.4.1 Configuration Parameters

Shared server configuration parameters are set in the database server configuration parameters file. Simple example parameter settings for using shared servers and dispatchers are as follows.

DISPATCHERS = "(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1522)" SHARED_SERVERS = 2 CIRCUITS = 10 SHARED_SERVER_SESSIONS = 10 MAX_DISPATCHERS = 3 MAX_SHARED_SERVERS = 5

Note 

 Oracle Database 10 Grid   Many parameters have been greatly simplified, particularly the shared server parameters. The SHARED_SERVERS parameter is the only parameter required to instantiate a shared server configuration.

SHARED_SERVERS and DISPATCHERS are created at database startup. CIRCUITS are virtual circuits for both in-and outbound network traffic. A virtual circuit is a chunk of memory which passes requests and responses between dispatcher and shared server processes. SHARED_SERVER_SESSIONS denotes user sessions per shared server. MAX_DISPATCHERS limits the number of dispatchers and connections per dispatcher. MAX_SHARED_SERVERS specifies the maximum number of shared servers allowed.

The DISPATCHERS parameter can get a lot more complicated. The DISPATCHERS parameter in the configuration parameter file is very much like a TNS name connection string. Effectively a dispatcher is a connection string because it performs a routing function between client processes and shared server processes.

DISPATCHERS = "(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521)"

Now we can add further specifications to the DISPATCHERS parameter.

DISPATCHERS = "(DESCRIPTION=(ADDRESS=       (PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521) (QUEUESIZE=50)))       (SESSIONS=500) (CONNECTIONS=500) (MULTIPLEX=ON) (POOL=ON)          (TICK=2)"

• POOL=ON.   This option enables connection pooling. When using connection pooling not only are shared servers shared but so are dispatchers. This increases scalability in terms of network connections and numbers of users.

• TICK.   Sets a timeout on a connection request and response, making an idle connection available for use by another client request or shared server response when the timeout is exceeded.

• MULTIPLEX=ON.   Oracle Connection Manager provides scalability by providing network node addresses plus routing directions to those network nodes. Oracle Connection Manager is more of a listening router rather than just a network names storage facility. Additionally Oracle Connection Manager can provide what is called multiplexing or funneling in addition to other features such as protocol transparency. Funneling is relevant to tuning. Connection concentration or funneling permits multiple connections between two processes. This can allow a shared server process to service more connection requests than by using connection pooling alone. The result is multiple client connection requests serviced by a single dispatcher process. Multiple Connection Managers can increase the number of concurrent client connections serviced astronomically.

  Note 

   Oracle Database 10 Grid   Oracle Connection Manager is improved.

There can also be multiple dispatchers using different ports.

DISPATCHERS = "(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521)" DISPATCHERS = "(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1522)" 

More dispatchers will decrease user resource usage but decrease performance for each user due to all the switching. However, if connection numbers are high shared server processes are scalable if a middle tier is not utilized. On a Windows server set a single dispatcher for between every 200 to 1,000 connections, depending on application type.

16.4.2 Network Performance Views

Network performance views involve views for shared servers, dispatchers, virtual circuits, in addition to the usual event and wait event statistics views. These are the shared server parameter settings I have in my database.

SHARED_SERVERS = 2 CIRCUITS = 5 SHARED_SERVER_SESSIONS = 5 MAX_SHARED_SERVERS = 5 MAX_DISPATCHERS = 3

When executing queries within this section I had between 10 and 100 concurrent jobs running in my Accounts schema on my database.

Shared Servers

Three views which can be used to inspect shared server usage are the V$SHARED_SERVER, V$QUEUE, and V$SHARED_SERVER_ MONITOR performance views. The first query shows that only one of the shared server processes is being used.

COL name FORMAT a4; SELECT name, messages, bytes, breaks, idle, busy, requests FROM v$shared_server;     NAME   MESSAGES    BYTES   BREAKS     IDLE   BUSY   REQUESTS ----   --------   ------   ------   ------   ----   -------- S000        284    49131        0   810433   4385        141 S001          0        0        0   814722      0          0

This next query shows possible shared server process contention where COMMON examines shared server processes only, in 100th's of a second. The average wait per activity shown is less than 3 100th's of a second. Since only one shared server process is used this is not a problem.

SELECT wait/totalq FROM v$QUEUE WHERE type = 'COMMON';     WAIT/TOTALQ              .269503546

The V$SHARED_SERVER_MONITOR view holds the highest values reached since database startup. My parameter values could be substantially reduced.

SELECT MAXIMUM_CONNECTIONS "Circuits"       ,MAXIMUM_SESSIONS "Shared_Server_Sessions"       ,SERVERS_STARTED "Started"       ,SERVERS_TERMINATED "Terminated"       ,SERVERS_HIGHWATER "Max_Shared_Servers" FROM v$shared_server_monitor;     Circuits  Shared_Server_  Started  Terminated  Max_Shared_                 Sessions                           Servers --------  --------------  -------  ----------  -----------        3               3        0           0            2

Dispatchers

Use the V$DISPATCHER, V$QUEUE, and V$DISPATCHER_RATE performance views to monitor dispatcher activity. The first query shows dispatcher activity. There is little competition for this single dispatcher. Therefore, examining V$DISPATCHER_RATE output is pointless in this case.

COL name FORMAT a4; COL status FORMAT a10; SELECT name, status, messages, bytes, busy, idle, busy/idle FROM v$dispatcher;     NAME   STATUS   MESSAGES   BYTES   BUSY     IDLE   BUSY/IDLE ----   ------   --------   -----   ----   ------   --------- D000     WAIT        284   49131     25   852372   .00002933

As with shared servers this next query on the V$QUEUE performance view shows possible dispatcher process contention. The DISPATCHER value examines dispatcher processes only, in 100th's of a second. The average wait per activity is nonexistent.

SELECT wait/totalq FROM v$QUEUE WHERE type = 'DISPATCHER';     WAIT/TOTALQ -----------           0 

Virtual Circuits

The V$CIRCUIT performance view provides a picture of shared server to dispatcher database connections. This query indicates no problems.

SELECT status, queue, bytes, breaks FROM v$circuit;     STATUS    QUEUE   BYTES   BREAKS ------   ------   -----   ------ NORMAL   NONE      9033        0 NORMAL   NONE      8675        0 NORMAL   SERVER   11484        0

Using Events

Events such as virtual circuit status and dispatcher timer can be used to detect potential problems with shared servers and dispatchers. Events of interest are as follows.

SELECT name "Event" FROM v$EVENT_NAME WHERE name LIKE    '%dispatcher%' OR name LIKE '%circuit%' OR name LIKE '%SQL*Net%';     Event ----------------------------------------------- alter system set dispatcher dispatcher shutdown virtual circuit status dispatcher timer dispatcher listen timer SQL*Net message to client SQL*Net message to dblink SQL*Net more data to client SQL*Net more data to dblink SQL*Net message from client SQL*Net more data from client SQL*Net message from dblink SQL*Net more data from dblink SQL*Net break/reset to client SQL*Net break/reset to dblink

Some simple event statistics can be gathered with this query.

COL event FORMAT a30; COL waits FORMAT 9999990; COL timeouts FORMAT 99999990; COL average FORMAT 99999990; SELECT event "Event", time_waited "Total Time", total_waits    "Waits", average_wait "Average", total_timeouts "Timeouts" FROM V$SYSTEM_EVENT WHERE event IN (       SELECT name FROM v$EVENT_NAME WHERE name LIKE '%          dispatcher%'       OR name LIKE '%circuit%' OR name LIKE '%SQL*Net%' ) ORDER BY EVENT;     Total Event                             Time  Waits  Average  Timeouts -----------------------------  -------  -----  -------  -------- SQL*Net break/reset to client        3      8        0         0 SQL*Net message from client     605113    340     1780         0 SQL*Net message to client            0    344        0         0 SQL*Net more data to client          0      8        0         0 dispatcher timer                958324    630     1521       483 virtual circuit status         1871562    765     2446       618 

That is all there is to know about basic Oracle network tuning. The next chapter will look into Oracle Partitioning and parallelism.

---