Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Sunday, September 13, 2015

Configure the database instance to support shared server connections

Shared Server Architecture
In a shared server architecture, a dispatcher directs multiple incoming network session requests to a pool of shared server processes, eliminating the need for a dedicated server process for each connection. An idle shared server process from the pool picks up a request from a common queue.


Advantages:
  • Reduces the number of processes on the operating system
  • A small number of shared servers can perform the same amount of processing as many dedicated servers.
  • Reduces instance PGA memory
  • Every dedicated or shared server has a PGA. Fewer server processes means fewer PGAs and less process management.
  • Increases application scalability and the number of clients that can simultaneously connect to the database
  • May be faster than dedicated server when the rate of client connections and disconnections is high


Disadvantages:
Slower response time in some cases
incomplete feature support, and
increased complexity for setup and tuning.

As a general guideline, only use shared server when you have more concurrent connections to the database than the operating system can handle.


 
 
 
 
 
 
 
 
 
 
 
Dispatcher Processes (Dnnn)
The dispatcher processes enable client processes to share a limited number of server processes. You can create multiple dispatcher processes for a single database instance.

Dispatcher processes establish communication as follows:
  1. When an instance starts, the network listener process opens and establishes a communication pathway through which users connect to Oracle Database.
  2. Each dispatcher process gives the listener process an address at which the dispatcher listens for connection requests.
  3. At least one dispatcher process must be configured and started for each network protocol that the database clients will use.
  4. When a client process makes a connection request, the listener determines whether the client process should use a shared server process:
    • If the listener determines that a shared server process is required, then the listener returns the address of the dispatcher process that has the lightest load, and the client process connects to the dispatcher directly.
    • If the process cannot communicate with the dispatcher, or if the client process requests a dedicated server, then the listener creates a dedicated server and establishes an appropriate connection.


Restricted Operations of the Shared Server

Certain administrative activities cannot be performed while connected to a dispatcher process, including shutting down or starting an instance and media recovery. These activities are typically performed when connected with administrator privileges. To connect with administrator privileges in a system configured with shared servers, you must specify that you want to use a dedicated server process.

Enable Shared Server:
If SHARED_SERVERS is not included in the initialization parameter file at database startup, but DISPATCHERS is included and it specifies at least one dispatcher, shared server is enabled. In this case, the default for SHARED_SERVERS is 1.

If neither SHARED_SERVERS nor DISPATCHERS is included in the initialization file, you cannot start shared server after the instance is brought up by just altering the DISPATCHERS parameter. You must specifically alter SHARED_SERVERS to a nonzero value to start shared server.

Determining a Value for SHARED_SERVERS

The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you 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.

Configuring Dispatchers

The DISPATCHERS initialization parameter configures dispatcher processes in the shared server architecture. At least one dispatcher process is required for shared server to work.If you do not specify a dispatcher, but you enable shared server by setting SHARED_SERVER to a nonzero value, then by default Oracle Database creates one dispatcher for the TCP protocol.

DISPATCHERS Initialization Parameter Attributes



ADDRESS
Specify the network protocol address of the endpoint on which the dispatchers listen.
DESCRIPTION
Specify the network description of the endpoint on which the dispatchers listen, including the network protocol address. The syntax is as follows: (DESCRIPTION=(ADDRESS=...))
PROTOCOL
Specify the network protocol for which the dispatcher generates a listening endpoint. For example: (PROTOCOL=tcp)
DISPATCHERS
Specify the initial number of dispatchers to start.
CONNECTIONS
Specify the maximum number of network connections to allow for each dispatcher.
SESSIONS
Specify the maximum number of network sessions to allow for each dispatcher.
TICKS
Specify the duration of a TICK in seconds. A TICK is a unit of time in terms of which the connection pool timeout can be specified. Used for connection pooling.
LISTENER
Specify an alias name for the listeners with which the PMON process registers dispatcher information. Set the alias to a name that is resolved through a naming method.
MULTIPLEX
Used to enable the Oracle Connection Manager session multiplexing feature.
POOL
Used to enable connection pooling.
SERVICE
Specify the service names the dispatchers register with the listeners.

Determining the Number of Dispatchers

Once you 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 )
Monitor the following views to determine the load on the dispatcher processes:
  • V$QUEUE
  • V$DISPATCHER
  • V$DISPATCHER_RATE


Disabling Shared Server

You disable shared server by setting SHARED_SERVERS to 0.
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 = '';

Shared Server Data Dictionary Views



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.



Demo:

SHAIKDB>select name from v$database;

NAME
---------
SHAIKDB

