In Oracle, Shared Server enables multiple users to use a single server process rather than each user having their own server process. This configuration is particularly useful in systems with a large number of users or environments that require high resource scalability. Shared Server optimizes system resources and helps reduce server load. However, if you have Application Servers that use Connection Pools, you might not need this configuration, as connection pooling can handle resource management.
Steps to Configure Shared Server
-
Configure Key Parameters
SHARED_SERVERS: This parameter specifies how many Shared Server Processes are initially active. The value should be set based on system load and the number of users.
SQL> show parameter shared_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_server_sessions integer
shared_servers integer 1
SQL> ALTER SYSTEM SET SHARED_SERVERS=5 SCOPE=BOTH;
System altered.
SQL> show parameter shared_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_server_sessions integer
shared_servers integer 5
DISPATCHERS: This parameter defines the number of Dispatcher processes that handle requests. Dispatchers send user requests to Shared Server processes.
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=orclXDB)
max_dispatchers integer
SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=2)' SCOPE=BOTH;
System altered.
Configure Maximum Parameters
MAX_SHARED_SERVERS: Maximum number of Shared Server Processes that can run on the system.
SQL> ALTER SYSTEM SET MAX_SHARED_SERVERS=20 SCOPE=BOTH;
System altered.
SHARED_SERVER_SESSIONS: Maximum number of sessions that can use the Shared Server.
SQL> ALTER SYSTEM SET SHARED_SERVER_SESSIONS=200 SCOPE=BOTH;
System altered.
How Shared Server, Dispatcher, and Queue Work
Shared Server uses Dispatchers and Queues to manage requests. Here’s how it works:
- Requests to Dispatcher: When clients connect, their requests go to Dispatchers, not directly to Shared Server processes. Dispatchers manage and distribute these requests, using Queues to store and queue them.
- Requests in the Request Queue: Dispatchers place each user’s request in the Request Queue, where it waits until a Shared Server process is available.
- Processing by Shared Server: Shared Server processes sequentially handle requests from the Request Queue.
- Response Back to Dispatcher: Once processed, responses go back to the Response Queue, and Dispatchers retrieve them to send to users.
Note: Dispatchers and Shared Server create indirect processes for requests and responses, improving resource efficiency.
Connecting as Shared from Clients
To connect clients like SQL*Plus and SQL Developer to Shared Server, configure TNS settings in the tnsnames.ora file:
Example of Shared TNS
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle9-19)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl_shared =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle9-19)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
)
)
The connection above is dedicated, while the one named orcl_shared is shared. Two simultaneous connections with the user c##vahid will reveal the difference:
SQL> select sid, serial#, username, server, status from v$session where username='C##VAHID';
SID SERIAL# USERNAME SERVER STATUS
---------- ---------- ------------------------------ --------- --------
2 56203 C##VAHID DEDICATED INACTIVE
26 37433 C##VAHID NONE INACTIVE
The SERVER type is displayed as NONE, which indicates SHARED.
Disabling Shared Server
To disable Shared Server, set SHARED_SERVERS to 0, which stops all Shared Server processes and shifts connections to Dedicated.
ALTER SYSTEM SET SHARED_SERVERS = 0 SCOPE=BOTH;
Killing a Session in Shared Server
In Shared Server, sessions use shared processes, so you can’t simply use KILL SESSION directly. Mark sessions for kill first.
Steps to Kill a Session
-
Identify
SIDandSERIAL#:
SELECT SID, SERIAL#, SERVER, STATUS, USERNAME
FROM V$SESSION
WHERE SERVER IN ('SHARED','NONE') AND USERNAME = 'C##VAHID';
Kill the session:
SQL> ALTER SYSTEM KILL SESSION '26,37433' IMMEDIATE;
-
Note: If the issue persists, consider checking the Dispatcher. Closing a Dispatcher process disconnects all connected sessions, but proceed cautiously.
Potential Library Cache Mutex Issues in Shared Server
Shared Server might cause Library Cache Mutex problems due to shared resources.
- Library Cache: Part of the Shared Pool used to store SQL code and execution plans.
- Mutexes: Lightweight locks to prevent simultaneous access to Library Cache.
Ways to Reduce Mutex Issues
- Optimize SQL Code: Reduce duplicate SQL code to minimize Mutex Waits.
- Increase Shared Pool Size: Provides more space for Library Cache.
- SQL Plan Management: Managing plans can reduce Mutex Waits.
- Dedicated Servers for Certain Users: For users needing long-term connections, assign a Dedicated Server.
Key Considerations for Application Servers and Connection Pool
Application Servers often use Connection Pools, which can interfere with Shared Server. Some points:
- Connection Lifespan: Shared Server suits short-term sessions, while Connection Pool maintains long-term connections, possibly leading to queuing delays.
- Connection Affinity: In Connection Pool, a connection is retained for a specific session, but in Shared Server, sessions are shared among users, which might reduce efficiency.
Conclusion
This article introduced you to configuring Shared Server, monitoring sessions, managing Mutex issues in the Library Cache, and optimizing them. Shared Server is effective for managing resources in multi-user environments, but it requires careful configuration to coordinate with Connection Pools and mitigate Mutex issues.