Connection Pools

The performance shortcomings just described were recognized soon after web-database applications came into existence. Today, the problem is commonly solved through the use of connection pools. The connection pool, a pool of preestablished database connections, links the application to a database schema. The application opens, and keeps open, several physical database connections. When an incoming request is made, the application will grab a connection from the pool, issue a query (or queries), and then return the connection back to the pool. A connection pool is similar to having the database predial all of the phone numbers it’ll need to talk to the database. If someone needs to ask the database a question, the application simply hands them the equivalent of the telephone receiver and lets them talk (or query, as the case may be).

Connection pools are used to enhance performance in a multiuser environment. Concurrent requests can be handled simultaneously by granting each request one of the preestablished connections from the connection pool. Once the user’s request has been satisfied, the connection is returned to the pool so it can be used by the next request. In this manner, the connection overhead is minimized and you increase the performance and scalability of your application.

Connection pool implementations vary widely. In the beginning, the pools had to be created and managed by the application. Today, it’s more common for the application server to provide the capabilities to make the connection pool management a transparent process (or almost transparent) to the applications themselves. You’ll now look at two variations of JDBC connection pooling supported by Oracle: implicit connection cache and the OCI connection pool. OCI stands for Oracle Call Interface and will be discussed in more details in the upcoming “OCI Connection Pool” section.

Choosing Connection’s Database Account

Connection pools are ideal for web applications that support many end users. If you have an application that has 20 distinct users, you should consider creating dedicated connections for each user. As the number of application users increases, there comes a point at which the application server and database resources are consumed by open and potentially unused connections. The pool allows the database connections to occur in a manner that doesn’t require an open, dedicated connection for each user.

Oracle Implicit Connection Cache

In the first examples, an Oracle JDBC connection pool known as the implicit connection cache will be used. JDBC stands for Java Database Connectivity and represents the industry standard way of connecting Java client programs to database servers. To use JDBC, a Java program must incorporate the use of a specific JDBC driver; the driver is a vendor provided implementation of the JDBC specification. All JDBC drivers support the same syntax and APIs. For more information on JDBC, refer to the Oracle Database JDBC Developer’s Guide and Reference 10g.

A connection cache implies a caching of physical database connections. For our discussions, the phrases “connection cache” and “connection pool” are synonymous. A physical database connection (in the implicit connection cache) includes both the dedicated client-to-server network connection and the database session. In the OCI Connection Pool section, you’ll see that the physical connection and the database session can be separated.

The value in using the implicit connection cache is that the application doesn’t have to manage the connection pool. The implicit connection cache does this by creating a logical mapping

of the actual physical database connections. This simplifies the application development process as the implicit connection cache has the intelligence to know how to perform the caching in the most effective manner. In the past, the application developer was responsible for the intelligent management of the connection pool.

The driver (program) for the implicit connection cache is provided with the Oracle Database 10g JDBC libraries in the file $ORACLE_HOME/jdbc/lib/classes12.jar. The following examples represent the code that would be used by a Java application connecting to the database.

Connection Cache Example

The examples in this chapter are simplified so that you can focus on the connection pooling concepts. Since connection pools were built to solve the performance problems associated with establishing database connections, the first objective is to measure the time required to establish database connections.

This example has been decomposed into sections that should help in making the code more understandable. In the first section, the requisite libraries are imported, variables are defined, and the data source is created.

