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.
- See How to Determine Which Intervals Have a Particular Select Limit to determine which intervals have a select limit.
-
See Retrieving Statistics on Autorefresh Transactions to retrieve statistics for incremental autorefresh transactions for this autorefresh interval. This determines how a select limit for a particular autorefresh interval is performing.