Dynamic Load Performance

Dynamic loading of a single cache instance based on a primary key search of the root table has faster performance than primary key searches on a child table or foreign key searches on a child table.

See Dynamic Cache Groups.

Dynamic loading of multiple cache instances may have faster performance than loading single cache instances. See Dynamically Loading Multiple Cache Instances.

If you combine dynamic load operations with autorefresh operations, you may experience some contention. See Improving Performance for Autorefresh Operations for details on how to improve your performance in this situation.

There can be a performance cost when opening a new connection for a dynamic load operation. You can reduce the cost of opening new connections by creating a cache connection pool. You may want to use a cache connection pool if your application requires frequent dynamic load operations that would create too many open connections to the Oracle database. See Managing a Cache Connection Pool to the Oracle Database for Dynamic Load Requests.

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.

There can be a performance cost when opening a new connection for the dynamic load. You can reduce the cost of opening new client connections by creating a cache connection pool.

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:

Enable the Cache Connection Pool

You can specify that TimesTen creates a cache connection pool on the TimesTen server when it starts up.

If a cache connection pool is created, then a dynamic load request from a client/server connection acquires a connection from the cache connection pool, performs the dynamic load, and returns the connection to the cache connection pool after the dynamic load request completes. The cache connection pool is destroyed when the TimesTen server shuts down.

Note:

The cache connection pool can only be initiated from client-server applications (using multithreaded mode) and is used only for dynamic loads initiated for dynamic read-only cache groups.

To enable client/server connection requests to use the cache connection pool, an application must specify the following connection attributes when connecting.

MaxConnsPerServer connection attribute: This connection attribute sets the maximum number of client/server connections that can be created for each child server process. When the value is set to > 1, each TimesTen child server can handle multiple client connections where each client/server connection is multithreaded. You can only use the cache connection pool with a multithreaded client/server connection.

When MaxConnsPerServer connection attribute is set to 1, TimesTen creates one single-threaded client/server connection for each child server process.
ServersPerDSN connection attribute: Value designates the number of child server processes to spawn for the TimesTen server. Default is 1.

Each new incoming connection spawns a new child server process up to the value specified by the ServersPerDSN connection attribute. When the maximum number of child server processes is reached, the existing child server processes handle multiple connections (up to the number specified in MaxConnsPerServer) in a round-robin method. That is, if you specify ServersPerDSN = 2 and MaxConnsPerServer = 3, then the first two connections would spawn two child server processes. The third through the sixth connections would be handled by these child server processes, where each child server process would service every other connection.

Once all of the child server processes have the maximum allowed number of connections, the next incoming connection starts a new set of child server processes.

The ServersPerDSN and MaxConnsPerServer connection attributes are used to designate how to distribute connections across multiple child server processes.
UseCacheConnPool connection attribute: Must be enabled (set to 2) to use the cache connection pool. When the UseCacheConnPool connection attribute is enabled, the cache connection pool is created and used for dynamic load operations initiated by multithreaded client/server connections. If the UseCacheConnPool connection attribute is disabled (set to 0), then the cache connection pool is not created and the dynamic load operations perform using the existing behavior. See UseCacheConnPool in the Oracle TimesTen In-Memory Database Reference.

Note:

You may also want to limit the number of connections to the Oracle database. See Limiting the Number of Connections to the Oracle Database.

The following example specifies connection attributes for the cache connection pool in the DSN definition:

The cache1 DSN definition in the sys.odbc.ini file specifies UseCacheConnPool=2, ServersPerDSN=2 and MaxConnsPerServer=3.

[cache1]
DataStore=/users/OracleCache/database1
PermSize=64
OracleNetServiceName=oracledb
DatabaseCharacterSet=AL32UTF8
UseCacheConnPool=2
ServersPerDSN=2
MaxConnsPerServer=3

Alternatively, you can specify both of the connection attributes on the command line when connecting from the application.

ttIsql "DSN=cache1;OracleNetServiceName=oracledb;UseCacheConnPool=2;ServersPerDSN=2;MaxConnsPerServer=3"

Note:

See the MaxConnsPerServer, ServersPerDSN, and UseCacheConnPool sections in the Oracle TimesTen In-Memory Database Reference.

Size the Cache Connection Pool

You can appropriately size the cache connection pool to avoid contention for connections with the ttCacheConnPoolSet built-in procedure.

The ttCacheConnPoolSet built-in procedure saves the values of these parameters in the Oracle database, which are then used as the default values when restarting the TimesTen server. Once applied to each TimesTen server, the values specified are used for the cache connection pool across all client/server applications for a TimesTen database.

If you want to modify these values after the TimesTen server starts, you can change the cache connection pool sizing parameters on the Oracle database using the ttCacheConnPoolSet built-in procedure. After which, you can re-initialize the TimesTen server by either:

Restarting the TimesTen server to re-initialize the server (and all child server processes) with the new sizing parameters.
Dynamically re-initializing each TimesTen server with the cache connection pool parameters saved on the Oracle database with the ttCacheConnPoolApply built-in procedure. See Dynamically Applying Cache Connection Pool Sizing Modifications.

You can run the ttCacheConnPoolSet built-in procedure from a direct connection, a single-threaded client/server connection or a multithreaded client/server connection.

Note:

See the ttCacheConnPoolSet in the Oracle TimesTen In-Memory Database Reference.

For example, the following initiates the minimum and maximum number of pooled connections to be between 10 and 32 connections and the increment is 1. The maximum idle time by the client is set to 10 seconds. And all dynamic load operations will wait for an available connection from the cache connection pool.

Command> call ttCacheConnPoolSet(10, 32, 1, 10, 0);

Set the minimum and maximum size of the cache connection pool to levels where connections are available when needed. If no connections are available in the pool, dynamic load operations stall until a connection from the pool is available (unless you set ConnNoWait=1). If a connection to the Oracle database times out, you receive an error denoting a loss of the connection, sometimes requiring a rollback on TimesTen.

You can query what the cache connection pool parameters are with the ttCacheConnPoolGet built-in procedure.

See Example Demonstrating Management of the Cache Connection Pool.

Use the ChildServer Connection Attribute to Identify a Child Server Process

In a client/server environment, TimesTen can create multiple TimesTen child server processes to handle incoming requests from clients. You can use the ChildServer connection attribute to identify a specific child server process when performing certain cache connection pool administrative functions, such as the ttCacheConnPoolGet('current') or ttCacheConnPoolApply built-in procedures.

The target child server process is identified by a value specified using the ChildServer=n connection attribute, where n is a number ranging from 1 to the number of running child server processes. When you specify the ChildServer connection attribute, then the client process connects using the identified child server process. If the attribute is not specified, then the client process connects using a randomly selected child server process.

See ttCacheConnPoolApply and ttCacheConnPoolGet in the Oracle TimesTen In-Memory Database Reference. See Example Demonstrating Management of the Cache Connection Pool.

Dynamically Applying Cache Connection Pool Sizing Modifications

The cache connection pool parameters are saved in the Oracle database, which are used to initialize the cache connection pool for the TimesTen database every time that the TimesTen server restarts. The sizing is set on the Oracle database with the ttCacheConnPoolSet built-in procedure. This sizing applies to each TimesTen server and child server processes when started.

However, you can dynamically resize the cache connection pool parameters for each child server process (while the database is running) with the ttCacheConnPoolApply built-in procedure.

Execute the ttCacheConnPoolSet built-in procedure to set a new set of parameters that are stored on the Oracle database.
Connect to the child server process.
Dynamically associate the new set of cache connection pool parameters for this particular child server process with the ttCacheConnPoolApply built-in procedure.

For example, the following connects to the child server process identified as 1 and applies the new cache connection pool configuration to this child server process. It does the same process for child server process 2 (given that ServersPerDSN=2).

Command> connect "DSN=cache1;ChildServer=1;";
Command> call ttCacheConnPoolApply;
Command> disconnect;

Command> connect "DSN=cache1;ChildServer=2;";
Command> call ttCacheConnPoolApply;
Command> disconnect;

You can run the ttCacheConnPoolApply built-in procedure only from a multithreaded client/server connection.

If the cache connection pool fails, you can recreate the pool by running the ttCacheConnPoolApply built-in procedure from any child server process.

See Example Demonstrating Management of the Cache Connection Pool.

Example Demonstrating Management of the Cache Connection Pool

This example shows how to set new values for the cache connection pool and apply them to two separate child server processes.

This example uses the cache1 DSN as shown in Enable the Cache Connection Pool that enables the cache connection pool. It also assumes that you have set the cache administrator and password as described in Registering the Cache Administration User Name and Password.

/* Since ServerPerDSN is set to two and MaxConnsPerServer is set to 3, the first 
 and second connections spawn off both child server processes. And then you can
 create four more connections to reach the MaxConnsPerServer maximum, which are
 routed by the TimesTen server to the appropriate child server process (using a
 round robin method).*/
Command> connect "DSN=cache1;" as conn1;
Command> connect "DSN=cache1;" as conn2;
Command> connect "DSN=cache1;" as conn3;
Command> connect "DSN=cache1;" as conn4;
Command> connect "DSN=cache1;" as conn5;
Command> connect "DSN=cache1;" as conn6;
 
Command> use conn1;
 
/* Query the values for the cache connection pool that are saved on the Oracle database*/
Command> call ttCacheConnPoolGet('saved');
< 1, 10, 1, 10, 0, -1, -1, -1>
 
/* Change the configuration of the cache connection pool */
Command> call ttCacheConnPoolSet(1, 20, 1, 10, 0);
 
/* Query existing values for cache connection pool saved on the Oracle data base. 
 Since these are the saved values, this returns -1 for OpenCount, BusyCount
 and LastOraErr. */
Command> call ttCacheConnPoolGet('saved');
< 1, 20, 1, 10, 0, -1, -1, -1 >
 
/* Query existing values for the current cache connection pool on this TimesTen database */
Command> call ttCacheConnPoolGet('current');
< 1, 10, 1, 10, 0, 1, 0, 0 >
 
/* Connect to the child server process 1 using the ChildServer=1 connection
 attribute. Apply the saved values as the current values to the cache connection
 pool for child server process identified as ChildServer 1. */
Command> connect "DSN=cache1;ChildServer=1;";
Command> call ttCacheConnPoolApply;
Command> disconnect;
 
/* Connect to the child server process 1 using the ChildServer=1 connection
 attribute. Apply the saved values as the current values to the cache connection
 pool for child server process identified as ChildServer 2. */
Command> connect "DSN=cache1;ChildServer=2;";
Command> call ttCacheConnPoolApply;
Command> disconnect;
 
/* Query values for the cache connection pool in ChildServer 1 */
Command> use conn1;
Command> call ttCacheConnPoolGet('current');
< 1, 20, 1, 10, 0, 1, 0, 0 >
 
/* Query values for the cache connection pool in ChildServer 2 */
Command> use conn2;
Command> call ttCacheConnPoolGet('current');
< 1, 20, 1, 10, 0, 1, 0, 0 >

Limiting the Number of Connections to the Oracle Database

You can optimize performance while ensuring a limit to the number of connections to the Oracle database.

Tuning the total number of connections depends on the following:

Note:

These calculations assume that all connections to the Oracle database are client/server connections using a multithreaded server. The connections referred to in the rest of this section are only those used for dynamic load operations. There can be other connections from TimesTen to the Oracle database that are not accounted for in these calculations.

N: The number of connections to the Oracle database.
P: The limit on the number of connections for each cache connection pool, where each TimesTen child server process has a cache connection pool. You can set this with the MaxSize cache connection pool parameter using the ttCacheConnPoolSet built-in procedure.
S: The maximum number of child server processes that can be spawned for new connections. Currently, there is no direct way to limit the number of child server processes. Indirectly, you can influence the number of child server processes by setting the MaxConnsPerServer and Connections connection attributes. You should measure S on your system when your system is in a steady state that represents the typical operating conditions.
M: The maximum number of connections for each child server process, which you can set with the MaxConnsPerServer connection attribute.
D: The maximum number of connections to a DSN, which is set with the Connections connection attribute.

The number of connections (N) to the Oracle database is equal to the maximum number of TimesTen child server processes (S) times the maximum number of connections for each cache connection pool (P).

N=S*P

The maximum number of connections (D) to the DSN is equal to the maximum number of connections for each child server process (M) times the maximum number of TimesTen child server processes (S).

D=M*S

With the above calculation, you can also state:

S=D/M

Since there is no hard limit that we can configure for the number of TimesTen child server processes, we substitute for S to get the following equation:

N=(D*P)/M

Assuming that all connections to the Oracle database are client/server connections, then the maximum number of connections to the Oracle database arising from cache connection pools is equal to the maximum number of connections to the DSN (set by the Connections connection attribute) times the number of connections for each cache connection pool (set by the MaxSize cache connection pool parameter), which is then divided by the maximum number of connections for each child server process (set by the MaxConnsPerServer connection attribute).

Note:

For TimesTen Scaleout, you may also want to limit the connections to the Oracle database through limiting the number of cache agents. See Limiting Cache Agent Connections to the Oracle Database in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Restrictions for the Cache Connection Pool

There are restrictions when using the cache connection pool.

You cannot use the cache connection pool in conjunction with the Oracle Database Resident Connection Pooling feature.
The cache connection pool is only supported for multithreaded client/server connections, where the MaxConnsPerServer connection attribute must be greater than 1.
The cache connection pool is only used for dynamic load operations for dynamic read-only cache groups.