package OSBD; import java.sql.*; import oracle.jdbc.pool.OracleDataSource;  public class ConnectionCacheTest {   public static void main(String[] args)  {     long connectTime=0, connectionStart=0, connectionStop=0;     long connectTime2=0, connectionStart2=0, connectionStop2=0;     long connectTime3=0, connectionStart3=0, connectionStop3=0;     try {       OracleDataSource ods = new OracleDataSource();       ods.setURL("jdbc:oracle:thin:@DKNOX:1521:KNOX10G");

The setURL method will be used when you establish database connections. It defines a JDBC type and the required details needed to connect to the database listener. In this example, the thin JDBC driver is used. The thin JDBC driver program is completely written in Java. Its name is derived from the fact that no additional software is required for the database connection to occur. You also can use the OCI JDBC driver that has a Java interface but relies on the Oracle Call Interface APIs (which are written in C) to actually fulfill the connections. The implicit connection cache supports both.

Next, the database username and password are set. This defines the database account (schema) to which the initial connections will be established.

      ods.setUser("scott");       ods.setPassword("tiger");

For the connection pool, you have to explicitly tell the data source that you want to enable the connection caching. If you fail to do this, the connections will not get cached. To enable this, execute the following:

      ods.setConnectionCachingEnabled(true); // be sure set to true

Next, define the bounds in which the connection pool will operate. The InitialLimit property indicates the number of initial physical connections that will be established. The MinLimit sets the minimum number of connections the pool can shrink to. The MaxLimit specifies the maximum number of physical connections ever to be created within the pool.

      java.util.Properties prop = new java.util.Properties();       prop.setProperty("InitialLimit", "3");        prop.setProperty("MinLimit", "3");       prop.setProperty("MaxLimit", "20");       ods.setConnectionCacheProperties (prop);

The initial limit of three means that the first time a connection request is made, the connection pool will establish three physical connections (all to the SCOTT user as set in the previous setUser method). The minimum and maximum limits are the bounds in which the connection pool will operate. You can’t exceed the maximum number of connections. For the example, requesting the twenty-first connection would return a null object. Being able to limit the pool size is critical because it helps to ensure your application doesn’t saturate the database with connections.

For this example, three database connections are made and the time to make the connections is calculated. While not obvious from the code, the first connection request transparently initializes the connection pool. The second connection is to the same user defined in the first connection. And, the third connection will connect to a separate user. Before the program exits, the database connections are closed. The connection times are then printed.

      connectionStart = System.currentTimeMillis();       Connection conn = ods.getConnection("scott", "tiger");       connectionStop = System.currentTimeMillis();       connectionStart2 = System.currentTimeMillis();       Connection conn2 = ods.getConnection("scott", "tiger");       connectionStop2 = System.currentTimeMillis();       connectionStart3 = System.currentTimeMillis();       Connection conn3 = ods.getConnection("blake", "madd0g");       connectionStop3 = System.currentTimeMillis();       conn.close();       conn2.close();       conn3.close();       ods.close();     } catch (Exception e)    { System.out.println(e.toString()); }            connectTime = (connectionStop - connectionStart);     System.out.println("Initial connection time for pool: " +                          connectTime + " ms.");     connectTime2 = (connectionStop2 - connectionStart2);     System.out.println("Connection 2 to cached Scott user: " +                          connectTime2 + " ms.");        connectTime3 = (connectionStop3 - connectionStart3);     System.out.println("Connection 3 to Blake user: " +                          connectTime3 + " ms.");      } }

Analyzing the Results

When I executed the code, I got the following results:

Initial connection time for pool: 791 ms. Connection 2 to cached Scott user: 0 ms. Connection 3 to Blake user: 140 ms.

The first connection request (done by invoking the getConnection method) initializes the connection pool. The initialization process in the implicit connection cache creates three physical database connections and returns one of them to the application program. In the previous example, this took 791 milliseconds (ms). All three connections were made to the SCOTT user.

The initialization process and connection time for the initial request increases in proportion to the number of physical connections. Recall that the initial number of connections was set in the InitialLimit property value of the connection pool prior to connecting. Naturally, the higher the number specified in the initial limit, the longer the pool will take to fill. For a web application, you might consider accessing the application if the application server has rebooted so that none of your end users will be personally subject to the time required to populate the initial pool.

The second connection time shows the true benefit of this implementation. The connection was again to the SCOTT user. The connection pool was previously initialized to the SCOTT user. Only one of those three connections is currently in use. Therefore, the implicit connection cache actually returned a preestablished connection from the pool that took no measurable time.

The third connection request shows the time required to start a new physical connection. In the example, the connection is made to the BLAKE user. (This shows that the pool doesn’t have to consist of connections to the same database account.) A new connection had to be created because all the connections in the pool were to the SCOTT user. The time to create a new physical connection was 140 milliseconds.