SHAIKDB>show parameter shared_server

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers            integer
shared_server_sessions            integer
shared_servers                integer     1

SHAIKDB>show parameter dispatchers

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
dispatchers                string     (PROTOCOL=TCP) (SERVICE=SHAIKD
                       BXDB)
max_dispatchers             integer


SHAIKDB>

SHAIKDB>show parameter circuit

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
circuits                        integer

[oracle@collabn1 sf_stage]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 14-SEP-2015 00:14:03

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.


LSNRCTL> set current_listener SHAIKDB
Current Listener is SHAIKDB

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 1 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:2 refused:0
        LOCAL SERVER
The command completed successfully

SHAIKDB>set linesize 32000
SHAIKDB>set lines 100
SHAIKDB>col network for a50

SHAIKDB>select name,network,idle,busy,listener from v$dispatcher;

NAME NETWORK                             IDLE     BUSY    LISTENER
---- --------------------------------------------------            ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)     720608        0           0
    (PORT=31446))


SHAIKDB>select name,status,busy,circuit from v$shared_server;

NAME STATUS           BUSY CIRCUIT
---- ---------------- ---------- ----------------
S000 WAIT(COMMON)           0 00
SHAIKDB>alter system set dispatchers="(PROTOCOL=tcp)(DISPATCHERS=3)";

System altered.

SHAIKDB>show parameter dispatchers

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
dispatchers                string     (PROTOCOL=tcp)(DISPATCHERS=3)
max_dispatchers             integer


LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaikmeer" has 1 instance(s).
 Instance "SHAIKDB", status READY, has 4 handler(s) for this service...
    Handler(s):
     "D002" established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 19515>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=62261))
     "D001" established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 19513>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=38108))
     "D000" established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 29661>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=31446))
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
Service "SHAIKDB.shaiksameer" has 1 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0
        LOCAL SERVER
The command completed successfully




[oracle@collabn1 admin]$ vi tnsnames.ora

shared =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522))
    (CONNECT_DATA =
     (SERVER = shared)
     (SERVICE_NAME = SHAIKDB.shaiksameer)
    )
 )


[oracle@collabn1 admin]$ sqlplus test@shared

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 14 00:34:42 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options





LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 2 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0
        LOCAL SERVER
 Instance "SHAIKDB", status READY, has 4 handler(s) for this service...
    Handler(s):
     "D002" established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 23609>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=41339))
     "D001" established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 23607>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=38905))
     "D000" established:1 refused:0 current:1 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 23605>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=26328))
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
The command completed successfully


SHAIKDB>SET LINESIZE 32000
SHAIKDB>set lines 100
SHAIKDB>col network for a50

SHAIKDB>select name,network,idle,busy,listener from v$dispatcher;

NAME NETWORK                             IDLE     BUSY    LISTENER
---- -------------------------------------------------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)      20004        1           0
    (PORT=26328))

D001 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)      20004        0           0
    (PORT=38905))

D002 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)      20003        0           0
    (PORT=41339))


SHAIKDB>select name,status,busy,circuit from v$shared_server;

NAME STATUS           BUSY CIRCUIT
---- ---------------- ---------- ----------------
S000 WAIT(COMMON)           4 00



NAME NETWORK                             IDLE     BUSY    LISTENER
---- -------------------------------------------------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)      75378        1           0
    (PORT=26328))

D001 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)      75378        0           0
    (PORT=38905))

D002 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)      75377        0           0
    (PORT=41339))


SHAIKDB>select name,status,busy,circuit from v$shared_server;

NAME STATUS           BUSY CIRCUIT
---- ---------------- ---------- ----------------
S000 WAIT(COMMON)          11 00



SHAIKDB>col username for a10
SHAIKDB>col osuser for a10
SHAIKDB>col for machine for a15
SHAIKDB>col service_name for a20
SHAIKDB>set linesize 32000

SHAIKDB>select username,sid,serial#,osuser,machine,status,service_name,state from v$session  where username='TEST';


USERNAME      SID     SERIAL# OSUSER     MACHINE        STATUS   SERVICE_NAME      STATE
---------- ---------- ---------- ---------- --------------- -------- -------------------- -------------------
TEST           1           8 oracle     collabn1.shaiks INACTIVE SHAIKDB.SHAIKSAMEER  WAITING
                      ameer

Create 10 users and start making connections to the DB:

#!/bin/bash
ORACLE_SID=SHAIKDB
ORACLE_BASE=/u01/app/oracle
ORAENV_ASK=NO
ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD
export ORACLE_HOME ORACLE_SID ORACLE_BASE
for i in {1..10};
do
source /home/oracle/shaikdb.ora
#$ORACLE_HOME/bin/sqlplus -s test/test@shared<<EOF
$ORACLE_HOME/bin/sqlplus -s / as sysdba<<EOF
create user test$i identified by test$i;
grant create session to test$i;
EOF
done


#!/bin/bash
ORACLE_SID=SHAIKDB
ORACLE_BASE=/u01/app/oracle
ORAENV_ASK=NO
ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD
export ORACLE_HOME ORACLE_SID ORACLE_BASE
for i in {1..10};
do
source /home/oracle/shaikdb.ora
$ORACLE_HOME/bin/sqlplus -s test$i/test$i@shared<<EOF
select sysdate from dual;
EOF
done

SHAIKDB>select name,status,busy,circuit from v$shared_server;

NAME STATUS           BUSY CIRCUIT
---- ---------------- ---------- ----------------
S000 WAIT(COMMON)          84 00

SHAIKDB>select name,network,idle,busy,listener from v$dispatcher;

NAME NETWORK                             IDLE     BUSY    LISTENER
---- -------------------------------------------------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)     190305        5           0
    (PORT=26328))

D001 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)     190308        1           0
    (PORT=38905))

D002 (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)     190308        0           0
    (PORT=41339))


SHAIKDB>col network for a40
SHAIKDB>col listener for a50

SHAIKDB> select network,listener from v$dispatcher_config;

NETWORK                 LISTENER
---------------------------------------- --------------------------------------------------
(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))     (ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)
                   (port=1522)), (address=(protocol=tcp)(host=collabn
                   1.shaiksameer)(port=1523))



Reduce the # of dispatchers:

SHAIKDB>alter system set dispatchers="(PROTOCOL=TCP)(DISPATCHERS=1)";

System altered.

SHAIKDB>select name,status from v$dispatcher;

NAME STATUS
---- ----------------
D000 WAIT
D001 TERMINATE
D002 TERMINATE



SHAIKDB>/

NAME STATUS
---- ----------------
D000 WAIT


Kill a dispatcher:


SHAIKDB>alter system set dispatchers="(PROTOCOL=TCP)(DISPATCHERS=2)";

System altered.

SHAIKDB>select name,status from v$dispatcher;

NAME STATUS
---- ----------------
D000 WAIT
D001 WAIT


SHAIKDB>alter system shutdown immediate 'D001';

System altered.

SHAIKDB>select name,status from v$dispatcher;

NAME STATUS
---- ----------------
D000 WAIT
D001 TERMINATE


SHAIKDB>/

NAME STATUS
---- ----------------
D000 WAIT


LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 2 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0
        LOCAL SERVER
 Instance "SHAIKDB", status READY, has 2 handler(s) for this service...
    Handler(s):
     "D000" established:40 refused:0 current:1 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 23605>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=26328))
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
The command completed successfully



start some more user sessions:

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 2 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0
        LOCAL SERVER
 Instance "SHAIKDB", status READY, has 2 handler(s) for this service...
    Handler(s):
     "D000" established:60 refused:0 current:21 max:1022 state:ready
        DISPATCHER <machine: collabn1.shaiksameer, pid: 23605>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=26328))
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
The command completed successfully


SHAIKDB>select name,status,busy,circuit from v$shared_server;

NAME STATUS           BUSY CIRCUIT
---- ---------------- ---------- ----------------
S000 WAIT(COMMON)         100 00

SHAIKDB>select name,status from v$dispatcher;

NAME STATUS
---- ----------------
D000 WAIT

SHAIKDB>alter system shutdown immediate 'D000';

System altered.

SHAIKDB>select name,status from v$dispatcher;

NAME STATUS
---- ----------------
D000 TERMINATE



LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 2 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0
        LOCAL SERVER
 Instance "SHAIKDB", status READY, has 2 handler(s) for this service...
    Handler(s):
     "D000" established:60 refused:0 current:0 max:1022 state:blocked
        DISPATCHER <machine: collabn1.shaiksameer, pid: 23605>
        (ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=26328))
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
The command completed successfully


Now when I tried to make a connection using shared server:

[oracle@collabn1 sshaik]$ ./conn.sh
1
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server


No shared servers or dispatchers available:

SHAIKDB>select name,status from v$dispatcher;

no rows selected

SHAIKDB>select name,status,busy,circuit from v$shared_server;

no rows selected

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 2 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0
        LOCAL SERVER
 Instance "SHAIKDB", status READY, has 1 handler(s) for this service...
    Handler(s):
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
The command completed successfully

No comments: