Automatically Refreshing a Cache Group

AUTOREFRESH Cache Group Attribute Overview

The AUTOREFRESH cache group attribute can be specified when creating a read-only cache group or a user managed cache group using a CREATE CACHE GROUP statement.

AUTOREFRESH specifies that committed changes on cached Oracle Database tables are automatically refreshed to the TimesTen cache tables. Autorefresh is defined by default on read-only cache groups.

The following are the default settings of the autorefresh attributes:

The autorefresh mode is incremental.
The autorefresh interval is 5 minutes.
The autorefresh state is PAUSED.

If you create a unique index on a cache group with the AUTOREFRESH cache group attribute, the index is changed to a non-unique index to avoid a constraint violation. A constraint violation could occur with a unique index because conflicting updates could occur in the same statement processing on the Oracle Database table, while each row update is processed separately in TimesTen. If the unique index exists on the Oracle Database table that is being cached, then uniqueness is enforced on the Oracle Database table and does not need to be verified again in TimesTen.

The following sections describe each of the autorefresh attributes:

Autorefresh Mode Attribute Settings

You can set the autorefresh mode to designate how the automatic refresh is to perform.

TimesTen supports two autorefresh mode settings:

FULL: All cache tables are automatically refreshed, based on the cache group's autorefresh interval, by unloading all their rows and then reloading from the cached Oracle Database tables.

There is no overhead when using full autorefresh mode, but there may be performance implications.
INCREMENTAL: Committed changes on cached Oracle Database tables are automatically refreshed to the TimesTen cache tables based on the cache group's autorefresh interval.

There is overhead when using incremental autorefresh mode, but the performance is better than when using full autorefresh.

Some applications choose incremental autorefresh instead of full autorefresh mode for performance reasons. A full autorefresh can affect performance because:

More rows are refreshed with a full autorefresh.
A full autorefresh runs within a single transaction with no parallelism.

Even if you use incremental autorefresh on your cache group, the first load is a full refresh. In addition, TimesTen may perform a full autorefresh for recovery for certain error scenarios.

Note:

You can disallow full autorefresh with the DisableFullAutorefresh cache configuration parameter. See Disabling Full Autorefresh for Cache Groups.

When using incremental autorefresh mode, committed changes on cached Oracle Database tables are tracked in change log tables in the Oracle database. Because incremental autorefresh tracks committed changes on the Oracle database, incremental autorefresh mode incurs some overhead to refresh the cache group for each committed update on the cached Oracle Database tables. Under certain circumstances, it is possible for some change log records to be deleted (truncated) from the change log table before they are automatically refreshed to the TimesTen cache tables. If this occurs, TimesTen initiates a full automatic refresh on the cache group.

See Managing the Cache Administration User's Tablespace for information on how to configure an action to take when the tablespace that the change log tables reside in becomes full.

See Managing a Cache Environment With Oracle Database Objects for information on the change log tables in the Oracle Database.

The change log table on the Oracle database does not have column-level resolution because of performance reasons. Thus, the autorefresh operation updates all of the columns in a row. XLA reports that all of the columns in the row have changed even if the data did not actually change in each column. See XLA and TimesTen Event Management in the Oracle TimesTen In-Memory Database C Developer's Guide or Using JMS/XLA for Event Management in the Oracle TimesTen In-Memory Database Java Developer's Guide.

If you have a dynamic read-only cache group with autorefresh, you can reduce contention and improve performance. See Reducing Contention for Dynamic Read-Only Cache Groups With Incremental Autorefresh and Reducing Lock Contention for Read-Only Cache Groups With Autorefresh and Dynamic Load and Options for Reducing Contention Between Autorefresh and Dynamic Load Operations.

Autorefresh Interval and State Settings

The autorefresh interval determines how often autorefresh operations occur in minutes, seconds or milliseconds.

Cache groups with the same autorefresh interval are refreshed within the same transaction. 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.

