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 = '';
Comments