7 Cache Performance

The following sections contain information about cache performance.

Note:

See Oracle TimesTen In-Memory Database Troubleshooting Guide for extensive information about monitoring autorefresh operations and improving autorefresh performance. See "Monitoring autorefresh cache groups" and "Poor autorefresh performance".

See "AWT performance monitoring" and "Possible causes of poor AWT performance" in the Oracle TimesTen In-Memory Database Troubleshooting Guide for more information about AWT cache group performance.

Dynamic load performance

Dynamic loading 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. For more details, see "Dynamically loading a cache instance".

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.

Also, there can be a performance cost when opening a new connection for a dynamic load operation. See "Managing a cache connection pool to the Oracle database for dynamic load requests" for details on how to reduce the cost of opening new connections by creating a connection pool.

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 TimesTen 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.

By default, the connection to the Oracle database remains open until the application's connection to TimesTen is closed. When your application initiates a dynamic load, each client connection is associated with a connection to the Oracle database (when using TimesTen Cache). If you use several connections, TimesTen's requests for new connections to the Oracle database could exceed the maximum number of connections allowed to the Oracle database.

Applications can have multiple dynamic load requests to the Oracle database, which could result in too many open connections to the back-end Oracle database. However, for client/server applications with multiple connections per server, you can configure TimesTen to use the cache connection pool for all connections to the Oracle database. The cache connection pool can only be utilized by an application using a client/server connection as the pooled connections are shared across all client/server connections.

Dynamic load requests will use an existing connection to the Oracle database from the cache connection pool (rather than creating a new one) to reduce the total number of open 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 increases 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 connections rather than having each process (thread) dedicated to a single connection.

  • Enabling the sharing of session level server resources, such as memory, between connections.

Once the connection is returned to the cache connection pool, the application logically sees the connection as disconnected. Thus, if your application contains passthrough statements (DDL or DML statements executed on 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 explicitly execute the commit or rollback within the transaction before the dynamic load.

Note:

If an application will execute a higher than normal 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 your performance) from any application using the cache connection pool.

  • Maintaining a completely separate connection directly to the Oracle Database to execute its SQL directly against Oracle, rather than using passthrough statements to execute SQL indirectly through TimesTen.

