Theory Behind Connection Pooling in Oracle Shared Server (Oracle 11g R2)

In Oracle 11g R2, "connection pooling" is a technique used to optimize resource usage and manage database connections more efficiently in a "Shared Server" configuration. Connection pooling helps reduce the overhead of establishing new connections for each client request by reusing existing connections from a pool of server processes.
Here’s a breakdown of the key concepts behind connection pooling in the context of Oracle Shared Server:

1. Shared Server Architecture:
   • In the Shared Server (formerly Multi-Threaded Server or MTS) model, multiple client connections share a smaller number of server processes.
   • The server processes are shared across multiple client requests, which means that a pool of server processes is maintained rather than creating a separate dedicated server process for each client.
   • Clients are not directly connected to a dedicated server. Instead, the connection is routed through a dispatcher process, which passes the request to a shared server process.
2. Connection Pooling Mechanism:
   • Connection pooling in Oracle Shared Server works by keeping a pool of server processes ready to handle incoming client requests.
   • When a client connects to the database, it is assigned to an available shared server process from the pool via a dispatcher.
   • Instead of creating a new server process for every client request, a dispatcher routes the request to an idle server process from the pool, if available. This helps to minimize the time and resources needed to establish new connections.
3. Benefits of Connection Pooling:
   • Resource Efficiency: Connection pooling helps in reducing the overhead of creating and destroying server processes. It allows Oracle to handle many client connections with fewer processes, improving overall system efficiency.
   • Reduced Connection Latency: Reusing server processes from the pool means that the time spent establishing new connections is minimized, leading to reduced latency for client requests.
   • Scalability: Connection pooling allows Oracle databases to scale better by handling large numbers of client connections with a manageable number of server processes. It can easily scale to accommodate increased loads by adjusting the pool size.
   • Reduced Memory Usage: Since fewer server processes are created and maintained, memory usage is reduced. This is especially important in large-scale systems where many users need concurrent access to the database.
4. Oracle Shared Server with Connection Pooling:
   • Dispatchers: When a client requests a connection, the dispatcher listens for incoming requests and assigns them to a shared server process from the pool.
   • Server Processes: The shared server processes handle the actual work on behalf of clients. Multiple clients can use the same server process, but each client’s session is isolated by using session-specific information.
   • Session Management: The Oracle Shared Server uses session multiplexing, where multiple client sessions can share the same server process. This is possible because the server process can manage multiple clients through context switching. When a client finishes a request, the server process is returned to the pool and can be reused by another client.
5. Automatic Connection Pool Management:
   • In Oracle 11g R2, connection pooling is automatically managed by Oracle when Shared Server is enabled.
   • Initialization Parameters: Parameters like DISPATCHERS, MAX_DISPATCHERS, and MAX_SERVERS control the number of dispatchers and server processes, thus indirectly affecting the connection pool size.
   • Oracle’s connection pooling dynamically adjusts the size of the pool based on the workload, ensuring that there are enough server processes available to handle client requests without wasting resources.
6. Example Scenario:
   • Suppose an Oracle database is configured with 4 shared servers and 3 dispatchers. The database has 100 client connections, but since connection pooling is used, only a subset of those 100 clients will be actively connected to the shared servers at any given time.
   • If a new client arrives, a dispatcher will route the request to one of the idle shared server processes. When the client finishes its request, the server process will return to the pool, making it available for another client.
7. Configuration Considerations:
   • Pool Size: Administrators need to configure the right pool size based on the expected number of concurrent users and workload. The size of the connection pool (number of shared server processes) is typically adjusted using the MAX_SERVERS parameter.
   • Load Balancing: If multiple listeners are used, Oracle automatically balances the load across available dispatchers, ensuring efficient resource utilization.
   • Timeouts: If a server process in the pool is idle for too long, it may be released to free resources, depending on settings like SHARED_SERVER_TIME_OUT.

Conclusion: In Oracle 11g R2, connection pooling within the context of Shared Server is an essential technique to handle large numbers of concurrent client connections efficiently. By maintaining a pool of shared server processes and reusing them as needed, Oracle reduces the overhead of creating new connections, leading to better performance, scalability, and resource efficiency in large-scale environments.

1. Sharing or Pooling a Dispatcher's Set of Connections Among Multiple Client Processes: In Oracle's shared server configuration, dispatchers act as intermediaries between client processes and the shared server processes. Instead of each client having a dedicated server process, clients share dispatchers. When a dispatcher receives a request, it can queue it or immediately pass it to an available shared server process. This is essentially pooling at the dispatcher level, where multiple clients can share the same network connection to the database, optimizing resource usage.
2. Re-using Physical Connections (Making Them Available for Incoming Clients): This refers to the concept where once a client's session ends or goes idle, the physical connection isn't immediately closed but kept open in a pool. When a new client connects, instead of establishing a new connection, the server might reuse an existing connection from the pool. This reduces the overhead of creating new network connections, enhancing performance and scalability.
3. Maintaining a Logical Session with the Previous Idle Connection: This strategy can be seen as an extension of connection reuse but focuses on the session state. Oracle maintains session state information (like context, transaction state, etc.) for idle connections. When a new session starts, it might not just reuse the connection but also the session context if applicable, speeding up the process of starting a new session by avoiding the need to rebuild this state from scratch.

Important Notes: The term "multi-threaded server" is indeed an older term for what is now called "Oracle Shared Server". In modern Oracle versions, the terminology and some underlying mechanics have evolved, but the core principles remain:

• Dispatcher: Handles multiple client connections and routes their requests to shared server processes.
• Shared Server: Processes requests from multiple clients via dispatchers.
• Connection Pooling: Now often managed more dynamically through features like Fast Application Notification (FAN), Fast Connection Failover (FCF), or through application server connection pools (e.g., in Java with JDBC connection pools).

The concepts you've described are foundational to how Oracle manages connections in a shared server environment to optimize resource use, reduce overhead, and increase scalability. However, the implementation details and terminology have evolved with newer Oracle releases.

Location 1:	Maximum number of connections was configured to 266.
Location 2:	Client application has idled past the specified time and an incoming client requests a connection.
Location 3:	This client connection is the 266th connection into the server. Because connection pooling is turned on, this connection will be accepted.

By using a time-out mechanism to temporarily release transport connections that have been idle for a specified time period, connection pooling will "suspend" a previous connection and re-use the physical connection. When the idle client has more work to do, the physical connection is reestablished with the dispatcher. The next lesson discusses how to enable connection pooling.