Shared Server

Steps of shared server request processing:

  1. Client makes a request

  2. Listener determines the least busy dispatcher address and passes it back to the client.

  3. Client disconnects from listener and connects to dispatcher.

  4. PMON registers connection information with the listener.

  5. Dispatcher places client request on request queue.

  6. Shared server gets request from request queue, connects to database, processes client request.

  7. Shared server places response on response queue.

  8. Dispatcher gets response from queue

  9. Dispatcher returns result to client.

In case of Shared Server change the following Parameter in SPFILE/PFILE:

SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=nazmul)(PORT=1531))" scope=both;

SQL> ALTER SYSTEM SET DISPATCHERS='(ADDRESS=(PROTOCOL=tcp)(host=nazmul)(LIS=1531))(DISPATCHERS=2)' SCOPE=BOTH;

Managing the number of dispatcher:

ALTER SYSTEM SET DISPATCHERS='(ADDRESS=(PROTOCOL=tcp)(host=nazmul)(LIS=1531))(DISPATCHERS=4)' SCOPE=BOTH;

Enabling Connection Pooling

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.

ALTER SYSTEM SET

DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=2)(POOL=on)(TICK=1)(CONNECTIONS=950)(SESSIONS=4000)";

The DISPATCHERS and CONNECTIONS attributes do not have to be specified, since the default for DISPATCHERS is 1 and the default for CONNECTIONS is the maximum number of connections for each process, which is operating system dependent.

Using the MAX_DISPATCHERS Parameter

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)

SQL> alter system set max_dispatchers=10;

View the concurrent session:

SQL> select sid,serial#,username,server,program from v$session

where username is not null;

SHARED_SERVER Parameter:

View the number of shared server:

SQL> show parameter shared_servers;

Increasing the number of shared server:

SQL> ALTER SYSTEM SET SHARED_SERVERS = 5;

Set the maximum number of session per shared server:

SQL> ALTER SYSTEM SET SHARED_SERVER_SESSIONS = 5;

Set the maximum shared server:

SQL> ALTER SYSTEM SET MAX_SHARED_SERVERS = 25;

Some Dynamic Performance View for shared server:

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

SQL> select conf_indx,dispatchers,connections,sessions "SESS"

from v$dispatcher_config;

SQL> select * from v$queue;

SQL> select name,status,messages,bytes,idle,busy,

requests from v$shared_server;

See the dispatcher busy rate:

SQL>Select name, (busy / (busy + idle))*100

"Dispatcher % busy Rate"

From V$DISPATCHER;

Measuring How Long Users Are Waiting for Dispatchers

SQL>SELECT decode(sum(totalq),0,'No Responses',

Sum(wait)/sum(totalq)) "Average Wait time"

FROM V$QUEUE q, V$DISPATCHER d

WHERE q.type = 'DISPATCHER'

AND q.paddr = d.paddr;