SIGN UP MEMBER LOGIN:
   
ARTICLE

How to set shared server mode in Oracle

Posted by Sapna Malik Articles | DB Administration December 07, 2010
In this article, we will learn how to set shared server mode in Oracle.
Reader Level:

Shared Server Processes

The shared server process is one that handles user requests. The shared server process is a database server configured to allow many user processes to share a very limited number of server processes. The shared server processes can be either a dedicated server process, where one server process services only one user process, or if your database server is configured for shared server, it can be a shared server process, where a server process can service multiple user processes.

1. We will modify the initialization parameter file, add the following content or modify the spfile alter system command.


Example with explain:


DISPATCHERS = "(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521)" OR


DISPATCHERS = "(PROTOCOL= TCP) (serv = sapdata) (list = sapdata) (poo = on) (disp = 5) (conn = 33) (sess = 224)" PROTOCOL= tcp :- Agreement
list = sapdata :- Listener alias, not the listener's name, is actually a network service name in tnsnames.ora.
serv = sapdata :- Web service name.
poo = on :- Whether to enable the shared pool.
disp = 5 :- Number of initial scheduling process. 
sess = 224 :- The maximum sessions for each scheduling process.


SHARED_SERVERS = 2 :-The number of initial shared server processes, set to 0 that closes a shared server mode. 
CIRCUITS = 10 :- Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
SHARED_SERVER_SESSIONS = 10 :- The amount of user sessions.
MAX_DISPATCHERS = 3 :- The largest number of scheduling process.
MAX_SHARED_SERVERS = 5 :- The maximum number of shared server process.

All parameter explain of shared server in this table:

Parameter

Description

V$SHARED_SERVER

Contains information on the shared server processes.

V$MAX_SHARED_SERVER

The MAX_SHARED_SERVERS parameter sets the maximum number of Oracle Shared Servers that can be running concurrently.

V$SHARED_SERVER_SESSION

The amount of user sessions.

V$CIRCUIT

Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.

V$DISPATCHER

Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.

V$MAX_DISPATCHER

The maximal number of concurrent dispatcher processes.


2. If you modify the pfile need to restart the database, if the changes do not restart the spfile.

3. See whether the entry into force of the parameters set.


SQL> Show parameter shared_servers;

SQL> Show parameter disp;

SQL> Select * from v$shared_server;


Output:


SharedServer1.png

SQL> Select * from v$dispatcher;


Output:


SharedServer2.png

4. After the success of the default connection settings for the shared connection.


SQL> Select server from v$session;


Output:

SharedServer3.png


5. When the implementation of some of the management command is required to be dedicated oracle server connection, We can still specify the connection.

Modify the tnsnames.ora file, add the connect_data node (server = dedicated).


6. Modify the number of shared server process


ALTER SYSTEM SET SHARED_SERVERS = NEW_NUMBER;


SQL> ALTER SYSTEM SET SHARED_SERVERS = 2;


7. Modify the number of scheduling process.


SQL> ALTER SYSTEM
2 SET DISPATCHERS =
3 '(PROTOCOL=TCP)(DISPATCHERS=5) (INDEX=0)',
4 '(PROTOCOL=TCPS)(DISPATCHERS=2) (INDEX=1)';


8. We will close the specified scheduling process.


SQL> SELECT NAME, NETWORK FROM V$DISPATCHER;


Output:

SharedServer4.png


Alter system shutdown immediate 'Dispatcher_Name';


SQL> Alter system shutdown immediate 'D006';


SQL> SELECT NAME, NETWORK FROM V$DISPATCHER;


Output:


SharedServer6.png

9. The server from the shared server mode to dedicated server mode.


The sharing of existing process needs to wait until the end of the session to end. 


ALTER SYSTEM SET SHARED_SERVERS = 0 scope = both;


OR


An immediate end to all shared server processes mandatory. 


ALTER SYSTEM SET MAX_SHARED_SERVERS = 0 scope = both;


Output:

SharedServer5.png

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING