Database Resident Connection Pooling

A connection broker accepts incoming connection requests from PH processes (e. G. Web server processes handling PH requests) and assigns each a free server in the pool. Each PH process that is executing a PH script communicates with this Oracle server until the connection is released.

This happens automatically at the end of the script, or the connection can be explicitly released. When the connection Is released, the server process Is returned to the pool and the PH process keeps a link only to the connection broker.Active pooled servers contain the Process Global Area (VGA) and the user session data. Idle servers In the pool retain the user session for reuse by subsequent persistent PH connections. When the number of persistent connections is less than the number of pooled servers, a "dedicated optimization" avoids unnecessarily returning servers to the pool when a PH connection is closed. Instead, the dedicated association between the PH process and the server is kept in anticipation that the PH process will quickly become active again.

If PH scripts are executed by numerous web servers, the DROP LOL can grow to its maximum size (albeit typically a relatively small size) even if the rate of incoming user requests is low. Each PH process, either busy or now Idle, will be attached to Its own pooled server. When the pool reaches Its maximum size, a script that Is handled by a PH process without a pooled server will cause an Idle server to be returned to the pool for Immediate reuse. PH Scalability and High Availability Page 5 Figure 1. DROP Architecture.

The pool size and number of connection brokers are configurable.There is always at east one connection broker per database instance when DROP is enabled. Also, at any time, around 5% of the current pooled servers are reserved for authenticating new PH connections. Authentication is performed when a PH process establishes a connection to the connection broker. DROP boosts the scalability of the database and the web server tier because connections to the database are held at minimal cost. Database memory is only used by the pooled servers, and scaling can be explicitly controlled by DROP tuning options.

Without DROP, each PH process creates and estrous database servers when connections are opened and closed. Or alternatively, each process keeps connections open ("persistent connections") even when they are not processing any user scripts. This consumes database resources, shown in Figure Figure 2. Without DROP, idle persistent connections from PH still consume database resources. With the introduction of pooled servers used by DROP, there are now three types of database server process models that Oracle applications can use: dedicated servers, shared servers and pooled servers.

Table 1 .Differences between dedicated reverse, shared servers, and pooled servers for OIC_connect() calls. 2 Dedicated Servers Shared Servers Pooled Servers When the PH connection is created, a network connection to a dedicated server process and associated session are created connection to the dispatcher process is established. A session is created in the SAGA When the PH connection is created, a network connection to the connection broker is established Activity on a connection is handled by the dedicated server Each action on a connection goes through the dispatcher, which hands the work to a shared serverActivity on a connection wakes the broker, which hands the network connection to a pooled server process. The server then handles subsequent requests directly, Just like a dedicated 2 See Table 2 for the differences between connection types. PH Scalability and High Availability Page 7 Dedicated Servers Shared Servers Pooled Servers Scripts executing but with idle PH connections hold a server process and session resources Scripts executing but with idle PH connections hold session resources but not a server process Scripts executing but with idle PH session resourcesClosing a PH connection causes the session to be freed and the server process to be terminated Closing a PH connection causes the session to be destroyed and the pooled server to be released to the pool.

A broker is retained Memory usage is proportional to the number of server processes and sessions. There is one server and one session for each PH connection Memory usage is proportional to the sum of the shared servers and sessions. There is one session Memory usage is proportional to the number of pooled server processes and their sessions. There is one session for each pooled serverPooled servers in use by PH are similar in behavior to dedicated servers. After connection, PH directly communicates with the pooled server for all database operations. PH COCO Connections and DROP The PH COCO extension has three functions for connecting to a database: OIC_connect(), OIC_new_connect(), and OIC_postpone().

The implementation of these functions was reworked in COCO 1. 3 and all benefit from using DROP. Table 2 compares dedicated and pooled servers. Shared servers are similar to dedicated servers with the exception that only the session and not the server is destroyed when a connection is closed.