Shared-server configuration is useful only if the intended application connection actually uses it. Follow the complete path: bound the server pool and shared connections, inspect the registered dispatcher, open a fresh connection, and then inspect that session's server type.
Oracle DBA Lesson 42 Part 1 — Enable and Verify Shared Server
Scope
The administrator needs ALTER SYSTEM in the root and appropriate access to the diagnostic views. In a CDB, use a common administrator with the necessary common privilege for instance-wide changes. Check container visibility when you inspect PDB application sessions. The target PDB and service must be open and registered.
Before changing anything, record the current settings, including all dispatcher configuration strings, not only a shortened display:
SHOW CON_NAME
SHOW PARAMETER dispatchers
SHOW PARAMETER shared_servers
SHOW PARAMETER max_shared_servers
SHOW PARAMETER shared_server_sessions
SHOW PARAMETER circuits
SHOW PARAMETER processes
SHOW PARAMETER sessions
SHOW PARAMETER large_pool_size
SELECT name, value, ordinal
FROM v$system_parameter2
WHERE name IN ('dispatchers','shared_servers','max_shared_servers',
'shared_server_sessions','circuits')
ORDER BY name, ordinal;
Work in CDB$ROOT for the instance pool. Capture the memory settings and check available SGA capacity. Shared-session UGA is in the SGA, in the large pool when it is configured, or otherwise in the shared pool. The large pool and the shared pool are SGA components. A process count alone does not reserve all required session memory, and it does not prove there is room for additional sessions. These are memory allocations. The illustrated pool change creates no disk file.
LARGE_POOL_SIZE=0 alone does not prove that an automatically managed large pool currently has no allocation. Inspect the current allocation and the memory-management configuration. This exercise does not change the SGA budget. Verify that PROCESSES supports the illustrated pool plus all other required processes, and that SESSIONS leaves room for administrative and dedicated sessions and for the four shared sessions.
1. Bound the server pool
ALTER SYSTEM SET max_shared_servers=3 SCOPE=MEMORY;
ALTER SYSTEM SET shared_servers=2 SCOPE=MEMORY;
| Parameter | Meaning in this example |
|---|---|
SHARED_SERVERS=2 | Initial/minimum number of shared server processes maintained by Oracle |
MAX_SHARED_SERVERS=3 | Maximum for automatic shared-server growth, provided the minimum does not exceed it |
Set the maximum before raising the minimum. SHARED_SERVERS takes precedence if it exceeds MAX_SHARED_SERVERS. Keep the values consistent. Setting the maximum does not prove that a third server is running. Oracle can grow the pool as needed within applicable resource limits.
A positive SHARED_SERVERS value enables shared server. If no dispatcher configuration exists, Oracle can start a default TCP dispatcher. The explicit dispatcher setting below defines the intended configuration. It is not a requirement to restart the instance. See SHARED_SERVERS and MAX_SHARED_SERVERS. See managing processes for shared-server configuration, dispatcher changes, and shutdown.
2. Bound shared connections
ALTER SYSTEM SET shared_server_sessions=4 SCOPE=MEMORY;
ALTER SYSTEM SET circuits=4 SCOPE=MEMORY;
SHARED_SERVER_SESSIONS limits concurrent shared-server user sessions. CIRCUITS limits virtual circuits, the communication state structures used for shared connections. In the ordinary direct-client example, a shared connection has a virtual circuit. Do not generalize this small example into a universal equality between every session, circuit, and physical network connection in every architecture.
These limits serve different purposes from the shared-server process counts. A minimum of two servers does not mean only two users can connect, and a four-session limit does not reserve four new dedicated server processes. Session and SGA resources must also be available. None of these settings is a workload performance guarantee. See SHARED_SERVER_SESSIONS and CIRCUITS.
3. Define the dispatcher
ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)(DISPATCHERS=1)'
SCOPE=MEMORY;
This defines one TCP dispatcher for the example. DISPATCHERS is a configuration string. Existing configurations can contain service, listener, address, and other attributes. Changes without an INDEX can modify a matching configuration or add a new configuration when no match exists. Do not assume this statement safely replaces every existing dispatcher configuration. Review all strings and dependencies first.
SCOPE=MEMORY changes the running instance's setting immediately and lasts until shutdown. It does not update the saved startup parameter file. This temporary scope still changes the current instance and can affect other users.
The instance pool is configured in the root. For a PDB, SHARED_SERVERS=0 can disable shared-server connections. Resetting the PDB setting restores inherited behavior. Do not attempt to size a separate shared-server pool by setting a positive value inside a PDB. See DISPATCHERS and ALTER SYSTEM for privileges, scope, and registration. SHARED_SERVERS documents the container scope.
4. Inspect the running pool
SELECT name, status
FROM v$shared_server
ORDER BY name;
| NAME | STATUS |
|---|---|
| S000 | WAIT (COMMON) |
| S001 | WAIT (COMMON) |
Two rows indicate two shared-server processes in this example. WAIT (COMMON) means idle, waiting for a user request. It is an ordinary ready-to-work state, not a failure. Actual names, counts, and states can change with work and automatic growth. Verify the effective parameter values as well as the view rows. See V$SHARED_SERVER.
5. Verify registration and dispatcher handling
From the dedicated administrator connection:
ALTER SYSTEM REGISTER;
On the database host, using the Oracle home and listener name serving the client's address:
lsnrctl services LISTENER
Registration requests immediate listener registration. It does not, by itself, fix an incorrect listener address, service configuration, or registration failure. In the orders service section, inspect a dispatcher handler, its readiness, and its address: D000, state:ready, DISPATCHER, and the dispatcher address.
A DEDICATED handler by itself cannot satisfy an explicit SERVER=SHARED request. Shared handling needs an available dispatcher for that service. A failed shared request is not evidence that the pool must be enlarged. First verify the requested service, the registered handler, and the client configuration. Queue and capacity diagnosis is Part 02. See listener services inspection.
6. Open a fresh shared connection
Complete alias in the client tnsnames.ora:
ORDERS_SHARED =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orders)
(SERVER = SHARED)
)
)
The video's CONNECT_DATA fragment is only the inner part of this alias. Start a new client connection:
sqlplus -L app_user@ORDERS_SHARED
Enter the password when prompted. The listener directs the new connection to an available dispatcher. An explicit shared request with no available dispatcher is rejected. Do not assume a dedicated fallback. The client sqlnet.ora setting USE_DEDICATED_SERVER=on can override a SERVER entry to dedicated, so inspect the configuration actually used by this client if the result is unexpected.
Existing dedicated sessions keep their original type. Changing SHARED_SERVERS or editing an alias does not convert a currently connected dedicated session. See configuring a shared server architecture.
7. Verify the actual session
From the administrator connection, with visibility to the intended application sessions:
SELECT sid, serial#, username, server, service_name
FROM v$session
WHERE username = 'APP_USER'
AND service_name = 'orders'
ORDER BY sid;
| SID | SERIAL# | USERNAME | SERVER | SERVICE_NAME |
|---|---|---|---|---|
| 41 | 872 | APP_USER | DEDICATED | orders |
| 58 | 614 | APP_USER | SHARED | orders |
Session 58 is the newly opened shared connection. Session 41 is an earlier dedicated connection. Match the new connection to its real SID and SERIAL# before judging success. Compare before and after rows with only one client added, or obtain its identity through a session-identification method. Multiple sessions can use the same account and service, and SIDs can be reused after disconnection.
A listener handler or a configured parameter alone does not prove the application's actual session type. See V$SESSION.
No comments:
Post a Comment