Best Practices for In-Memory Database Cache

This page contains recommendations on how best to configure IMDB Cache. It contains configuration and monitoring best practices for Read-only and Asynchronous Write-through cache groups.

IMDB Cache Configuration

Use the same user for Cache Administration User and Cache Manager User

In order to cache Oracle database tables into a TimesTen database, you must create a cache administration user in the Oracle database. This user owns objects that are used for cache management and change tracking purposes. Similarly, in the TimesTen database, a cache manager user is responsible for setting up and managing the cache grid, and creating and loading data into cache groups. It is recommended to use the same database user name for both the cache administration user and the cache manager user. It simplifies the configuration of IMDB Cache. Note: The user in the TimesTen database can have a different password to the one in the Oracle database.

Read-only cache group

Choose appropriate Autorefresh Intervals

Autorefresh interval is a cache group property that determines the frequency of data refresh for cache tables in a read-only cache group. Cache groups with the same autorefresh interval are refreshed within the same transaction. This ensures transactional data consistency across cache groups. However, if set inappropriately, this can lead to poor refresh performance, since the updates to all the cache tables are applied within in a single large transaction. Consider using different autorefresh intervals between cache groups for better performance, if transactional consistency across cache groups is not required. This enhances the performance of autorefresh, since the volume of updates will be smaller, and multiple refresh threads can be used, one per refresh interval.

Handle large updates on Oracle Database tables

Autorefresh operation is performed as a single transaction on a TimesTen database. Large updates such as a bulk update on read-only cache group base tables in an Oracle database can take a long time to refresh, and potentially cause the Perm space to be exhausted. The three suggestions below can help to alleviate the problem:

Avoid large updates to Oracle Database tables

If possible, break down large updates to the Oracle database tables into smaller transactions, and execute them over multiple autorefresh intervals. This allows the updates to be processed automatically by the autorefresh operation.

Use LOAD Cache Group with PARALLEL clause

Updates to Oracle database tables are refreshed into TimesTen using a single autorefresh thread. For applications, where data can be temporarily unavailable during the refresh, consider pausing the cache group, and use a manual LOAD CACHE GROUP to populate the cache table using multiple threads. Here are the steps:

  1. Set the AUTOREFRESH state of the cache groups to PAUSED prior to running the bulk update on the Oracle Database. This ensures there is a consistent view of the data during the update.
  2. Run the large update job on the Oracle database tables.
  3. Use manual UNLOAD CACHE GROUP and LOAD CACHE GROUP with PARALLEL threads and COMMIT EVERY N ROWS, to re-populate the data using multiple threads and smaller transactions.
  4. Set the AUTOREFRESH state of the cache groups back to ON to resume autorefresh.

For more information refer to the section on Avoiding performance and memory problems for large batch jobs on Oracle database tables in the Cache User's Guide.

Define autocommit limit for autorefresh

If transactional consistency of data during refresh is not required, consider using the built-in ttCacheAutorefreshXactLimit to break up a single autorefresh transaction into multiple smaller transactions. Once the autorefresh cycle completes, the data is transactionally consistent.

For more information refer to the section on Improving execution of massive data changes for autorefresh, read-only cache groups in the Cache User's Guide.

Use separate Oracle database connection versus PassThrough

PassThrough specifies which SQL statements are to be executed locally in the TimesTen database and which are to be redirected to the Oracle database for execution. This allows an application to establish a single database connection to both the TimesTen and the Oracle database. If your application requires frequent access to non cache tables in the Oracle database, establishing a separate connection, and executing the Oracle SQL statements directly on the Oracle database is more efficient than going through the TimesTen database.

Monitor Read-only cache groups

For more information refer to the section on Monitoring autorefresh cache groups in the Cache User's Guide.

Asynchronous writethrough (AWT) cache group

Configure parallel propagation to Oracle Database tables

To improve the throughput for AWT cache groups, you can configure multiple threads that act in parallel to propagate updates to the Oracle database. Parallel propagation enforces transactional dependencies and applies changes to Oracle database tables in the same commit order. The following database attributes ReplicationApplyOrdering, ReplicationParallelism and CacheAwtParallelism enable parallel propagation, and control the number of parallel write threads to the Oracle Database.

For more information refer to the section on Configuring parallel propagation to Oracle tables in the Cache User's Guide.

Follow Replication best practices for A/S pairs

When using Active Standby Pairs with AWT cache groups, the updates to the Oracle database are sent from the standby database. Therefore the throughput for AWT cache groups can be impacted by the replication performance between the active and the standby database. Please review the Best Practices for TimesTen Replication to ensure it is configured optimally.

Add constraint checks to minimize AWT errors

Constraints on tables in the Oracle database can cause AWT propagation to fail if the same checks were not enforced in the TimesTen database. It is a good practice to create the same constraints in the TimesTen database; if that is not possible, build the checks into the application.

Monitor Asynchronous WriteThrough cache groups

For more information refer to the section on AWT performance monitoring in the Cache User's Guide.