Configuration Analysis of Oracle Shared Server (SharedServer/MTS)

The Shared Server SharedServer under Oracle9i (the multi-thread server MTS in 8i) is mainly used in the OLTP business, and each server process needs to process the business very short.

The Shared Server under Oracle 9i (the multi-thread Server MTS in 8i) is mainly used in the OLTP business, and each Server process needs to process the business very short.

The Shared Server (MTS in 8i) under Oracle 9i is mainly used in the OLTP service. It takes only a short time for the Server process to process the service each time and is mostly idle, in this case, a small amount of resources can be used to 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 preceding method is started, the DISPATCHER port is randomly allocated. To fix the port 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 is on Windows NT, dispatchers can only use the 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, the start up, shut down, or recovery command cannot be executed. 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
2 where 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
(ADDRESS = (PROTOCOL = tcp) (HOST = heysky) (PORT = 2312 ))
"D001" established: 0 refused: 0 current: 0 max: 1002 state: ready
DISPATCHER
(ADDRESS = (PROTOCOL = tcp) (HOST = heysky) (PORT = 2311 ))
"D000" established: 0 refused: 0 current: 0 max: 1002 state: ready
DISPATCHER
(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 the LISTENER is started, any dynamic changes to the database, for example, DISPATCHERS and SERVICE_NAMES are dynamically bound to the LISTENER. You can see these changes from LSNRCTL. If the LISTENER is not started, start the LISTENER 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.