1. Cache Guide
  2. Cache Performance
  3. Improving Performance for Autorefresh Operations
  4. Configuring a Select Limit for Incremental Autorefresh for Read-Only Cache Groups

Configuring a Select Limit for Incremental Autorefresh for Read-Only Cache Groups

To facilitate incremental autorefresh for read-only cache groups, TimesTen runs 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 processing, 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. To protect instance consistency, we recommend that you set the select limit only on cache groups with only a single table.

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:

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 cache groups with incremental autorefresh where the interval value is 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 cache groups with incremental autorefresh where the interval value is 7 seconds. 

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

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

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

The following sections describe details when configuring a select limit for static read-only cache groups with incremental autorefresh.