Identify the Dispatcher Parameters used with Oracle

The Oracle Multi-Threaded Server (MTS) was renamed to "Shared Server" starting with Oracle 9i. This change reflects enhancements made to the architecture, but the core functionality remains available under the new name. While the term "MTS" is deprecated, the multi-threaded dispatching capabilities continue to be supported in Oracle's database systems as "Shared Server."
To configure the "Oracle Shared Server" (previously known as Multi-Threaded Server, or MTS), the init.orainitialization parameters need to include several "dispatch parameters". These are used to define how the server processes client connections in a shared server environment.
Key Parameters for Shared Server Configuration:

1. `DISPATCHERS`
   • This parameter specifies the number and attributes of dispatcher processes. It has a complex syntax that allows setting the protocol, network address, and other attributes.
   • Example:

     DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"
             

   • This creates 3 dispatchers for the TCP protocol.
2. `MAX_DISPATCHERS`
   • Specifies the maximum number of dispatcher processes that can be created.
   • Example:

     MAX_DISPATCHERS = 10
             

3. `SHARED_SERVERS`
   • Specifies the initial number of shared server processes to start when the database instance is started.
   • Example:

     SHARED_SERVERS = 5
             

4. `MAX_SHARED_SERVERS`
   • Specifies the maximum number of shared server processes that can run simultaneously.
   • Example:

     MAX_SHARED_SERVERS = 20
             

5. `CIRCUITS`
   • Specifies the maximum number of virtual circuits that can be simultaneously created.
   • Example:

     CIRCUITS = 100
             

6. `SHARED_SERVER_SESSIONS`
   • Specifies the total number of shared server user sessions that can be created.
   • Example:

     SHARED_SERVER_SESSIONS = 50
             

Optional Parameters

• `SERVICE_NAMES`
  • Specifies the service names used by the instance. Dispatchers can reference these names for connecting clients.
  • Example:

    SERVICE_NAMES = "mydb"
            

• `LISTENER_NETWORKS`
  • Used to specify which listeners the dispatcher processes register with.
  • Example:

    LISTENER_NETWORKS = "(NAME=listener1)(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521))"
            

Summary
These parameters should be included in the `init.ora` (or `spfile`) file to initialize and optimize the Oracle Shared Server setup based on workload requirements. After setting these, you should restart the database instance for the changes to take effect.

When users connect to the database via Shared Server, Oracle needs to allocate additional space in the shared pool for storing information about the connections between the user processes, dispatchers, and servers. For each user who will connect via Shared Server, you must add 1KB to the setting of the parameter shared_pool_size.
In Oracle databases, the Shared Server architecture requires additional memory in the "shared pool" for connection management. Each user connection via Shared Server consumes a certain amount of memory in the shared pool for storing information about the connection, including session information, dispatcher queues, and other metadata.
The specific amount of memory needed per user connection can vary based on factors such as:

1. Database version: Different Oracle versions may have slightly different memory requirements.
2. Workload: Heavier workloads or more complex session states may require more memory.
3. Configuration: Additional parameters or features, such as large dispatcher queues, can impact memory usage.

General Estimate

• Memory per connection: Typically, each connection via Shared Server consumes approximately 10 KB to 15 KB of memory in the shared pool.
• If you have for example, 1,000 users connecting via Shared Server, the additional memory required would be approximately:

  $$1000\times 15 \text{KB}=\frac{15000}{1000} \text{KB}=15 \text{MB}$$

How to Calculate Additional `shared_pool_size`

1. Estimate the number of concurrent Shared Server connections:
   • If you expect 1,000 users, calculate the total memory as:

     Total memory = Concurrent connections × Memory per connection

2. Add this memory to your existing shared_pool_size setting:
   • For example, if the current shared_pool_size is 500 MB, and you need an additional 15 MB, set:

     New shared_pool_size = 500 MB + 15 MB = 515 MB

Guidelines

1. Monitor usage: Use V$SGASTAT and V$SHARED_SERVER views to monitor memory usage and ensure sufficient space is allocated.
2. Allow headroom: Add a buffer (e.g., an extra 10-20%) to the shared_pool_size to handle unexpected spikes or additional features.
3. Adjust dynamically: If you are unsure about the exact requirements, start with an estimate and monitor memory usage after users start connecting. Adjust the shared_pool_size parameter as needed.

Example SQL to Check Current Shared Pool Usage

SELECT
    name,
    bytes / 1024 / 1024 AS size_in_mb
FROM
    v$sgastat
WHERE
    pool = 'shared pool';

This will give you an idea of the current shared pool allocation and usage.

---

Specify address of listener
The Shared Server_listener_address parameter specifies the address of the listener and shows each port to which the database may connect. For example, in the following syntax, addr is an address at which the listener will listen for connection requests for a specific protocol.

Shared Server_LISTENER_ADDRESS = "(addr)"

View the code below to see multiple addresses contained in the init.ora file.

dilbert:[/u/oracle] > ps -ef | grep ora_d0

  oracle 19236     1   0 07:42:15      -  0:00 ora_d001_fred 
  oracle 29520     1   0 07:42:15      -  0:00 ora_d000_fred 
  oracle 30084 32034   1 07:47:09  pts/7  0:00 grep ora_d0 
  oracle 33642     1   0 07:42:15      -  0:00 ora_d002_fred

Each address specified in the database's parameter file must also be specified in the corresponding listener's configuration file. You specify addresses differently for various network protocols.

You use the Shared Server_service parameter to specify the name of the service associated with dispatchers. A user requests Shared Server by specifying this service name in the connect string. A service name must be unique; if possible, use the instance's SID (system identifier). For example, if the dispatcher's service name were fred, you would set the Shared Server_service parameter as follows:
Shared Server-service

Shared Server_SERVICE = "fred"

Once the Shared Server_service has been defined, you can specify a connect string for connecting to this dispatcher:

SQLPLUS system/manager@\
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(NODE=dilbert)\
    (OBJECT=outa))(CONNECT_DATA=(SID=fred)))

If you do not set the Shared Server_SERVICE parameter, its value defaults to the DB_NAME parameter. If DB_NAME is also not set, Oracle returns the error ORA-00114, "missing value for system parameter Shared Server_service," when you start the database.
The next lesson investigates the remaining dispatch parameters.