In TimesTen Classic, you can manually initiate an immediate autorefresh operation with the ttCacheAutorefresh built-in procedure. See ttCacheAutorefresh in Oracle TimesTen In-Memory Database Reference.

The autorefresh state can be set to ON, OFF, or PAUSED.

ON: Autorefresh operations are scheduled by TimesTen when the cache group's autorefresh state is ON.
OFF: When the cache group's autorefresh state is OFF, committed changes on the cached Oracle Database tables are not tracked. When you change the state from OFF to ON, a full autorefresh is performed.
PAUSED: When the cache group's autorefresh state is PAUSED, committed changes on the cached Oracle Database tables are tracked in the Oracle database, but are not automatically refreshed to the TimesTen cache tables until the state is changed to ON.

By default, a cache group is created with autorefresh state set to PAUSED. This provides you a choice of how and when the initial full load is performed.

If the data in the Oracle database is large, then an initial full load of the cache group can prove to be time consuming. The recommended option is to run a manual load with parallelism with the LOAD CACHE GROUP... PARALLEL statement. The autorefresh state automatically changes to ON after the initial load completes.
If the data on the Oracle database is small, change the state to ON with an ALTER CACHE GROUP. Changing the state to ON when an initial load has not yet been performed causes the initial load to be performed and autorefresh operations to start.

After the initial load is completed, you can change the state to PAUSED at any time. When you change the state to ON, then incremental autorefresh resumes for static cache groups that were created with incremental autorefresh.

If the data on the Oracle database is too large to perform an initial full load, you can disable all full load operations. See Disabling Full Autorefresh for Cache Groups.

Restrictions for Autorefresh

There are restrictions when using the AUTOREFRESH cache group attribute.

TimesTen Scaleout only supports static read-only cache groups with incremental autorefresh. See Using Cache Groups in TimesTen Scaleout in the Oracle TimesTen In-Memory Database Scaleout User's Guide.
A FLUSH CACHE GROUP statement cannot be issued on the cache group.

See Flushing a User Managed Cache Group.
A TRUNCATE TABLE statement issued on a cached Oracle Database table is not automatically refreshed to the TimesTen cache table. Before issuing a TRUNCATE TABLE statement on a cached Oracle Database table, use an ALTER CACHE GROUP statement to change the autorefresh state of the cache group that contains the cache table to PAUSED.

See Altering a Cache Group to Change the AUTOREFRESH Mode, Interval or State.

After issuing the TRUNCATE TABLE statement on the cached Oracle Database table, use a REFRESH CACHE GROUP statement to manually refresh the cache group.
A LOAD CACHE GROUP statement can only be issued if the cache tables are empty, unless the cache group is dynamic.

See Manually Loading and Refreshing a Cache Group and Creating a Dynamic Cache Group With the DYNAMIC Keyword.
The autorefresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement on the cache group, unless the cache group is dynamic. If the cache group is dynamic, the autorefresh state must be PAUSED or ON.
The LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic. If the cache group is dynamic, the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.

See Using a WHERE Clause.
The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement on the cache group. The REFRESH CACHE GROUP statement cannot contain a WHERE clause.
All tables and columns referenced in WHERE clauses when creating, loading or unloading the cache group must be fully qualified. For example:

owner.table_name and owner.table_name.column_name
To use the AUTOREFRESH cache group attribute in a user managed cache group, all of the cache tables must be specified with the PROPAGATE cache table attribute or all of the cache tables must be specified the READONLY cache table attribute.
You cannot specify the AUTOREFRESH cache group attribute in a user managed cache group that contains cache tables that explicitly use the NOT PROPAGATE cache table attribute.
The AUTOREFRESH cache table attribute cannot be used when caching Oracle Database materialized views in a user managed cache group.
LRU aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.

See LRU Aging in TimesTen Classic.
If you want to use replication with a static cache group with autorefresh on TimesTen Classic, you can only use an active standby pair replication scheme. Any other type of replication scheme is not allowed with a static cache group with autorefresh on TimesTen Classic.

