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:
- 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.
- Run the large update job on the Oracle database tables.
- 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.
- 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
- Use the built-in procedure ttCacheAutoRefreshStatsGet to check the status and the progress of the last ten autorefresh operations for a cache group. For each autorefresh operation, it includes the following statistics: the number of rows refreshed, the time taken to fetch the rows from the Oracle database, and the time taken to perform the updates in the TimesTen Database.
- For each table in a read-only cache group, a change log table is created in the Oracle database to track the updates that need to be refreshed to a TimesTen database. You can use the cacheInfo.sql script to monitor the current autorefresh status, including the autorefresh backlog on the different change log tables.
- Redundant change log tables can impact the performance of the Oracle database tables, as they continue to track the autorefresh changes. These leftover autorefresh objects are caused by a TimesTen database that is offline, or the TimesTen database was destroyed without dropping its read-only cache groups. Redundant change log tables can be found by running the cacheInfo.sql script. Use the cacheCleanup.sql script to remove them from the Oracle database.
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
- Monitor performance statistics using system table. AWT cache group statistics are stored in the system table SYS.SYSTEMSTATS.
e.g. select * from sys.systemstats where name like 'cg.awt%';
It includes a breakdown on the number of rows propagated by the different DML operations, and the number of batches sent to the Oracle database.
- Monitor AWT performance using the ttRepAdmin utility. Use ttRepAdmin with the -showstatus -awtmoninfo options to monitor and obtain more detailed performance statistics for AWT cache groups. The statistics include timing information and the latency between the TimesTen and Oracle database. In order for ttRepAdmin to return AWT performance metrics,
AWT performance monitoring must be enabled using the built-in procedure ttCacheAwtMonitorConfig.
- If the log buffer size (LogBufMB) is set too small, it can affect the throughput for AWT Cache groups. Since it will read from the transaction logs on disk instead of from the log buffer in memory. Refer to the section on Check size of log buffer for more information.
- Monitor Oracle Database errors reported by AWT cache groups. There are 2 types of Oracle Database errors, Permanent and Transient errors, they can impact the throughput for AWT cache groups. Permanent errors are Insert, delete, update errors that occur while applying the changes to the Oracle database. For example, a unique constraint violation may cause an update to fail. Permanent errors are not retried. They are reported in the AWT error file. Transient errors are underlying problems on the Oracle database. For example, Oracle database is not available or the TNS listener is down. Transient errors are reported in the support log. The AWT transactions are retried. The problem must be solved before AWT operations can continue.
For more information refer to the section on AWT performance monitoring in the Cache User's Guide.