Dedicated Server Vs Shared server

What is Shared server!
What dedicated server connectivity!
How to configure share server!
How to establish dedicated connection in share server!
Benefit of shared server!
How to Monitor Share shared server!

Configuration

Shared server configuration are mainly starts with below two parameters
§ALTER SYSTEM SET SHARED_SERVERS = 5;
§ALTER SYSTEM SET DISPATCHERS="(PROTOCOL=TCP) (SERVICE=SIDXDB)"
Dispatcher can be configured with specific address details.
§ALTER SYSTEM SET DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))(DISPATCHERS=2)"

Parameters for Shared Server

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
DISPATCHERS: Configures dispatcher processes in the shared server architecture.
MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.


Process

SQL>show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 3774873
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     2

SQL> select * from v$shared_server;
NAME PADDR    STATUS             MESSAGES      BYTES     BREAKS CIRCUIT        IDLE       BUSY   REQUESTS
---- -------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- ----------
S000 20919B5C WAIT(COMMON)              0          0          0 00           591233          0       0
S001 2091A14C WAIT(COMMON)              0          0          0 00           591233          0       0
SQL>

SQL> show parameter dispatcher
NAME                 TYPE        VALUE
-------------------- ----------- -------------------------
dispatchers          string   (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)
                              (PORT=1525))(DISPATCHERS=2)
max_dispatchers      integer
SQL>
SQL> select name,network from v$dispatcher;
NAME NETWORK
---- -------------------------------------------------------------
D000 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
D001 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))

Listener

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IBM-8553497657D)(PORT = 1525))
    )
  )
SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (DBGLOBAL_NAME = ORCL.WORLD)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = ORCL)
    )
  )


LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SubrataRoy)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date                06-JAN-2011 07:28:14
Uptime                    0 days 0 hr. 10 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SubrataRoy)(PORT=1525)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully




Connectivity

Tnsnames.ora
S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 9.118.64.31)(PORT = 1525))
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )
D =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 9.118.64.31)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


SQL> SELECT SID, SERIAL#, username, STATUS, SERVER FROM V$SESSION where username is not null;
SID    SERIAL# USERNAME                STATUS   SERVER
------ ------- --------                ------   -------
11        121  RENE                    INACTIVE DEDICATED
12        166  RENE                    INACTIVE DEDICATED
14          9  SYS                     ACTIVE   DEDICATED

C:> sqlplus rene/rene@S
SQL> SELECT name, status, accept, created, conf_indx, network FROM v$dispatcher;
NAME STATUS           ACC    CREATED  CONF_INDX NETWORK
---- ---------------- --- ---------- ---------- ------------------------------------------------------
D000 WAIT             NO           0          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
D001 WAIT             YES          1          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
C:> sqlplus rene/rene@S
SQL> SELECT name, status, accept, created, conf_indx, network FROM v$dispatcher;
NAME STATUS           ACC    CREATED  CONF_INDX NETWORK
---- ---------------- --- ---------- ---------- ------------------------------------------------------
D000 WAIT             NO           0          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
D001 WAIT             YES          2          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
C:> sqlplus rene/rene@S
SQL> SELECT name, status, accept, created, conf_indx, network FROM v$dispatcher;
NAME STATUS           ACC    CREATED  CONF_INDX NETWORK
---- ---------------- --- ---------- ---------- ------------------------------------------------------
D000 WAIT             NO           0          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
D001 WAIT             YES          3          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
C:> sqlplus rene/rene@S
SQL> SELECT name, status, accept, created, conf_indx, network FROM v$dispatcher;
NAME STATUS           ACC    CREATED  CONF_INDX NETWORK
---- ---------------- --- ---------- ---------- ------------------------------------------------------
D000 WAIT             NO           0          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
D001 WAIT             YES          4          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))

Listener

LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SubrataRoy)(PORT=1525)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SubrataRoy)(PORT=1525)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
    "D000" established:2 refused:0 current:0 max:972 state:ready      
         DISPATCHER        
         (ADDRESS=(PROTOCOL=tcp)(HOST=appdev)(PORT=47594))
The command completed successfully
LSNRCTL>


Connectivity

SQL> SELECT name, busy / (busy + idle) * 100 FROM v$dispatcher;
  NAME BUSY/(BUSY+IDLE)*100
  ---- --------------------
  D000           .000359889
  D001           .000405469
SQL> alter system shutdown 'D001';
System altered.
SQL>  SELECT name, status, accept, created, conf_indx, network FROM v$dispatcher;
NAME STATUS           ACC    CREATED  CONF_INDX NETWORK
---- ---------------- --- ---------- ---------- ------------------------------------------------------------
D000 WAIT             NO           0          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
D001 WAIT             NO           6          0 (ADDRESS=(PROTOCOL=TCP)(HOST=9.118.64.31)(PORT=1525))
C:\Documents and Settings\Administrator>sqlplus rene/rene@d
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 4 14:44:06 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12564: TNS:connection refused
Enter user-name:
  SQL> SELECT paddr, type, queued, DECODE(totalq,0,0,wait/totalq)
    2  FROM v$queue;
  PADDR    TYPE         QUEUED DECODE(TOTALQ,0,0,WAIT/TOTALQ)
  -------- ---------- -------- ------------------------------
  00       COMMON            0                     .009878904
  8008AC54 DISPATCHER        0                     3.38482683
  8008AF70 DISPATCHER        0                     .009591116


SQL> SELECT  d.name, s.username, c.status, c.queue
     FROM   v$circuit c, v$dispatcher d, v$session s
     WHERE  c.dispatcher = d.paddr AND c.saddr = s.saddr;
NAME USERNAME                       STATUS           QUEUE
---- ------------------------------ ---------------- ----------
D001 RENE                           NORMAL           NONE
D001 RENE                           NORMAL           NONE
D001 RENE                           NORMAL           NONE
 SQL> SELECT name, busy / (busy + idle) * 100
    FROM v$shared_server;
  NAME BUSY/(BUSY+IDLE)*100
  ---- --------------------
  S000            .00155527
  S001           .000669656


Memory Requirement

Consider an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.
If there are 5000 client connections, the memory used by each configuration is as follows:
Dedicated Server
Memory used = 5000 X (400 KB + 4 MB) = 22 GB
Shared Server
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA.
Database Resident Connection Pooling
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB

Monitor Shared server

View
Description
V$DISPATCHER
Provides information on the dispatcher processes, including name,
network address, status, various usage statistics, and index number.
V$DISPATCHER_CONFIG
Provides configuration information about the dispatchers.
V$DISPATCHER_RATE
Provides rate statistics for the dispatcher processes.
V$QUEUE
Contains information on the shared server message queues.
V$SHARED_SERVER
Contains information on the shared servers.
V$CIRCUIT
Contains information about virtual circuits, which are user connections to
the database through dispatchers and servers.
V$SHARED_SERVER_MONITOR
Contains information for tuning shared server.
V$SGA
Contains size information about various system global area (SGA) groups.
May be useful when tuning shared server.
V$SGASTAT
Contains detailed statistical information about the SGA, useful for tuning.
V$SHARED_POOL_RESERVED
Lists statistics to help tune the reserved pool and space within the
shared pool.