Altering a Cache Group to Change the AUTOREFRESH Mode, Interval or State

After creating a cache group with autorefresh, you can use ALTER CACHE GROUP statement to change autorefresh mode, interval or state.

In TimesTen Classic, you can change the cache group's autorefresh mode, interval or state.
In TimesTen Scaleout, you can only change the cache group's autorefresh state.

You cannot use ALTER CACHE GROUP to instantiate automatic refresh for a cache group that was originally created without autorefresh defined.

If you change a cache group's autorefresh state to OFF or drop a cache group that has an autorefresh operation in progress:

The autorefresh operation stops if the setting of the LockWait connection attribute is greater than 0. The ALTER CACHE GROUP or DROP CACHE GROUP statement preempts the autorefresh operation.
The autorefresh operation continues if the LockWait connection attribute is set to 0. The ALTER CACHE GROUP or DROP CACHE GROUP statement is blocked until the autorefresh operation completes or the statement fails with a lock timeout error.

The following example alters the autorefresh attributes of a cache group in TimesTen Classic. These statements change the autorefresh mode, interval and state of the customer_orders cache group:

ALTER CACHE GROUP customer_orders SET AUTOREFRESH MODE FULL;
ALTER CACHE GROUP customer_orders SET AUTOREFRESH INTERVAL 30 SECONDS;
ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE ON;

TimesTen returns asynchronously after executing the ALTER CACHE GROUP statement. However, there may be a delay for the cache agent to implement the change for the new state, mode or interval.

Manually Creating Oracle Database Objects for Cache Groups With Autorefresh

There are certain procedures you need to do if you created the Oracle Database objects used to enforce the predefined behaviors of a cache group with autorefresh with the initCacheAdminSchema.sql script.

See The initCacheAdminSchema.sql Script.

Set the autorefresh state to OFF when creating the cache group.
Run the ttIsql utility's cachesqlget command with the INCREMENTAL_AUTOREFRESH option and the INSTALL flag as the TimesTen cache administration user. This command generates a SQL*Plus script used to create a cache log table and a trigger in the Oracle database for each Oracle Database table that is cached in the autorefresh cache group. These Oracle Database objects track updates on the cached Oracle Database tables so that the updates can be automatically refreshed to the cache tables.

Note:

The ttCacheSQLGet built-in procedure provides the same functionality as the ttISql cachesqlget command.
Use SQL*Plus to run the script generated by the ttIsql utility's cachesqlget command as the sys user.
Run an ALTER CACHE GROUP statement to change the autorefresh state of the cache group to PAUSED.

The following examples shows how to create a read-only cache group when Oracle Database objects are created with the initCacheAdminSchema.sql script.

The first statement creates a read-only cache group customer_orders with the autorefresh state set to OFF. The SQL*Plus script generated by the ttIsql utility's cachesqlget command is saved to the /tmp/obj.sql file. The last statement changes the autorefresh state of the cache group to PAUSED.

CREATE READONLY CACHE GROUP customer_orders
AUTOREFRESH STATE OFF
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
sales.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES sales.customer(cust_num));

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> cachesqlget INCREMENTAL_AUTOREFRESH customer_orders INSTALL /tmp/obj.sql;
Command> exit

% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/obj
SQL> exit

ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE PAUSED;

See ttISql and ttCacheSQLGet in Oracle TimesTen In-Memory Database Reference.

Initiating an Immediate Autorefresh in TimesTen Classic

In TimesTen Classic, if the Oracle Database tables have been updated with data that needs to be applied to cache tables without waiting for the next autorefresh operation, you can call the ttCacheAutorefresh built-in procedure.

The ttCacheAutorefresh built-in procedure initiates an immediate refresh operation and resets the autorefresh cycle to start at the moment you invoke ttCacheAutorefresh.

