How to configure Shared Server?

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  

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="(PROTOCOL=TCP)(DISPATCHERS=2)"  
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) :

DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'
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='' ;  

Enjoy   :-)