Managing a Cache Connection Pool to the Oracle Database for Dynamic Load Requests
When a qualifying SELECT statement is issued on any dynamic
read-only cache table and the data does not exist in the cache table (but does exist in
the base Oracle database table), this results in a cache miss. After which, Timesten
performs a dynamic load to retrieve the data from the Oracle database (either over an
existing or a new connection to the Oracle database) and inserts the rows into the cache
group.
By default, a client connection to the Oracle database remains open until the application's connection to TimesTen is closed. When the application initiates a dynamic load, each client connection is associated with a connection to the Oracle database (when using cache). If you use several client connections, TimesTen's requests for new client connections to the Oracle database could exceed the maximum number of client connections allowed to the Oracle database.
Applications can have multiple dynamic load requests spread across multiple client connections to the Oracle database, which could result in too many open client connections to the back-end Oracle database. Alternately, there could be applications across multiple TimesTen databases performing dynamic loads against the same Oracle database. For client/server applications with multiple client connections per server, you can configure TimesTen to use the cache connection pool for all client connections that are used for dynamic load operations from the Oracle database. The cache connection pool can only be utilized by an application using a client connection as the pooled connections are shared across all client connections.
Dynamic load requests will use an existing client connection to the Oracle database from the cache connection pool (rather than creating a new one) to reduce the total number of open client connections. Once the dynamic load request completes, the connection is returned to the cache connection pool.
Using an existing connection from the cache connection pool optimizes your application performance by:
-
Reducing the cost of starting a dedicated Oracle server process (or thread) for each newly requested connection.
-
Reducing the total number of Oracle server processes (threads) by sharing them amongst client connections rather than having each process (thread) dedicated to a single connection. However, if there are no available client connections in the cache connection pool, the dynamic load operation waits until a connection is added to the pool.
-
Enabling the sharing of session level server resources, such as memory, between client connections.
Once the connection is returned to the cache connection pool, the application
logically sees the client connection as disconnected. Thus, if an
application contains passthrough statements (DDL or DML statements performed
in the Oracle database), any passthrough statement must be committed or
rolled back before the dynamic load is requested or an error is thrown. You
can set autocommit to ON or run the commit or rollback
within the transaction before the dynamic load.
Note:
If an application runs a higher than expected number of dynamic load requests and performance is critical, then you might consider either:
-
Removing or minimizing passthrough statements with DDL or DML statements (which can slow down performance) from any application using the cache connection pool.
-
Maintaining a completely separate client connection directly to the Oracle Database to run its SQL directly against the Oracle database, rather than using passthrough statements to run SQL indirectly through TimesTen.
To decide whether to use the cache connection pool, evaluate if any applications request a high number of dynamic load operations from the Oracle database (resulting in too many open client connections to the Oracle database).
The following sections describe how to use the cache connection pool for your dynamic read-only cache groups: