A shared server process allows a single server process to service several clients, based on the premise that usually in an OLTP environment, a user is more often than not, reading and editing data on the screen than actually executing a DML. What this means is that, there will be chunks of time when the dedicated server process, dedicated to a particular c lient will be sitting idle. It is this idleness that is exploited by the shared server process in servicing several clients together.
Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured. The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that we want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.
In typical systems, the number of shared servers stabilizes at a ratio of one shared server for every ten connections . For OLTP applications, when the rate of requests is low, or when the ratio of server usage to request is low, the connections-to-servers ratio could be higher . If we know the average load on our system, then we can set SHARED_SERVERS to an optimal value. The Below example shows how we can use this parameter .
For Example
Assume a database is being used by a telemarketing center staffed by 1000 agents. On average, each agent spends 90% of the time talking to customers and only 10% of the time looking up and updating records. To keep the shared servers from being terminated as agents talk to customers and then spawned again as agents access the database, a DBA specifies that the optimal number of shared servers is 100 . However, not all work shifts are staffed at the same level. On the night shift, only 200 agents are needed. Since SHARED_SERVERS is a dynamic parameter, a DBA reduces the number of shared servers to 20 at night, thus allowing resources to be freed up for other tasks such as batch jobs .
Setting the Initial Number of Dispatchers
DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)" We can specify multiple dispatcher configurations by setting DISPATCHERS to a comma separated list of strings, or by specifying multiple DISPATCHERS parameters in the initialization file. If we specify DISPATCHERS multiple times, the lines must be adjacent to each other in the initialization parameter file. Internally, Oracle Database assigns an INDEX value (beginning with zero) to each DISPATCHERS parameter. We can later refer to that DISPATCHERS parameter in an ALTER SYSTEM statement by its index number. Some examples of setting the DISPATCHERS initialization parameter follow.
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"
To force the dispatchers to use a specific port as the listening endpoint, add the PORT attribute as follows:
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"
Determining the Number of Dispatchers :
Once we know the number of possible connections for each process for the operating system, calculate the initial number of dispatchers to create during instance startup, for each network protocol, using the following formula:
Number of dispatchers = CEIL ( max. concurrent sessions / connections for each dispatcher )CEIL returns the result roundest up to the next whole integer.
For example, assume a system that can support 970 connections for each process, and that has :
A maximum of 4000 sessions concurrently connected through TCP/IP and A maximum of 2,500 sessions concurrently connected through TCP/IP with SSL then DISPATCHERS attribute for TCP/IP should be set to a minimum of five dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970) :
Depending on performance, we may need to adjust the number of dispatchers.
Steps to configure shared server : To configure shared server we have to enable the following parameter . All the below parameters are dynamic . Below is Demo to configur e the shared server.
1.) alter system set shared_servers= 25;
2.) alter system set max_shared_servers= 50;
3.) alter system set dispatcherS= '(PROT=tcp)(DISP=30)';
4.) Add (SERVER = SHARED) in tnsnames.ora file.
the tnsnames.ora file look like
NOIDA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = noida)
)
To check the status of server fire the below query :
SQL> select distinct server,username from v$session ;
SERVER USERNAME
------------- -----------------
DEDICATED SYS
DEDICATED
Once, i found that after configuring the shared server the above query shows the server status as 'NONE' .
What does it mean if SERVER = 'NONE' in v$session?On googling , i found that , in Shared Server configuration when we see value 'NONE' , it means there is no task being processed by shared server for that session. The server column will infact show status of 'SHARED' if there is some task being processed at that particular time by the shared server process for that session. Hence to check the status , fire some big query and then check the server status .
Disabling Shared Server :
We can disable shared server by setting SHARED_SERVERS to 0. we can do this dynamically with the 'alter system' statement. When we disable shared server, no new clients can connect in shared mode. However, 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:
SQL> alter system set dispatchers='' ;
