Configuration Analysis of Oracle Shared Server (Shared Server/MTS)

Shared Server under Oracle 9i (multi-thread Server MTS in 8i) It is mainly used in OLTP services. It takes only a short time for a server process to process the business, and most of them are in idle State. In this case, fewer resources can be used, process a large number of user requests.

Configuration of the Shared Server
1. Set the DISPATCHERS parameter:
   Add the following in pfile:

*. DISPATCHERS = '(PROTOCOL = TCP) (SERVICE = SKYSH) (DISPATCHERS = 2) (PROTOCOL = IPC) (DISPATCHERS = 1 )'

   After the above method is started, DISPATCHERThe ports are randomly allocated. To fix the port number of Each DISPATCHER, use the following method:
   Use different ports:

*. DISPATCHERS = '(ADDRESS = (PROTOCOL = TCP) (PORT = 5000) (DISPATCHERS = 1)', '(ADDRESS = (PROTOCOL = TCP) (PORT = 5001 )) (DISPATCHERS = 1 )'

   Use the same port:

*. Dispatchers = '(ADDRESS = (PROTOCOL = TCP) (PORT = 5130) (SERVICE = SKYSHR) (DISPATCHERS = 1 )'

   If spfile is enabled, create pfile from spfile first. After modification, create spfile from pfile to create spfile.
   Note: Although DISPATCHERS is a dynamic parameter, you can only use alter system set DISPATCHERS to temporarily add and delete dispatcher. After restart, the original value is restored and the scope parameter cannot be used.
   Here, the SERVICE = SKYSH parameter can be left unspecified. If not specified, You need to specify the service_names and instance_name initial parameters. When the instance is started, PMON dynamically binds the value specified by SERVICE or service_names to LISTENER and generates dispatchers.
   DISPATCHERS = 2 if not specified, the default value is 1.
   PROTOCOL: when using the Shared Server to connect, you must use Oracle Net Services. Even if the client and database are on the same host, if the client and database are on Windows NT, dispatchers can only use TCP/IP.Protocol.

2. Set the client tnsnames. ora file:

SKY3 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.123) (PORT = 1521 ))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = SKYSH.heysky.net)
          (SERVER = SHARED)
      )
  )

   Here, the value in SERVICE_NAME = SKYSH.heysky.net must be a dynamic state of service_name.db_domain, otherwise it will generate a ORA-12523 error:

ERROR:
ORA-12523: TNS: listener cocould not find instance appropriate for the client connection

   To connect to a shared server, you must specify (SERVER = SHARED). If not specified, the connection is used as a DEDICATE connection.

3. SHARED_SERVERS parameters:
   This parameter specifies the number of initially generated Shared Server processes when the instance is started, which must be greater than 0 if you want to use a Shared Server; otherwise, a ORA-12520 error is generated:

ERROR:
ORA-12520: TNS: listener cocould not find available handler for requested type of server

   By dynamically changing this parameter, you can increase or decrease the number of default shared server processes, or disable shared servers without stopping the database. When it is set to 0 dynamically, the sharing server process stops after it completes all the work and is idle for a period of time. You can view the status of the Sharing server in the v $ shared_server dynamic view.

4. Other related parameters:
   MAX_DISPATCHERS: Maximum number of DISPATCHER instances. The default value is 5. If the sum of all DISPATCHER quantities set in DISPATCHERS is greater than 5, MAX_DISPATCHERS is equal to this number.

   MAX_SHARED_SERVERS: Process of the largest Shared Server. When the initial allocated SHARED_SERVERS is insufficient, Oracle will continue to generate a shared server until the maximum value is reached. When there is no idle space, Oracle will terminate idle processes, until the value specified in SHARED_SERVERS.

   CIRCUITS: The change value is generally equal to the number of SESSIONS.

   SHARED_SERVER_SESSIONS: The maximum SESSION value that can be used by the shared server connection. Generally, it can be set to a value smaller than CIRCUITS and SESSIONS-5 to leave a certain number of SESSIONS for the DEDICATE connection.

5. Some precautions:
   When sysdba is connected to a shared server, start up, shut down, orThe recovery command. Otherwise, the following error may occur:

ORA-00106: cannot startup/shutdown database when connected to a dispatcher

   When using a shared server, you need to set the LARGE_POOL_SIZE parameter, which is sufficient because when using a shared server, the cursor state and User session data information in PGA will be stored in the large pool. If LARGE_POOL_SIZE is not set, it will be placed in the share pool, which will cause fragmentation of the share pool and affect performance.

6. Some Dynamic views:
   V $ CIRCUIT
   V $ SHARED_SERVER
   V $ DISPATCHER
   V $ SHARED_SERVER_MONITOR
   V $ QUEUE
   V $ SESSION

Example: how to connect to the database

SQL> select sid from v $ mystat where rownum = 1;

      SID
----------
        9

SQL> select server from v $ session
 2Where sid = 9;

SERVER
---------
SHARED

7. View listener information:
   When Shared Server is used, when the Instance is started, PMON dynamically binds the service_names parameter or the value set by the SERVICE parameter in DISPATCHERS to the LISTENER, and assigns the corresponding DISPATCHER to the SERVICE, this information can be obtained through the LSNRCTL command:

C: \ Documents ents and Settings \ Administrator> lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.3.0-Production on 17-MAY-2006 23:22:58

Copyright (c) 1991,200 2, Oracle Corporation.All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = heysky) (PORT = 1521 )))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                 TNSLSNR for 32-bit Windows: Version 9.2.0.3.0-Produc
Tion
Start Date              17-MAY-2006 21:41:12
Uptime                  0 days 1 hr. 46 min. 13 sec
Trace Level             Off
Security                OFF
SNMP                    OFF
Listener Parameter File E: \ oracle \ ora92 \ network \ admin \ listener. ora
Listener Log File       E: \ oracle \ ora92 \ network \ log \ listener. log
Listening Endpoints Summary...
 (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = heysky) (PORT = 1521 )))
 (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (PIPENAME = \. \ pipe \ EXTPROC0ipc )))
Services Summary...
Service "PLSExtProc" has 1 instance (s ).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...
Service "SKYSHARED.heysky.net" has 1 instance (s ).
 Instance "sky", status READY, has 4 handler (s) for this service...
Service "SKYTEST" has 1 instance (s ).
 Instance "SKY", status UNKNOWN, has 1 handler (s) for this service...
The command completed successfully
LSNRCTL> service
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = heysky) (PORT = 1521 )))
Services Summary...
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
Service "SKYSHARED.heysky.net" has 1 instance (s ).
 Instance "sky", status READY, has 4 handler (s) for this service...
   Handler (s ):
     "DEDICATED" established: 0 refused: 0 state: ready
        LOCAL SERVER
     "D002" established: 0 refused: 0 current: 0 max: 1002 state: ready
        DISPATCHER <machine: HEYSKY, pid: 3600>
        (ADDRESS = (PROTOCOL = tcp) (HOST = heysky) (PORT = 2312 ))
     "D001" established: 0 refused: 0 current: 0 max: 1002 state: ready
        DISPATCHER <machine: HEYSKY, pid: 2748>
        (ADDRESS = (PROTOCOL = tcp) (HOST = heysky) (PORT = 2311 ))
     "D000" established: 0 refused: 0 current: 0 max: 1002 state: ready
        DISPATCHER <machine: HEYSKY, pid: 684>
        (ADDRESS = (PROTOCOL = tcp) (HOST = heysky) (PORT = 2309 ))
Service "SKYTEST" has 1 instance (s ).
 Instance "SKY", status UNKNOWN, has 1 handler (s) for this service...
   Handler (s ):
     "DEDICATED" established: 0 refused: 0
        LOCAL SERVER
The command completed successfully

      If LISTENER is enabled, any dynamic changes to the database, for example, DISPATCHERS and SERVICE_NAMES are dynamically bound to LISTENER. You can see these changes from LSNRCTL. If LISTENER is not started, start LISTENER again after the changes, these changes will not be bound to the LISTENER. Therefore, it is generally recommended that you first start LISTENER and then the database to enable dynamic bonding.