Tuning ORACLE SHARED SERVER Parameters

Tuning ORACLE SHARED SERVER Parameters

Using shared server enables to reduce number of processes and the amount of memory consumed on the server machine. It beneficial for OLTP users performing intermittent transactions.
Using shared servers rather than dedicated servers is also generally better for systems that have a high connection rate to the database. With shared servers, when a connect request is received, a dispatcher is already available to handle concurrent connection requests. With dedicated servers, on the other hand, a connection-specific dedicated server is sequentially initialized for each connection request.
Performance of certain database features can improve when a shared server architecture is used, and performance of certain database features can degrade slightly when a shared server architecture is used. For example, a session can be prevented from migrating to another shared server while parallel execution is active.A session can remain nonmigratable even after a request from the client has been processed, because not all the user information has been stored in the UGA.
If a server were to process the request from the client, then the part of the user state that was not stored in the UGA would be inaccessible. To avoid this, individual shared servers often need to remain bound to a user session. To check how many shared servers are currently running by issuing the following query:
SELECT COUNT(*) "Shared Server Processes"
FROM V$SHARED_SERVER
WHERE STATUS != 'QUIT';
Proper configuration of shared servers can result in significant performance improvement.

* Oracle Shared Server initialization parameters:
Alter system set SHARED_SERVERS = nn;
Alter system set MAX_SHARED_SERVERS = nn;
Alter system set DISPATCHERS = '(PROTOCOL=TCP) (DISPATCHERS=nn)';
Alter system set MAX_DISPATCHERS = nn;
Alter system set CIRCUITS = nn;
Alter system set SHARED_SERVER_SESSIONS = nn;
 

Note: The default value of MAX_SHARED_SERVERS is dependent on the value of SHARED_SERVER. If SHARED_SERVERS is less than or equal to 10, then MAX_SHARED_SERVERS defaults to 20.
If SHARED_SERVERS is greater than 10, then MAX_SHARED_SERVERS defaults to two times the value of SHARED_SERVERS.

 
* Check the usage for dispatcher processes. 
Some high busy_rate possible indicate contention for dispatcher. Consider increasing the number of dispatcher.
column protocol format a60;
Select network as protocol, status, sum(owned) as clients, 
trunc(sum(busy)/(sum(busy)+sum(idle)),3) as busy_rate
from v$dispatcher
group by network, status;

 
* Check the values of dispatchers to analyze contention. 
  If CUR values are close or equal to the MAX values, consider increasing the number of dispatchers.
 

Select NAME, PADDR, CUR_LOOP_RATE, CUR_EVENT_RATE, CUR_EVENTS_PER_LOOP, CUR_MSG_RATE, CUR_SVR_BUF_RATE, CUR_SVR_BYTE_RATE, CUR_SVR_BYTE_PER_BUF, CUR_CLT_BUF_RATE,
CUR_CLT_BYTE_RATE, CUR_CLT_BYTE_PER_BUF, CUR_BUF_RATE, CUR_BYTE_RATE, CUR_BYTE_PER_BUF,CUR_IN_CONNECT_RATE, CUR_OUT_CONNECT_RATE, CUR_RECONNECT_RATE
from v$dispatcher_rate;
 

Select NAME, PADDR, MAX_LOOP_RATE, MAX_EVENT_RATE, MAX_EVENTS_PER_LOOP, MAX_MSG_RATE, MAX_SVR_BUF_RATE, MAX_SVR_BYTE_RATE, MAX_SVR_BYTE_PER_BUF, MAX_CLT_BUF_RATE,
MAX_CLT_BYTE_RATE, MAX_CLT_BYTE_PER_BUF, MAX_BUF_RATE, MAX_BYTE_RATE, MAX_BYTE_PER_BUF, MAX_IN_CONNECT_RATE, MAX_OUT_CONNECT_RATE, MAX_RECONNECT_RATE
from v$dispatcher_rate;

 
* Check the average wait time (expressed in hundredths of a seconds). 
With a increasing value consider increase the number of dispatchers.
Select decode(sum(totalq),0,'No Responses', trunc( sum(wait)/sum(totalq),3) ) as average_wait_time
from v$queue q, v$dispatcher d where q.type = 'DISPATCHER' and q.paddr = d.paddr;

 
* Check the current status of shared servers. 
With high pct_busy consider increase the number of shared servers.
Select name, requests, busy*100/(busy+idle) as pct_busy, status
from v$shared_server
where status != 'QUIT';

 
* Check the average wait time per requests in hundredths of seconds.
  With high wait time consider increase the number of shared servers.
Select  decode(totalq, 0, 'No Requests', trunc( wait/totalq,3) ) as average_wait_time_hund_secs
from v$queue q
where q.type = 'COMMON';