While Database Connection Pooling is not a new concept, it is becoming increasingly more important with the proliferation of both public and private web-based applications. This post will cover using a JDBC connection pool against an Oracle database. The general idea of Database Connection Pooling is universal and these concepts can be used with any app server / database combination. If you have never used a database connection pool before, Apache has an open source product called DBCP which is fairly well documented for use with the Tomcat app server.
What is Database Connection Pooling
Database Connection Pooling involves creating a persistent group of Oracle sessions, all waiting to fulfill database requests. When a pooled connection is used, it is not destroyed like a regular database session. It is returned to the pool so that it can be used to fulfill another request. The reason for the existence of Database Connection Pools is to eliminate the process (and time required) to create and destroy database connections. Because Oracle is a multi-process server, establishing a database session is considered expensive as it involves creating an operating system process and allocation of server memory. A busy web-based applicaiton with a few hundred users can easily generate thousands of logins per minute (we’ve seen this situation more than once). Although logins look like they happen pretty fast, the time spent waiting adds up very quickly. The bottom line is that logins just burn CPU.