Databases Reference
In-Depth Information
repeatedly parsing and creating cursors (server-side resource to manage the SQL
request) for the same statement, along with the associated network round trips.
A statement pool is owned by a physical connection, and prepared state-
ments are placed in the pool after their initial execution. Statements remain in
the pool until the physical connection is closed or the maximum size is reached.
Statement pooling typically doesn't affect application code. If you use pre-
pared statements and turn on statement pooling, when the prepared statement is
closed, it is placed in the statement pool for reuse instead of actually being closed.
All implementations of statement pooling that we have seen have at least one
attribute you can configure: maximum pool size, which defines the maximum
number of prepared statements that can be associated with a connection. We
provide guidelines for setting this attribute later in this section.
Some implementations of statement pooling have additional features that
allow you to do the following:
•
Import statements into a pool to preload the pool, which means the startup
time for the statement pool is paid when the application or application
server is started, not when the application is running.
•
Clear the pool. This feature is mainly used for maintenance purposes. For
example, if a change is made to an index on the database server and this
index is part of an execution plan for a pooled statement, the statement will
fail upon execution. In this case, you need a way to clear the pool so that a
new execution plan can be created for the statement.
Note
JDBC 4.0 provides a more granular level of statement pooling by allow-
ing applications to hint to the pool manager about whether or not a pre-
pared statement should be pooled.
Performance Tip
Use parameters in your SQL statements to take full advantage of state-
ment pooling. The parsed information from statements using parameters
can be reused even if the parameter values change in subsequent execu-
tions. In contrast, if you use literals and the literal values change, the
application cannot reuse the parsed information.
