Session Pools and the Oracle OCI Connection Pool

The implicit connection cache works very well, but there are two considerations worth further investigation. The first is that the cache isn’t optimized for end-user authentication. In the event that you want to log in as a different user, say the BLAKE user in the preceding example, you suffer the costs of starting a new physical connection. The second area of interest is that the connections are all physical connections. In most cases, you typically associate a physical database connection with a database session. However, this isn’t always true.

Oracle supports fine-grained management of database connections and database sessions. The physical connection and the database session can be decoupled. The OCI layer exploits this by allowing developers to multiplex database sessions over the same physical connection. Because physical connections are more costly to establish, this ability is very desirable. OCI will therefore allow you to create multiple database sessions over a single physical connection, thereby offering a more efficient way to handle resources.

Oracle supports session multiplexing in JDBC with the Oracle OCI connection pool. In a similar manner to the implicit connection cache, the Oracle OCI connection pool allows you to create multiple physical connections to the database. The OCI layer will then multiplex database sessions over all the available physical connections in the pool. The OCI layer transparently manages all the session multiplexing and physical pool allocation, thereby hiding the complexities from the application developer.

Note 

The OCI connection pool is only available to the thick JDBC drivers, and these thick drivers require the installation of the Oracle Client Network libraries.

OCI Connection Pool Example

The preceding example has been ported to use the OCI connection pool. The setup is quite similar to the implicit connection cache. The OCI Connection Pool library has been imported, the data source has changed object types, and a variable was created to hold the database TNS connection information.

package OSBD; import java.sql.*; import oracle.jdbc.*; import oracle.jdbc.pool.OracleOCIConnectionPool; import oracle.jdbc.oci.OracleOCIConnection; public class ConnectionPoolTest {   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;     String tnsAlias = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = " +                       "  (PROTOCOL = TCP)(HOST = DKNOX)(PORT = 1521)) )" +                       "  (CONNECT_DATA = (SERVICE_NAME = knox10g) ) )";     try {       OracleOCIConnectionPool ods = new OracleOCIConnectionPool();       ods.setURL("jdbc:oracle:oci:@"+tnsAlias);       ods.setUser("scott");       ods.setPassword("tiger");

The OCI connection pool also allows you to configure the pool parameters. The OCI connection pool size is set to be the same as the implicit connection cache so you can compare connection performance. Because the OCI connection pool will be multiplexing sessions over your physical connections, in implementation you may not need as many physical connections as you would using the implicit connection cache.

      java.util.Properties prop = new java.util.Properties();       prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "3");       prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "20");       prop.setProperty(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1");       ods.setPoolConfig(prop);

Timings are performed for the same three connections that were created in the connection cache test. The first connection establishes the pool. The second connection is to the same user that was used to create the connection pool. The third connection is to a user outside of the pool. Once completed, the connections are closed and the result times are 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()); }     // print connection time     connectTime = (connectionStop - connectionStart);     System.out.println("Initial connection time for pool: " +                          connectTime + " ms.");     connectTime2 = (connectionStop2 - connectionStart2);     System.out.println("Connection 2 to Scott user: " +                          connectTime2 + " ms.");        connectTime3 = (connectionStop3 - connectionStart3);     System.out.println("Connection 3 to Blake user: " +                          connectTime3 + " ms.");      } }

Analyzing the Results

The results are significantly different from those received from the implicit connection cache test:

Initial connection time for pool: 170 ms. Connection 2 to Scott user: 20 ms. Connection 3 to Blake user: 20 ms.

The initial connection pool created three physical connections. This initial value is significantly less than the approximately 800 ms required to create a pool of three connections for the implicit connection cache. The difference has nothing to do with the JDBC driver type either. Testing with the thick driver for the implicit connection cache yields connect times greater than 800 ms.

The drastic difference in connect times may be attributed to the OCI implementation. Remember that the OCI layer is written in C and will most likely execute faster than the same program written in Java.

Another interesting result is seen with the two identical connection times for the SCOTT user and the BLAKE user. In the implicit connection cache example, SCOTT’s second connection took 0 ms. SCOTT’s subsequent connection using the OCI connection pool took 20 ms longer. However, the BLAKE user required 150 ms with the implicit connection cache because a new physical database connection had to be established and a database session created. With the OCI connection pool, the time required to get a new connection is only 20 ms because a new database session is created using an already established physical connection.

Viewing the Connections

You can use the same thread.sleep() call that was shown in the implicit connection cache to view the database sessions. While the program is sleeping, query the V$SESSION view:

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                          PSEUDO    javaw.exe SCOTT                          DEDICATED javaw.exe SCOTT                          DEDICATED javaw.exe SCOTT                          DEDICATED javaw.exe SCOTT                          PSEUDO    javaw.exe SCOTT                          PSEUDO    javaw.exe SEC_MGR                        DEDICATED SQL*Plus 7 rows selected.

The records with the SERVER column value of DEDICATED are for the three dedicated sessions that were configured in the initialization value for the connection pool. Recall that this connection pool connected to the SCOTT user. The SEC_MGR query is from the SQL*Plus connection.

The records with the SERVER column value of PSUEDO are “lightweight” database sessions. The sessions are what is returned from the connection request. When the connection request is made, the OCI layer creates a new database session for the user utilizing one of the existing physical connections in the pool. Once the session is established, the physical connection is free and can be used to spawn new database sessions. The OCI Connection Pool will multiplex the creation of all the database sessions over the pool of physical connections that are available.

Starting a session over a preestablished database connection is faster than starting a brand new database connection and session. This explains why the creation of a new session (even to a new user) took 20 ms, and the creation of a new connection using the implicit connection cache took 150 ms.

Points of Note

The OCI connection pool uses resources from the database’s large pool. As the number of concurrent users grows, you may find that you need to increase the large_pool_size to accommodate the additional memory required to handle the concurrent requests.

Another important point to note is the OCI connection pool doesn’t support transparent application failover (TAF). TAF allows you to transparently reconnect your application to a clustered database instance in case the instance you are already connected to fail. TAF is only relevant in failing over database connections when using Oracle’s Real Application Clusters.

By comparing the test results, you might conclude that an application that always connects to the same database schema would perform best with the implicit connection cache, and an application that needs to connect to different database schemas would perform better with the OCI connection pool. Connecting to the same database schema is useful in some situations, but it may be the wrong design when your application users require different database authorizations.

Let’s explore connecting to separate database schemas in more detail because there is still an unresolved issue lurking.

Password Management Risk

If you need to support separate authentications for separate users, the application will need some way of obtaining the user’s database password. There are three solutions. First, the application could prompt the user for the password. This will work and could be secure, but it’s seldom done in practice. With the proliferation of single sign-on solutions, many applications can’t gain access to the user’s password. The application knows only the user’s identity and that the user has successfully authenticated to the single sign-on server.

Second, the application could securely store the user’s password. The preferred method for secure password storage is to compute a one-way hash of the password. Unfortunately, you can’t use the hashed password as the authenticator for the user; you need the plaintext password for database authentication.

Storing the password in each application also creates a password maintenance challenge. When the user changes their password, the password will have to be changed in all the applications that store a copy of the user’s password. There are several other challenges presented by allowing applications to handle passwords, all of which helped drive the industry to using single sign-on solutions and organizational wide master directory services.

Note 

In a single sign-on environment, the application typically doesn’t have access to the user’s password.



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net