Tuning Shared Server

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

SQL> show parameter shared_servers;

NAME TYPE VALUE

------------------------------------ ----------- -----------

max_shared_servers integer

shared_servers integer 1

SQL> alter system set shared_servers=10;

System altered.

SQL> select name from v$shared_server;

NAME

----------

S000

S001

S002

S003

S004

S005

S006

S007

S008

S009

10 rows selected.

SQL> select name from v$dispatcher;

NAME

----

D000

SQL> alter system set dispatchers='(pro=tcp)(dis=5)';

System altered.

SQL> select name from v$dispatcher;

NAME

----

D000

D001

D002

D003

D004

You should query the V$SHARED_SERVER view to obtain information on the current status of shared servers:

SQL> SELECT NAME,DECODE(BUSY+IDLE,0,0, round((busy/(busy+idle))*100,4))

"busy rate"

from v$shared_server;

NAME busy rate

---- ----------

S000 0

S001 0

S002 0

S003 0

S004 0

S005 0

S006 0

S007 0

S008 0

S009 0

10 rows selected.

You can also determine if there is contention for shared server processes by querying the wait and totalq columns of the V$QUEUE dynamic performance view.

SQL> SELECT DECODE(totalq,0,'No Requests',

wait/totalq || 'hundredths of seconds')

"Average Wait Time Per Requests"

FROM v$queue

WHERE type='COMMON';

This query returns the total wait time for all requests and total number of requests for the requests queue. The result of this query looks like this:

Average Wait Time Per Requests

-------------------------------------------------------------

No Requests

SQL> SELECT NAME,(BUSY/(BUSY+IDLE))*100

"DIS BUSY RATE"

FROM V$DISPATCHER;

NAME DIS BUSY RATE

---- -------------

D000 0

D001 0

D002 0

D003 0

D004 0

SQL> SELECT NAME, CUR_IN_CONNECT_RATE,

MAX_IN_CONNECT_RATE,

(CUR_IN_CONNECT_RATE-MAX_IN_CONNECT_RATE) "VARIENCE"

FROM V$DISPATCHER_RATE;

NAME CUR_IN_CONNECT_RATE MAX_IN_CONNECT_RATE VARIENCE

---- ------------------- ------------------- -------------------------------------------------------------------------

D000 0 0 0

D001 0 0 0

D002 0 0 0

D003 0 0 0

D004 0 0 0

You can also query the V$DISPATCHER_RATE view to analyze connection. The V$DISPATCHER view contains columns grouped under CUR,AVG and MAX. Compare CUR and MAX values.

If the performance of connections using shared servers are not satisfactory, and the CUR values are close or equal to the MAX values, then you should consider increasing the number of dispatcher for the corresponding protocols.

If, on the other hand, you find that the performance is satisfactory and the CUR values are substantially below the MAX values, you have configured too many dispatchers. Consider reducing the number of dispatcher.

Sizing the User Global Area:

UGA space used by your connection:

SQL> select SUM(value) ||'bytes' "Total session memory"

from V$MYSTAT,V$STATNAME

where name='session uga memory'

and v$mystat.statistic#=v$statname.statistic#;

Total session memory

---------------------------------------------

1740548bytes

Maximum UGA space used by all users:

SQL> select SUM(value) ||'bytes' "Total session memory"

from V$SESSTAT,V$STATNAME

where name='session uga memory'

and v$sesstat.statistic#=v$statname.statistic#;

Total session memory

---------------------------------------------

3606300bytes

Maximum UGA space used by all users:

SQL> select SUM(value) ||'bytes' "Total max memory"

from V$SESSTAT,V$STATNAME

where name='session uga memory max'

and v$sesstat.statistic#=v$statname.statistic#;

Total max memory

---------------------------------------------

1239344bytes

For all Oracle Shared Server Connection, you need to compute the amount of space required for all shared server users to put their session memory in the shared pool. If needed increase shared_pool_size.

SQL> show parameter shared_pool_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------

shared_pool_size big integer 50331648

SQL>ALTER SYSTEM SET SHARED_POOL_SIZE=200M;

Shutting Down Specific Dispatcher Processes:

You can shutdown any specific dispatcher.

SQL> SELECT NAME, NETWORK,status FROM V$DISPATCHER;

NAME NETWORK STATUS

----------------------------------------------------------------------------------------------------

D000 (ADDRESS=(PROTOCOL=tcp)(HOST=nazmul)(PORT=1159)) WAIT

D001 (ADDRESS=(PROTOCOL=tcp)(HOST=nazmul)(PORT=1169)) WAIT

D002 (ADDRESS=(PROTOCOL=tcp)(HOST=nazmul)(PORT=1780)) WAIT

D003 (ADDRESS=(PROTOCOL=tcp)(HOST=nazmul)(PORT=1662)) WAIT

D004 (ADDRESS=(PROTOCOL=tcp)(HOST=nazmul)(PORT=1663)) WAIT

SQL> ALTER SYSTEM SHUTDOWN 'D002';

The Dispatcher stops accepting new connections and will wait until all of the sessions it handles are disconnected before shutting down.

For a more "immediate" shutdown of the Dispatcher (it is in a bad state or it won't shutdown with the above command) issue this command:

SQL> ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

The IMMEDIATE keyword stops the Dispatcher from accepting new connections and immediately terminates all existing connections through that Dispatcher. After all sessions are cleaned up, the Dispatcher process shuts down.

When a Dispatcher is shut down, other Dispatchers will not inherit the name, so it is possible to see some Dispatchers missing. When starting up a new Dispatcher, it will obtain the next open Dispatcher name. In the case where TCPS D002 Dispatcher was shut down, and a new Dispatcher for TCP was started, the new Dispatcher will be called D002.

Disabling Shared Servers:

You disable shared server by setting SHARED_SERVERS to 0. No new client can connect in shared mode. However, when you set SHARED_SERVERS to 0, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of SHARED_SERVERS or the value of the MAX_SHARED_SERVERS parameter, whichever is smaller. If both SHARED_SERVERS and MAX_SHARED_SERVERS are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS or MAX_SHARED_SERVERS is raised again.

To terminate dispatchers once all shared server clients disconnect, enter this statement:

ALTER SYSTEM SET DISPATCHERS = '';