To decide whether to use the cache connection pool, evaluate if your applications request a high number of dynamic load requests of data from the Oracle database (resulting in too many open 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. All TimesTen servers share parameters that are stored on the Oracle database.

A dynamic load request from a client/server connection acquires a connection from the cache connection pool, performs the dynamic load, then 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 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, your application must specify the following connection attributes when connecting.

  • MaxConnsPerServer connection attribute: Value must be set to > 1 to use the cache connection pool.

    A TimesTen server can create and assign work to multiple TimesTen child server processes (in multithreaded mode) when this connection attribute is greater than 1. This connection attribute sets the maximum number of client connections that can be created in a connection pool 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: Set the value = 2 to enable the use of the cache connection pool. See "UseCacheConnPool" in the Oracle TimesTen In-Memory Database Reference for more details.

Note:

You may also want to limit the number of connections to the Oracle database, which can be specified with the Connections connection attribute. See "Limit the number of connections to the Oracle database" for details.

The following describes the expected behavior when a dynamic load is initiated from different types of connections:

  • Direct connection: The cache connection pool is not used. The dynamic load performs using the existing behavior.

  • Single threaded client/server connection (when MaxConnsPerServer=1): The cache connection pool is not used. The dynamic load performs using the existing behavior.

  • Multithreaded client/server connection (when MaxConnsPerServer>1): Uses the cache connection pool for dynamic load if it is created. Otherwise, an error is returned.

You can set connection attributes for the connection either within a DSN definition or on a connect string.

Example 7-1 Specifying 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

Example 7-2 Specifying connection attributes for the cache connection pool on the connect string

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:

For more information, 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 on the Oracle database, which are then used as the default values when restarting the TimesTen server.

Note:

If you are dynamically changing the sizing, you can apply the changes to each TimesTen server by executing the ttCacheConnPoolApply built-in procedure. See "Apply cache connection pool sizing to currently executing database" for details on the ttCacheConnPoolApply built-in procedure.

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.

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

Note:

For more information, see the "ttCacheConnPoolSet" section 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, then TimesTen performs the following depending on the setting for ConnNoWait parameter in the ttCacheConnPoolSet built-in procedure:

  • ConnNoWait=0: TimesTen stalls until a connection from the pool is available or until a timeout occurs. If the Oracle database is down, applications wait until the Oracle database comes back up, or a timeout occurs.

    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.

  • ConnNoWait=1: Any dynamic load operations fail with an error if there are no available connections in the cache connection pool.

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" for a full example of how to use this built-in procedure.

Use the ChildServer connection attribute to identify a child server process

In a client/server environment, TimesTen can have multiple TimesTen child server processes to handle incoming requests from clients. You provide the ChildServer connection attribute to identify a specific child server process for certain cache connection pool built-in procedures. Each child server process is identified by a number assigned with the ChildServer=n connection attribute, where n is a number ranging from 1 to the number of running child server processes. Once connected to the child server process, you can execute either the ttCacheConnPoolGet('current') or ttCacheConnPoolApply built-in procedures that are meant for a specific child server process.

See "ttCacheConnPoolApply" and "ttCacheConnPoolGet" sections in the Oracle TimesTen In-Memory Database Reference for details on the built-in procedures that require this connection attribute. See "Example demonstrating management of the cache connection pool" for an example of how to use this connection attribute.

Apply cache connection pool sizing to currently executing database

Since the cache connection pool parameters are saved on the Oracle database, these parameters are used to initialize the cache connection pool for the TimesTen database every time that the TimesTen server restarts.

However, if you set the cache connection parameters while the database is already running, then you can dynamically resize the cache connection pool parameters on each child server process with the ttCacheConnPoolApply built-in procedure. After which, the cache connection pool parameters are associated with the child server process.

For example, the following connects to the child server process identified as 1 and applies the saved 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 execute the ttCacheConnPoolApply built-in procedure only from a multithreaded client/server connection.

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

See "Example demonstrating management of the cache connection pool" for a full example of how to use this built-in procedure.

Example demonstrating management of the cache connection pool

Using the cache1 DSN as shown in Example 7-1 that enables the cache connection pool and assuming that you have set the cache administrator and password as described in "Set the cache administration user name and password", the following example sets new values for the cache connection pool and applies them to two separate child server processes.

/* 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 >

Limit the number of connections to the Oracle database

You can tune your performance while ensuring a limit to the number of connections to the Oracle database. Tuning the number of total connections depends on the following:

  • 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, which you can set with the MaxSize cache connection pool parameter in the ttCacheConnPoolSet built-in procedure.

  • S: The 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 limit the number of child server processes by setting the MaxSize parameter, MaxConnsPerServer connection attribute, and Connections connection attributes, as shown in the formula below.

  • 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 number of TimesTen child server processes (S) times the number of connections for each cache connection pool (M).

N=S*P

While 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 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 merging the two equations together to eliminate S to get the following equation:

N=(D*P)/M

Thus, the number of connections to the Oracle database is set 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).

Restrictions for the cache connection pool

Restrictions when using the cache connection pool:

  • You cannot use this in conjunction with the Oracle Database Resident Connection Pooling feature.

  • This is only supported for multithreaded client/server connections, where the MaxConnsPerServer connection attribute must be greater than 1.

  • This can only be used for dynamic load operations for dynamic read-only cache groups.

Improving AWT throughput

Use the following methods to improve through put for AWT cache groups:

Improving AWT throughput with parallel propagation

To improve throughput for an AWT cache group, you can configure multiple threads that act in parallel to propagate and apply transactional changes to the Oracle database. Parallel propagation enforces transactional dependencies and applies changes in AWT cache tables to Oracle Database tables in commit order. For full details, see "Configuring parallel propagation to Oracle Database tables".

Improving AWT throughput with SQL array execution

The CacheAWTMethod connection attribute setting determines whether to use the PL/SQL execution method or SQL array execution method for asynchronous writethrough propagation when applying changes to the Oracle database.

  • PL/SQL execution method: AWT bundles all pending operations into a single PL/SQL collection that is sent to the Oracle database server to be executed. This execution method is appropriate when there are mixed transactions and network latency between TimesTen and the Oracle database server. It is efficient for most use cases when the workload consists of mixed INSERT, UPDATE, and DELETE statements to the same or different tables. By default, TimesTen uses the PL/SQL execution method (CacheAWTMethod=1).

  • SQL array execution method: Consider changing CacheAWTMethod to 0 when the changes consist of mostly repeated sequences of the same operation (INSERT, UPDATE, or DELETE) against the same table. For example, SQL array execution is very efficient when a user does an update that affects several rows of a table. Updates are grouped together and sent to the Oracle database in a single batch.

The PL/SQL execution method transparently falls back to SQL array execution mode temporarily when it encounters one of the following:

  • A statement that is over 32761 bytes in length.

  • A statement that references a column of type BINARY FLOAT, BINARY DOUBLE and VARCHAR/VARBINARY of length greater than 4000 bytes.

Note:

You can also set this value with the ttDBConfig built-in procedure with the CacheAwtMethod parameter. For details, see "ttDBConfig" in the Oracle TimesTen In-Memory Database Reference.

For more information, see "CacheAWTMethod" in Oracle TimesTen In-Memory Database Reference.

Improving performance for autorefresh operations

The following sections describe how to improve performance for autorefresh operations:

Minimizing delay for cached data with continuous autorefresh

You can specify continuous autorefresh with an autorefresh interval of 0 milliseconds. With continuous autorefresh, the next autorefresh cycle is scheduled as soon as possible after the last autorefresh cycle has ended.

Continuous autorefresh could result in a higher resource usage when there is a low workload rate on the Oracle database, since the cache agent could be performing unnecessary round-trips to the Oracle database.

See "CREATE CACHE GROUP" and "ALTER CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference for details on how to set the autorefresh interval.

Reducing contention on TimesTen for dynamic read-only cache groups with incremental autorefresh

For most cache group operations, autorefresh and dynamic load operations coordinate their access to the Oracle database for correctness. The default TimesTen coordination behavior could result in contention between autorefresh and dynamic load operations (in extreme cases).

If you have dynamic read-only cache groups with incremental autorefresh, then:

  • Multiple dynamic load operations could be blocked by autorefresh operations.

  • Autorefresh operations are frequently delayed while waiting for dynamic load operations to complete.

Enabling the DynamicLoadReduceContention database system parameter changes the way that autorefresh and dynamic load operations coordinate, which results in reduced contention between autorefresh and dynamic load operations.

  • Dynamic load operations are never blocked by autorefresh operations (due to additional synchronization).

  • Autorefresh operations are not completely delayed by dynamic load operations. Instead, autorefresh operations will wait a short while for concurrently executing dynamic load operations to be notified that a new autorefresh operation is starting. This enables dynamic load operations to synchronize in tandem with concurrently executing autorefresh operations.

Note:

You cannot change the value of the DynamicLoadReduceContention database system parameter if there are any dynamic read-only cache groups or if the cache or replication agents are running. You must unload or drop (and recreate later) any existing dynamic read only cache groups before you can change this value.

The following example sets DynamicLoadReduceContention=1:

call ttDbConfig('DynamicLoadReduceContention','1');

You can query the current value of the DynamicLoadReduceContention parameter.

call ttDbConfig('DynamicLoadReduceContention');

Note:

For more details, see "ttDBConfig" in the Oracle TimesTen In-Memory Database Reference.

Requirements for setting DynamicLoadReduceContention

The DynamicLoadReduceContention database system parameter is supported on TimesTen Release 11.2.2.8.39 and following. In addition, the following are requirements when enabling the DynamicLoadReduceContention database system parameter:

Required Oracle Database privileges

You must grant two additional Oracle Database privileges to the cache administration user:

  • EXECUTE ON SYS.DBMS_FLASHBACK

  • SELECT ANY TRANSACTION

These are granted to the cache administration user when you execute the grantCacheAdminPrivileges.sql and initCacheAdminSchema.sql scripts.

Unsupported Oracle Database feature

This feature requires the use of the Oracle Database Flashback Transaction Queries. However, if you are using Oracle Database 12.2.0.1 with Multitenant option, Flashback Transaction Queries only supports Local Undo. It does not support Oracle Database 12.2.0.1 Multitenant option with Shared Undo.

Required settings for active standby pair replication scheme

When you are using an active standby pair replication scheme:

  • Both active and standby masters must be installed with TimesTen Release 11.2.2.8.39 or following. If you are replicating between and active and standby masters where each is installed with different TimesTen versions, then this parameter cannot be enabled if one of the TimesTen versions does not support this feature.

  • The DynamicLoadReduceContention database system parameter must be set to the same value on both the active and standby masters.

Otherwise, an error is written to the daemon log. Replication will not progress until the settings and TimesTen versions conform on both the active and standby masters.

Reducing lock contention for read-only cache groups that use autorefresh and dynamic load

An autorefresh operation automatically loads committed updates on cached Oracle Database tables into the TimesTen cache tables. A dynamic load operation requests data from the Oracle database (originating from a SELECT statement) and inserts the rows into the cache group. Both the autorefresh and dynamic load operations require access to the TimesTen Cache metadata, which could cause a lock contention.

At the end of an autorefresh operation, TimesTen updates the metadata to track the autorefresh progress. If you have requested guaranteed durability by setting the DurableCommits connection attribute to 1, then the autorefresh updates to the metadata are always durably committed. If you have requested delayed durability by setting the DurableCommits connection attribute to 0 (the default), then TimesTen must ensure that the autorefresh updates to the metadata are durably committed before the garbage collector can clean up the autorefresh tracking tables stored on the Oracle database.

When a durable commit is initiated for the metadata, any previous non-durable committed transactions in the log buffer that have not been flushed to disk are also a part of the durable commit. On hosts with busy or slow disks, the durable commit could be slow enough to lock out dynamic load requests for an undesirable amount of time.

If you notice that your application is timing out because of a lock contention between autorefresh and dynamic load requests, you can set the CacheCommitDurable cache configuration parameter to 0 with the ttCacheConfig built-in procedure. This reduces the occurrence of lock contention between autorefresh and dynamic load requests in the same application by:

  • Executing a non-durable commit of the autorefresh changes made to the metadata.

  • Uses a separate thread in the cache agent to durably commit the autorefresh changes before the garbage collector cleans up the autorefresh tracking tables stored on the Oracle database.

By starting a new thread to perform the durable commit of the transaction log, the lock is removed after the non-durable commit of the autorefresh changes to the metadata. After which, there is no longer a lock held on the metadata and any dynamic load requests for the recently refreshed tables can continue processing without waiting.

The following example sets CacheCommitDurable=0:

call ttCacheConfig('CacheCommitDurable',,,'0');

You can query the current value of the CacheCommitDurable parameter.

call ttCacheConfig('CacheCommitDurable');

See "ttCacheConfig" in the Oracle TimesTen In-Memory Database Reference for more details.

Note:

Since setting CacheCommitDurable=0 spawns a new thread to perform the durable commit of the transaction log buffer, the initiation of the garbage collection for the autorefresh change log records starts later than when CacheCommitDurable=1.

Improving performance when reclaiming memory during autorefresh operations

As described "Transaction reclaim operations" in the Oracle TimesTen In-Memory Database Operations Guide, TimesTen Classic resource cleanup occurs during the reclaim phase of a transaction commit. To improve performance, a number of transaction log records are cached in memory to reduce the need to access the transaction log on disk in the the commit buffer. However, TimesTen must access the transaction log on disk if the transaction is larger than the reclaim buffer.

When you are using autorefresh for your cache groups, the cache agent has its own reclaim buffer to manage the transactions that are committed within autorefresh operations. If the cache agent reclaim buffer is too small, the commit operations during autorefresh can take longer than expected as it must access the transaction log on disk. To avoid any performance issues, you can configure a larger reclaim buffer for the cache agent so that the cache agent can handle larger transactions in memory at reclaim time.

When using an active standby pair replication scheme to replicate autorefresh operations, the replication agent applies the same autorefresh operations as part of the replication. Thus, the replication agents on both the active and standby nodes have their own reclaim buffers that should be configured to be the same size or greater than the cache agent reclaim buffer.

The ttDbConfig built-in procedure provides the following parameters for setting the maximum size for the reclaim buffers for both the cache agent and the replication agent. (The memory for the reclaim buffers are allocated out of temporary memory.)

  • CacheAgentCommitBufSize sets the maximum size for the reclaim buffer for the cache agent.

  • RepAgentCommitBufSize sets the maximum size for the reclaim buffer for the replication agent. You should configure the maximum size for the reclaim buffer on both the active and standby nodes. It is recommended that you set the size for the reclaim buffers to the same value on both nodes, but not required.

Note:

For more details, see "ttDBConfig" in the Oracle TimesTen In-Memory Database Reference.

To determine if you should increment the size for the cache agent reclaim buffer, evaluate the CommitBufMaxReached and CommitBufNumOverflows statistics provided by the ttCacheAutorefIntervalStatsGet built-in procedure. For more details, see "Retrieving statistics on autorefresh transactions".

Executing large transactions with incremental autorefresh read-only cache groups

At certain times, you may execute large transactions, such as for the end of the month, the end of a quarter, or the end of the year transactions. You may also have situations where you modify or add a large amount of data in the Oracle database over a short period of time. For read-only cache groups with incremental autorefresh, TimesTen could potentially run out of permanent space when an autorefresh operation applies either one of these cases. Therefore, for these situations, you can configure an autorefresh transaction limit, where the large amount of data is broken up, applied, and committed over several smaller transactions.

Note:

The autorefresh transaction limit can only be set for static read-only cache groups.

The ttCacheAutorefreshXactLimit built-in procedure enables you to direct autorefresh to commit after executing a specific number of operations. This option applies to all incremental autorefresh read-only cache groups that are configured with the same autorefresh interval.

Since the single transaction is broken up into several smaller transactions, transactional consistency cannot be maintained while autorefresh is in progress. Once the autorefresh cycle completes, the data is transactionally consistent. To protect instance consistency, we recommend that you set the autorefresh transaction limit only on cache groups with only a single table, since instance consistency between the parent and child tables is not guaranteed. When the autorefresh transaction limit is turned on, TimesTen does not enforce the foreign key relationship that protects instance consistency. Once you turn off the autorefresh transaction limit for incremental autorefresh read-only cache groups, both instance and transactional consistency are maintained again.

Note:

If you are using an active standby pair, you must call the ttCacheAutorefreshXactLimit built-in procedure for the same values on both the active and standby masters.

Using ttCacheAutorefreshXactLimit

Note:

For more information, such as the syntax and the returned result set, see "ttCacheAutorefreshXactLimit" in the Oracle TimesTen In-Memory Database Replication Guide.

For the month end processing, there can be a large number updates in a single transaction for the Oracle tables that are cached in autorefresh cache groups. In order to ensure that the large transaction does not fill up permanent memory, you can enable autorefresh to commit after every 256 (or any other user specified number) operations with the ttCacheAutorefreshXactLimit built-in procedure.

Turn on an autorefresh transaction limit for incremental autorefresh read-only cache groups before a large transaction with the ttCacheAutorefreshXactLimit built-in procedure where the value is set to ON or to a specific number of operations. Then, when autorefresh finishes updating the cached tables in TimesTen, turn off the autorefresh transaction limit for incremental autorefresh read-only cache groups with the ttCacheAutorefreshXactLimit built-in procedure.

The following example sets up the transaction limit to commit after every 256 operations for all incremental autorefresh read-only cache groups that are defined with an interval value of 10 seconds.

call ttCacheAutorefreshXactLimit('10000', 'ON');

After the month end process has completed and the incremental autorefresh read-only cache groups are refreshed, disable the transaction limit for incremental autorefresh read-only cache groups that are defined with the interval value of 10 seconds.

call ttCacheAutorefreshXactLimit('10000', 'OFF');

To enable the transaction limit for incremental autorefresh read-only cache groups to commit after every 1024 operations, provide 1024 as the value as follows:

call ttCacheAutorefreshXactLimit('10000', '1024');

Example of potential transactional inconsistency

The following example uses the employee and departments table, where the department id of the department table is a foreign key that points to the department id of the employee table.

The following example creates two incremental autorefresh read-only cache groups, where each is in its own cache group. The autorefresh transaction limit is enabled with ttCacheAutorefreshXactLimit before a large transaction and is disabled after it completes.

  1. Before you initiate the large transaction, invoke ttCacheAutorefreshXactLimit to set the interval value and the number of operations after which to automatically commit. The following sets the number of operations to three (which is intentionally low to show a brief example) for all incremental autorefresh read-only cache groups with a two second interval.

    CALL ttCacheAutorefreshXactLimit('2000', '3');
    < 2000, 3 >
    1 row found.
    
  2. Create the incremental autorefresh read-only cache groups with interval of two seconds. This example creates two static (non-dynamic) read-only cache groups, where each contains a single table.

    CREATE READONLY CACHE GROUP cgDepts AUTOREFRESH MODE INCREMENTAL 
     INTERVAL 2 SECONDS 
    FROM departments
        ( department_id    NUMBER(4) PRIMARY KEY
        , department_name  VARCHAR2(30) NOT NULL
        , manager_id       NUMBER(6)
        , location_id      NUMBER(4)
        );
     
    CREATE READONLY CACHE GROUP cgEmpls AUTOREFRESH MODE INCREMENTAL 
     INTERVAL 2 SECONDS 
    FROM employees
        ( employee_id    NUMBER(6) PRIMARY KEY
        , first_name     VARCHAR2(20)
        , last_name      VARCHAR2(25) NOT NULL
        , email          VARCHAR2(25) NOT NULL UNIQUE
        , phone_number   VARCHAR2(20)
        , hire_date      DATE NOT NULL
        , job_id         VARCHAR2(10) NOT NULL
        , salary         NUMBER(8,2)
        , commission_pct NUMBER(2,2)
        , manager_id     NUMBER(6)
        , department_id  NUMBER(4)
        );
    
  3. Perform a manual LOAD CACHE GROUP for both autorefresh cache groups.

    LOAD CACHE GROUP cgDepts COMMIT EVERY 256 ROWS;
    27 cache instances affected.
     
    LOAD CACHE GROUP cgEmpls COMMIT EVERY 256 ROWS;
    107 cache instances affected.
    

You can have inconsistency within the table during an autorefresh as shown with the employees table.

  1. On TimesTen, select the minimum and maximum salary of all employees.

    SELECT MIN(salary), MAX(salary) FROM employees;
    < 2100, 24000 >
    1 row found.
    
  2. On the Oracle database, add 100,000 to everyone's salary.

    UPDATE employees SET salary = salary + 100000;
    107 rows updated.
    
  3. On TimesTen, when you perform the SELECT again (while the autorefresh transactions are commmitted after every 3 records), it shows that while the maximum salary has updated, the minimum salary is still the old value.

    SELECT MIN(salary), MAX(salary) FROM employees;
    < 2100, 124000 >
    1 row found.
    
  4. However, once the autorefresh completes, transactional consistency is maintained. For this example, once the autorefresh process completes, all salaries have increased by 100,000.

    SELECT MIN(salary), MAX(salary) FROM employees;
    < 102100, 124000 >
    1 row found.
    
  5. The large transaction is complete, so disable the transaction limit for autorefresh cache groups with a 2 second interval.

    call ttCacheAutorefreshXactLimit('2000', 'OFF');
    

You can have transactional inconsistency between cache groups if you perform a SQL statement while the autorefresh process is progressing. The following SELECT statement example executes against the employees and department table in the cgDepts autorefresh cache group. With this example, since the foreign key is not enforced on TimesTen and the autorefresh process applies several transactions, the employee table updates may be inserted before the department updates.

In addition, all of the updates for both tables in the cache group are not applied until the autorefresh cycle has completed. In the following example, the SELECT statement is executed before the autorefresh process is complete. Thus, the results do not show all of the expected data, such as the department name and several employees (some of the lawyers in the legal department 1000) are missing.

SELECT e.department_id, d.DEPARTMENT_NAME, e.FIRST_NAME, e.LAST_NAME  
       FROM employees e, departments d         
       WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+) 
       AND e.department_id >= 1000 ORDER BY 1,2,3,4;
< 1000, <NULL>, Alan, Dershowitz >
< 1000, <NULL>, F. Lee, Bailey >
< 1000, <NULL>, Johnnie, Cochran >
3 rows found.

However, after the autorefresh process completes, transactional consistency is maintained. The following shows the same SELECT statement executed after the autorefresh is complete. All expected data, the department information and all of the new lawyers, are updated.

SELECT e.department_id, d.DEPARTMENT_NAME, e.FIRST_NAME, e.LAST_NAME  
       FROM employees e, departments d         
       WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+) 
       AND e.department_id >= 1000 ORDER BY 1,2,3,4;
< 1000, Legal, Alan, Dershowitz >
< 1000, Legal, Barry, Scheck >
< 1000, Legal, F. Lee, Bailey >
< 1000, Legal, Johnnie, Cochran >
< 1000, Legal, Robert, Kardashian >
< 1000, Legal, Robert, Shapiro >
6 rows found.

For autorefresh cache groups that have more than one table, you can also experience transactional inconsistency if you execute SQL statements while the autorefresh process is in progress.

  1. Initiate the transaction limit for incremental autorefresh cache groups of 2 seconds with the ttCacheAutorefreshXactLimit built-in procedure and create a single autorefresh cache group with two tables: the employees and departments tables.

    CALL ttCacheAutorefreshXactLimit('2000', '3');
    < 2000, 3 >
    1 row found.
     
    CREATE READONLY CACHE GROUP cgDeptEmpls AUTOREFRESH MODE INCREMENTAL
     INTERVAL 2 SECONDS 
    FROM departments
         ( department_id    NUMBER(4) PRIMARY KEY
         , department_name  VARCHAR2(30) NOT NULL
         , manager_id       NUMBER(6)
         , location_id      NUMBER(4)
         )
       , employees
         ( employee_id    NUMBER(6) PRIMARY KEY
         , first_name     VARCHAR2(20)
         , last_name      VARCHAR2(25) NOT NULL
         , email          VARCHAR2(25) NOT NULL UNIQUE
         , phone_number   VARCHAR2(20)
         , hire_date      DATE NOT NULL
         , job_id         VARCHAR2(10) NOT NULL
         , salary         NUMBER(8,2)
         , commission_pct NUMBER(2,2)
         , manager_id     NUMBER(6)
         , department_id  NUMBER(4)
         , foreign key(department_id) references departments(department_id)
         );
    
  2. Manually load the cache group.

    LOAD CACHE GROUP cgDeptEmpls COMMIT EVERY 256 ROWS;
    27 cache instances affected.
    
  3. Perform a SELECT statement on TimesTen that uploads all of the legal department data.

    SELECT e.department_id, d.department_name, count(*)
           FROM employees e, departments d         
           WHERE e.department_id  = d.department_id (+) 
           GROUP BY e.department_id, d.department_name
           ORDER BY 1 desc;
    < 110, Accounting, 2 >
    < 100, Finance, 6 >
    < 90, Executive, 3 >
    < 80, Sales, 34 >
    < 70, Public Relations, 1 >
    < 60, IT, 5 >
    < 50, Shipping, 45 >
    < 40, Human Resources, 1 >
    < 30, Purchasing, 6 >
    < 20, Marketing, 2 >
    < 10, Administration, 1 >
    11 rows found.
    
  4. On Oracle, insert a new legal department, numbered 1000, with 6 new lawyers in both the employee and department tables.

  5. When performing a SELECT statement on TimesTen during the autorefresh process, only data on two of the lawyers in department 1000 have been uploaded into TimesTen.

    SELECT e.department_id, d.department_name, count(*)
           FROM employees e, departments d         
           WHERE e.department_id  = d.department_id (+) 
           GROUP BY e.department_id, d.department_name
           ORDER BY 1 desc;
    < 1000, Legal, 2 >
    < 110, Accounting, 2 >
    < 100, Finance, 6 >
    < 90, Executive, 3 >
    < 80, Sales, 34 >
    < 70, Public Relations, 1 >
    < 60, IT, 5 >
    < 50, Shipping, 45 >
    < 40, Human Resources, 1 >
    < 30, Purchasing, 6 >
    < 20, Marketing, 2 >
    < 10, Administration, 1 >
    12 rows found.
    
  6. However, after the autorefresh process completes, all 6 employees (lawyers) in the legal department have been uploaded to TimesTen. Now, it is transactionally consistent.

    SELECT e.department_id, d.department_name, COUNT(*)
           FROM employees e, departments d         
           WHERE e.department_id  = d.department_id (+) 
           GROUP BY e.department_id, d.department_name
           ORDER BY 1 desc;
    < 1000, Legal, 6 >
    < 110, Accounting, 2 >
    < 100, Finance, 6 >
    < 90, Executive, 3 >
    < 80, Sales, 34 >
    < 70, Public Relations, 1 >
    < 60, IT, 5 >
    < 50, Shipping, 45 >
    < 40, Human Resources, 1 >
    < 30, Purchasing, 6 >
    < 20, Marketing, 2 >
    < 10, Administration, 1 >
    12 rows found.
    
  7. The large transaction is complete, so disable the transaction limit for autorefresh cache groups with a 2 second interval.

    call ttCacheAutorefreshXactLimit('2000', 'OFF');
    

Retrieving statistics to evaluate performance when a transaction limit is set

To see how a autorefresh transaction limit for a particular autorefresh interval is performing, you can retrieve statistics for the last 10 incremental autorefresh transactions for this autorefresh interval with the ttCacheAutorefIntervalStatsGet built-in procedure. See "Retrieving statistics on autorefresh transactions" for more information.

Configuring a select limit when using incremental autorefresh for read-only cache groups

To facilitate incremental autorefresh for read-only cache groups, TimesTen executes a table join query on both the Oracle database base table and its corresponding change log table to retrieve the incremental changes. However, if both tables are very large, the join query can be slow. In addition, if the Oracle database base table is continuously updated while the join-query is executing, you may receive the ORA-01555 ”Snapshot too old” error from a long-running autorefresh query.

To avoid this situation, you can configure incremental autorefresh with a select limit for static read-only cache groups, which joins the Oracle database base table with a limited number of rows from the autorefresh change log table. You can configure a select limit with the ttCacheAutorefreshSelectLimit built-in procedure.

Note:

The select limit can only be set for static read-only cache groups.

Autorefresh continues to apply changes to the cached table incrementally until all the rows in the autorefresh change log table have been applied. When there are no rows left to apply, the autorefresh thread sleeps for the rest of the interval period.

Note:

For details on the syntax, parameters, result set, and restrictions, see "ttCacheAutorefreshSelectLimit" in the Oracle TimesTen In-Memory Database Reference.

For example, before a large transaction, you can call the ttCacheAutorefreshSelectLimit built-in procedure to set a select limit to 1000 rows for incremental autorefresh cache groups with an interval value of 10 seconds. The following example sets the value to ON.

Command> call ttCacheAutorefreshSelectLimit('10000', 'ON');
< 10000, ON >
1 row found.

The following example set a select limit to 2000 rows for incremental autorefresh cache groups with an interval value of 7 seconds.

Command> call ttCacheAutorefreshSelectLimit('7000', '2000');
< 7000, 2000 >
1 row found.

You can disable any select limit for incremental autorefresh cache groups with an interval value of 10 seconds by setting the value to OFF.

Command> call ttCacheAutorefreshSelectLimit('10000', 'OFF');
< 10000, OFF >
1 row found.

To see how a select limit for a particular autorefresh interval is performing, you can retrieve statistics for incremental autorefresh transactions for this autorefresh interval. See "Retrieving statistics on autorefresh transactions" for more information.

How to determine the cache group name for a particular select limit

To determine the interval for a cache group, use ttIsql and run the cachegroups command:

> cachegroups cgowner.cgname;

This returns all attributes for the cgowner.cgname cache group including the interval.

To determine which intervals have a select limit, you can run the following query on the Oracle database where <cacheAdminUser> is the cache administrator, <hostName> is the host name of the machine where the TimesTen database is located, <databaseFileName> is the database path taken from the DataStore attribute, and substitute the version number (such as 06) for the xx.

SELECT * FROM <cacheAdminUser>.tt_xx_arinterval_params
 WHERE param='AutorefreshSelectEveryN'
   AND host='<hostName>'
   AND database like '%<databaseFileName>%'
 ORDER BY arinterval;
 

For example, if the cache administrator user name is pat, the host name is myhost, the database file name is myTtDb, and 06 is substituted for xx that is the TimesTen Classic minor release number then:

SELECT * FROM pat.tt_06_arinterval_params
 WHERE param='AutorefreshSelectEveryN'
   AND host='myhost'
   AND database like '%myTtDb%'
 ORDER BY arinterval;

The interval is stored in milliseconds.

Retrieving statistics to evaluate performance when using a select limit

To see how a select limit for a particular autorefresh interval is performing, you can retrieve statistics for incremental autorefresh transactions for this autorefresh interval with the ttCacheAutorefIntervalStatsGet built-in procedure. See "Retrieving statistics on autorefresh transactions" for more information.

Retrieving statistics on autorefresh transactions

Call the ttCacheAutorefIntervalStatsGet built-in procedure for statistical information about the last 10 autorefresh cycles for a particular autorefresh interval defined for an incremental autorefresh read-only cache group.

Note:

For more information on syntax and the returned result set for this built-in procedure, see "ttCacheAutorefIntervalStatsGet" in the Oracle TimesTen In-Memory Database Reference.

This built-in procedure is useful if you have set an transaction limit or a select limit for incremental, autorefresh read-only cache groups. See "Executing large transactions with incremental autorefresh read-only cache groups" and "Configuring a select limit when using incremental autorefresh for read-only cache groups" for details.

The following example shows how to call the ttCacheAutorefIntervalStatsGet built-in procedure to retrieve statistics for incremental autorefresh read-only cache groups that have been defined as static and have the interval of 2 seconds:

Command> call ttCacheAutorefIntervalStatsGet(2000, 1);

< 2000, 1, 21, 2013-04-30 06:05:38.000000, 100, 3761, 3761, 822, 1048576, 
1280, 0, 58825, 63825, 13590, 0, 0, 0, 0, 0 >
< 2000, 1, 20, 2013-04-30 06:05:37.000000, 100, 85, 85, 18, 1048576, 1280, 
0, 55064, 60064, 12768, 0, 0, 0, 0, 0 >
< 2000, 1, 19, 2013-04-30 06:05:32.000000, 100, 3043, 3043, 666, 1048576, 
1280, 0, 54979, 59979, 12750, 0, 0, 0, 0, 0 >
< 2000, 1, 18, 2013-04-30 06:05:30.000000, 100, 344, 344, 74, 1048576, 
1280, 0, 51936, 56936, 12084, 0, 0, 0, 0, 0 >
< 2000, 1, 17, 2013-04-30 06:05:28.000000, 100, 1826, 1826, 382, 1048576, 
1280, 0, 51592, 56592, 12010, 0, 0, 0, 0, 0 >
< 2000, 1, 16, 2013-04-30 06:05:26.000000, 100, 55, 55, 12, 1048576, 
1280, 0, 49766, 54766, 11628, 0, 0, 0, 0, 0 >
< 2000, 1, 15, 2013-04-30 06:05:22.000000, 100, 2901, 2901, 634, 1048576, 
1280, 0, 49711, 54711, 11616, 0, 0, 0, 0, 0 >
< 2000, 1, 14, 2013-04-30 06:05:21.000000, 100, 55, 55, 12, 1048576, 
1280, 0, 46810, 51810, 10982, 0, 0, 0, 0, 0 >
< 2000, 1, 13, 2013-04-30 06:05:10.000000, 100, 5844, 5844, 1263, 1048576, 
1280, 0, 46755, 51755, 10970, 0, 0, 0, 0, 0 >
< 2000, 1, 12, 2013-04-30 06:05:08.000000, 100, 607, 607, 132, 1048576, 
1280, 0, 40911, 45911, 9707, 0, 0, 0, 0, 0 >

10 rows found. 

Caching the same Oracle table on two or more TimesTen databases

For each cache administration user, TimesTen creates a change log table and trigger (as part of what is created to manage caching) in the Oracle database for each cache table in the cache group. A trigger is fired for each committed insert, update, or delete operation on the cached Oracle Database table; the action is logged in the change log table.

If you cache the same Oracle database table in a cache group on two different TimesTen databases, we recommend that you use the same cache administration user name on both TimesTen databases as the owner of the cache table on each TimesTen database. When you use the same cache administration user, only one trigger and change log table are created to manage the changes to the base table. Thus, it is efficient and does not slow down the application.

If you create separate cache administration users on each TimesTen database to own the cache group that caches the same Oracle table, then separate triggers and change log tables exist on the Oracle database for the same table: one for each cache administration user. For example, if you have two separate TimesTen databases, each with their own cache administration user, two triggers fire for each DML operation on the base table, each of which are stored in a separate change log table. Firing two triggers and managing the separate change log tables can slow down the application.

The only reason to create separate cache administration users is if one of the TimesTen databases that caches the same table has a slow autorefresh rate or a slow connection to the Oracle database. In this case, having a single cache administration user on both TimesTen databases slows down the application on the faster connection, as it waits for the updates to be propagated to the slower database.