The refresh operation is full or incremental depending on how the cache group is configured. The autorefresh state must be ON when ttCacheAutorefresh is called.

The autorefresh operation typically refreshes all cache groups sharing the same refresh interval in one transaction in order to preserve transactional consistency across these cache groups. Therefore, although you specify a specific cache group when you call ttCacheAutorefresh, the autorefresh operation occurs in one transaction for all cache groups that share the autorefresh interval with the specified cache group. If there is an existing transaction with table locks on objects that belong to the affected cache groups, ttCacheAutofresh returns an error without taking any action.

You can choose to run ttCacheAutorefresh asynchronously (the default) or synchronously. In synchronous mode, ttCacheAutorefresh returns an error if the refresh operation fails.

After calling ttCacheAutorefresh, you must commit or roll back the transaction before subsequent work can be performed.

This example calls ttCacheAutorefresh for the ttuser.western_customers cache group, using asynchronous mode.

Command> call ttCacheAutorefresh('ttuser', 'western_customers');

Disabling Full Autorefresh for Cache Groups

If performance is a concern, you can set the DisableFullAutorefresh cache configuration parameter to 1 to disallow full autorefresh requests for all cache groups defined with incremental autorefresh.

If you do disallow full autorefresh, then the initial load for each cache group requires a manual load since the initial load requires a full refresh.

You can disable full autorefresh using the DisableFullAutorefresh cache configuration parameter in both TimesTen Classic and TimesTen Scaleout.

Note:

The default value is 0 for the DisableFullAutorefresh cache configuration parameter, which specifies full autorefresh behavior. Full autorefresh is only supported on TimesTen Classic.

call ttCacheConfig('DisableFullAutorefresh',,,'1');

You can query the current value of the DisableFullAutorefresh parameter.

call ttCacheConfig('DisableFullAutorefresh');

If a full autorefresh is triggered for a cache group, TimesTen changes the cache group status to disabled. After which, all autorefresh operations cease on the cache group. You are notified of this action with a message logged in both the daemon and user log files. See Error, Warning, and Informational Messages in the Oracle TimesTen In-Memory Database Operations Guide.

The TimesTen database status is set to recovering when at least one of its cache groups have an autorefresh status of disabled or recovering. You can check the state of a database and cache group with the ttCacheDbCgStatus built-in procedure. The following example shows that:

Recovering: Some or all the cache groups with the AUTOREFRESH attribute in the database are being resynchronized with the Oracle database server. The status of at least one cache group is recovering.
Disabled: The cg1 cache group is disabled.

Command> call ttCacheDbCgStatus('ttuser','cg1');
< recovering, disabled >
1 row found.

When you set the DisableFullAutorefresh cache configuration parameter to 1, then the DeadDbRecovery cache configuration parameter automatically changes to Manual. TimesTen restores the original setting for the DeadDbRecovery cache configuration parameter if you change the DisableFullAutorefresh cache configuration parameter to 0.

If the autorefresh status of a cache group is either disabled or dead, its cache tables are no longer being automatically refreshed when updates are committed on the cached Oracle Database tables. The cache group must be recovered in order to resynchronize the cache tables with the cached Oracle Database tables.

For each cache group whose autorefresh status is disabled, a REFRESH CACHE GROUP statement must be issued in order to resume autorefresh operations for these cache groups.
For each dynamic cache group whose autorefresh status is disabled, an UNLOAD CACHE GROUP statement must be issued in order to resume autorefresh operations for these cache groups.
See Impact of Failed Autorefresh Operations on TimesTen Databases for details on how to specify recovery when the autorefresh status of a cache group is dead.

The following example shows the steps to manually refresh a disabled cache group.

Pause autorefresh for the cache group and return the cache group status to OK with the ALTER CACHE GROUP SET AUTOREFRESH STATE PAUSED statement.
Manually request a full refresh with the REFRESH CACHE GROUP statement (optionally, with parallelism).

ALTER CACHE GROUP cg_static SET AUTOREFRESH STATE PAUSED;
REFRESH CACHE GROUP cg_static COMMIT EVERY 500 ROWS PARALLEL 2;

Perform the following to reload a dynamic cache group:

To return the cache group status to OK, pause autorefresh for the cache group with the ALTER CACHE GROUP SET AUTOREFRESH STATE PAUSED statement.
Unload the disabled dynamic cache group with the UNLOAD CACHE GROUP statement.
Optionally, you can load the cache group with the LOAD CACHE GROUP statement (optionally, with parallelism) or initiate a dynamic load. See Dynamic Cache Groups.

The following example reloads the cg dynamic cache group:

ALTER CACHE GROUP cg_dynamic SET AUTOREFRESH STATE PAUSED;
UNLOAD CACHE GROUP cg_dynamic COMMIT EVERY 500 ROWS;
LOAD CACHE GROUP cg_dynamic COMMIT EVERY 500 ROWS PARALLEL 2;

Loading and Refreshing a Static Cache Group With Autorefresh

If the autorefresh state of a static cache group is PAUSED, the autorefresh state is changed to ON after a LOAD CACHE GROUP or REFRESH CACHE GROUP statement issued on the cache group completes.

The following restrictions apply when manually loading or refreshing a static cache group with autorefresh:

A LOAD CACHE GROUP statement can only be issued if the cache tables are empty.
The autorefresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement.
The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement.
A LOAD CACHE GROUP statement cannot contain a WHERE clause.
A LOAD CACHE GROUP or REFRESH CACHE GROUP statement cannot contain a WITH ID clause.
A REFRESH CACHE GROUP statement cannot contain a WHERE clause.
All tables and columns referenced in a WHERE clause when loading the cache group must be fully qualified. For example:

owner.table_name and owner.table_name.column_name

When an autorefresh operation occurs on a static cache group, all committed inserts, updates and deletes on the cached Oracle Database tables since the last autorefresh cycle are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle Database tables have been updated or deleted.

Loading and Refreshing a Dynamic Cache Group With Autorefresh

If the autorefresh state of a dynamic cache group is PAUSED, the autorefresh state is changed to ON automatically after specific events occur.

Its cache tables are initially empty, and then a dynamic load, a LOAD CACHE GROUP or an unconditional REFRESH CACHE GROUP statement issued on the cache group completes.
Its cache tables are not empty, and then an unconditional REFRESH CACHE GROUP statement issued on the cache group completes.

If the autorefresh state of a dynamic cache group is PAUSED, the autorefresh state remains at PAUSED after any of the following events occur:

Its cache tables are initially empty, and then a REFRESH CACHE GROUP ... WITH ID statement issued on the cache group completes.
Its cache tables are not empty, and then a dynamic load, a REFRESH CACHE GROUP ... WITH ID, or a LOAD CACHE GROUP statement issued on the cache group completes.

For a dynamic cache group, an autorefresh operation is similar to a REFRESH CACHE GROUP statement that only refreshes committed updates and deletes on the cached Oracle Database tables since the last autorefresh cycle into the cache tables because only existing cache instances in the cache tables are refreshed. New cache instances are not loaded into the cache tables. To load new cache instances into the cache tables of a dynamic cache group, use a LOAD CACHE GROUP statement or perform a dynamic load operation. See Dynamic Cache Groups.

The following restrictions apply when manually loading or refreshing a dynamic cache group with automatic refresh:

The autorefresh state must be PAUSED or ON before you can issue a LOAD CACHE GROUP statement.
The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement.
A LOAD CACHE GROUP statement that contains a WHERE clause must include a COMMIT EVERY n ROWS clause after the WHERE clause.
A REFRESH CACHE GROUP statement cannot contain a WHERE clause.
All tables and columns referenced in a WHERE clause when loading the cache group must be fully qualified. For example:

owner.table_name and owner.table_name.column_name