An important point is that any new connection will require roughly the same amount of time. For example, if there was a fourth connection request to the SCOTT user (the cache only has three connections established to SCOTT), a new physical connection would also have to be created. This would have taken about the same amount of time as BLAKE’s connection.

If your objective is to completely eliminate the connection time, then you should set your initial limit and minimum limit equal to the maximum limit and always connect to the same database schema. The first connection will take a long time, but all subsequent connections will take very little time (zero milliseconds in my tests). This is how many web applications are designed today. The application creates a connection pool that opens multiple physical connections to the database, and all the connections in the pool are to the same database schema. Performance is the design goal, and it can be achieved using the connection cache.

Security Risks

If all the connections are made to the same database schema, the database has no way of knowing the real identity of the application user. Security can’t be done on anonymity; therefore, more work is required to be able to use the database security features along with the connection pool. “Proxy Authentication,” shows an alternative pooling mechanism that preserves the user’s identity.

Closing a connection for a connection pool doesn’t close the physical database connection. Rather, the logical connection is returned to the pool where it’s available for the next request. In the previous example, if you closed the third connection to the BLAKE user, then later requested a connection to the BLAKE user, you’d get the previously established connection (assuming that no one else had taken it first).

The following code snippet illustrates this:

      connectionStart3 = System.currentTimeMillis();       Connection conn3 = ods.getConnection("blake", "madd0g");       conn3.close();       connectionStop3 = System.currentTimeMillis();           connectionStart4 = System.currentTimeMillis();       Connection conn4 = ods.getConnection("blake", "madd0g");       connectionStop4 = System.currentTimeMillis();

The implicit connection cache knows the fourth connection (conn4) is to a database user to which a connection has already been established. This preestablished connection is consequently returned. By capturing the timing as before, you’ll see the preceding code returns a 0 ms connect time for the fourth connection:

Initial connection time for pool: 791 ms. Connection 2 to cached Scott user: 0 ms. Connection 3 to Blake user: 150 ms. Connection 4 to cached Blake user: 0 ms. 

The security risk with caching connections is that the database session is never reset. Database roles that were enabled, PL/SQL programs that were invoked, and application context (see Chapter 6 explores how to do this.

Role Schemas

One excellent use of the implicit connection cache is to support role schema designs. This is the N:M mapping model discussed in Chapter 3, where multiple end users share security privileges based on the role or function they perform in the application. That is, all the accountants’ connect to the ACCOUNTANT schema, all the clerks connect to the CLERK schema, and so on.

You can create multiple connection caches, one for each role schema. You can either start all connection caches when the application starts, or create the connection caches on demand—that is, create the pool for each role schema the first time a user for that role schema makes a request. The Oracle software automatically grabs the correct connection from the corresponding cache based on the username and password supplied in the getConnection request. If you use this design, you must also use Client Identifiers and application contexts to achieve true end-user security. See Chapter 6 for more details.

Viewing the Connections

It’s a good idea to validate your application connection schemes. To do this, run the program again and query the V$SESSION view while the program is executing. This is a good method for verifying the connections your application is making. An artificial stall has been inserted to make the program sleep for ten seconds because the program executes too quickly to be able to issue the query (at least for me). To accomplish this, place the following line of code just above the first conn.close() statement:

// previous code       Thread.sleep(10000);       conn.close();       conn2.close();  // remaining code

Execute the program again, and within the ten second window, query the V$SESSION table:

sec_mgr@KNOX10g> COL module format a20 sec_mgr@KNOX10g> SELECT   username, server, module   2      FROM v$session   3     WHERE TYPE != 'BACKGROUND'   4       AND username IS NOT NULL   5  ORDER BY username; USERNAME                       SERVER    MODULE ------------------------------ --------- ---------- BLAKE                          DEDICATED SCOTT                          DEDICATED SCOTT                          DEDICATED SCOTT                          DEDICATED SEC_MGR                        DEDICATED SQL*Plus 

This verifies the pool configuration. The three dedicated connections from SCOTT were created when the connection pool was created. The last connection was created for the single connection to the BLAKE user. The record for the SEC_MGR user was for the query itself, which was issued from SQL*Plus.