Network Config   «Prev  Next»

Lesson 7Connection pooling using Shared Server in Oracle 19c
Objective Enable Connection Pooling.

Enable "connection pooling" in Oracle 19c when configuring the Oracle Shared Server

Enabling "connection pooling" in Oracle 19c while configuring the Oracle Shared Server involves setting up the database to reuse physical server processes efficiently. Connection pooling in a shared server environment helps optimize resource usage by allowing idle connections to be dropped and reassigned dynamically.
Here’s how you can enable and configure "connection pooling" in Oracle 19c:
Steps to Enable Connection Pooling
  1. Verify the Shared Server Environment
    • Ensure the Oracle Shared Server is enabled and configured correctly.
    • Check if the database is using a shared server configuration by verifying parameters like DISPATCHERS and SHARED_SERVERS.
    • SHOW PARAMETER DISPATCHERS;
SHOW PARAMETER SHARED_SERVERS;
    • If these parameters are not set, you can configure the Shared Server environment as follows:
    • ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)';
ALTER SYSTEM SET SHARED_SERVERS = 5;
    • These commands configure 3 dispatchers and 5 shared server processes.
  2. Enable Connection Pooling
    • Connection pooling is controlled by the SHARED_SERVER_SESSIONS and MAX_SHARED_SERVERS parameters.
    • To enable connection pooling, set the ENABLE_SHARED_SERVER_POOLING parameter in the database:
    • ALTER SYSTEM SET ENABLE_SHARED_SERVER_POOLING = TRUE;
    • Note: The ENABLE_SHARED_SERVER_POOLING parameter allows Oracle to dynamically manage connection pooling in the shared server.
  3. Set Connection Pool Parameters
    • Configure additional parameters that affect connection pooling performance:
      • MAX_SHARED_SERVERS: Sets the maximum number of shared server processes that can be started.
      • SHARED_SERVER_SESSIONS: Specifies the maximum number of concurrent shared server sessions.
      • DISPATCHERS: Configures the number of dispatchers to handle client connections.
    • Example:
    • ALTER SYSTEM SET MAX_SHARED_SERVERS = 20;
ALTER SYSTEM SET SHARED_SERVER_SESSIONS = 100;
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=5)';
  4. Set Idle Timeout for Connections
    • Use the SHARED_SERVER_IDLE_TIME parameter to define the maximum idle time for a pooled connection.
    • This parameter ensures that idle connections are returned to the pool, optimizing server resource usage.
    • ALTER SYSTEM SET SHARED_SERVER_IDLE_TIME = 300; -- 300 seconds (5 minutes)
  5. Monitor Connection Pooling
    • You can monitor connection pooling statistics using the following views:
      • V$SHARED_SERVER: Provides information about shared server processes.
      • V$QUEUE: Shows the number of requests in the queue for shared servers and dispatchers.
      • V$DISPATCHER: Displays statistics about dispatcher processes.
    • Example:
    • SELECT * FROM V$SHARED_SERVER;
SELECT * FROM V$DISPATCHER;
  6. Restart the Database
    • After making the changes, restart the database to apply the configuration.
    • SHUTDOWN IMMEDIATE;
STARTUP;

Best Practices
  • Adjust Dispatchers and Shared Servers: Configure an appropriate number of dispatchers and shared servers based on expected user workload.
  • Monitor and Tune: Regularly monitor connection pooling performance and adjust parameters as needed.
  • Resource Management: Ensure the server has sufficient CPU and memory to handle the pooled connections efficiently.

By following these steps, you can enable and configure connection pooling in Oracle 19c, improving scalability and optimizing resource usage in your Oracle Shared Server environment.

Enable Oracle Connection Pooling

In Oracle 19c, the `MTS_DISPATCHERS` parameter has been replaced with `DISPATCHERS` as part of the transition from Multi-Threaded Server (MTS) to the more modern architecture of Shared Server processes. Configurations for Oracle 19c
  1. Configuration 1: 
    DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"

    Connection pooling functionality is now managed differently in Oracle 19c.
  2. Configuration 2: 
    DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"

    The `POOL=ON` functionality is not explicitly required; connection pooling can be managed using the `SHARED_SERVER_SESSIONS` or Oracle Database Resident Connection Pooling (DRCP) if needed.
  3. Configuration 3: 
    DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"

    Connection handling is optimized internally by the database.*
  4. Configuration 4: 
    DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"

    Outbound connections are managed more efficiently by the database in newer versions.*

Notes for Oracle 19c
  1. Connection Pooling in Oracle 19c: If you need explicit connection pooling, use Oracle Database Resident Connection Pooling (DRCP), which is a separate feature and does not require configuration through the DISPATCHERS parameter.
  2. Managing Shared Server Processes: Additional shared server parameters like SHARED_SERVERS and MAX_SHARED_SERVERS can be used to fine-tune the behavior of shared server processes.
  3. Transition Recommendation: Oracle 19c has improved internal connection management mechanisms. Most of the legacy parameters like POOL are either no longer applicable or unnecessary.

Secure Configuration Checklist

In real-world situations with Oracle 19c, how often is "connection pooling" used when the database server is overwhelmed with incoming network requests?
Connection pooling is a widely used strategy in real-world Oracle 19c environments, especially when a database server is overwhelmed with incoming network requests. Here are the key reasons why and how it is implemented:
Why Connection Pooling is Used
  1. Performance Optimization: Establishing and tearing down database connections is resource-intensive. Connection pooling minimizes this overhead by reusing established connections for multiple client requests.
  2. Resource Efficiency: Each database connection consumes server resources, including memory and CPU. Connection pooling limits the maximum number of connections, helping to prevent resource exhaustion.
  3. Scalability: In high-traffic environments, connection pooling allows applications to scale better by managing connections efficiently.
  4. Reduced Latency: Connection reuse reduces latency for clients, as the time to establish a new connection is avoided.

How Often It Is Used In environments where the database server experiences high volumes of incoming network requests, connection pooling is almost always used. It is particularly prevalent in the following scenarios:
  • Web Applications: Applications with thousands of concurrent users, such as e-commerce platforms.
  • Microservices: Systems with multiple microservices communicating with the same database, where each service can generate numerous connection requests.
  • Enterprise Applications: ERP, CRM, or other business-critical applications with high transaction rates.

Real-World Implementation
  1. Oracle Universal Connection Pool (UCP):
    • Oracle recommends UCP for Java applications connecting to Oracle databases.
    • It provides advanced features such as connection validation, load balancing, and failover.
  2. Third-Party Connection Pooling:
    • Popular frameworks like Hibernate or Spring in Java ecosystems often use connection pooling libraries such as HikariCP or Apache DBCP.
  3. Oracle RAC (Real Application Clusters):
    • In RAC environments, connection pooling becomes even more critical to efficiently distribute the load across cluster nodes.
  4. Oracle Database Resident Connection Pooling (DRCP):
    • For environments with many short-lived connections (e.g., PHP or Python applications), DRCP is specifically designed to manage pooled server-side sessions.

When Not to Use
  • For long-lived, persistent connections that are continuously active.
  • In systems with minimal or predictable traffic where connection pooling overhead outweighs benefits.

Monitoring and Tuning
  • Oracle's Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) reports can help identify whether connection pooling is effectively addressing the network request load.
  • Performance tuning should involve adjusting pool size, timeout settings, and monitoring wait events.

Connection pooling is an essential component of Oracle database architecture when dealing with overwhelmed servers, and its use is a best practice in high-demand, high-availability environments.

(DRCP) Database Resident Connection Pooling

Database Resident Connection Pooling (DRCP) provides a connection pool of dedicated servers for typical Web application scenarios. A Web application typically makes a database connection, uses the connection briefly, and then releases it. Through DRCP, the database can scale to tens of thousands of simultaneous connections.
DRCP provides the following advantages:
  1. Complements middle-tier connection pools that share connections between threads in a middle-tier process.
  2. Enables database connections to be shared across multiple middle-tier processes. These middle-tier processes may belong to the same or different middle-tier host.
  3. Enables a significant reduction in key database resources required to support many client connections. For example, DRCP reduces the memory required for the database and boosts the scalability of the database and middle tier. The pool of available servers also reduces the cost of re-creating client connections.
  4. Provides pooling for architectures with multi-process, single-threaded application servers, such as PHP and Apache, that cannot do middle-tier connection pooling.

DRCP uses a pooled server, which is the equivalent of a dedicated server process (not a shared server process) and a database session combined. The pooled server model avoids the overhead of dedicating a server for every connection that requires the server for a short period.
Clients obtaining connections from the database resident connection pool connect to an Oracle background process known as the connection broker. The connection broker implements the pool functionality and multiplexes pooled servers among inbound connections from client processes.

SEMrush Software