Performing Operations on the Read-Only Cache Group

Automatically Refresh Updates on the Cached Oracle Database Table

You specify incremental autorefresh with the AUTOREFRESH INTERVAL cache group attribute when creating a read-only cache group using a CREATE CACHE GROUP statement. By default, autorefresh is defined on read-only cache groups.

This example shows an autorefresh interval of 5 seconds defined for the readcache cache group. The default mode is INCREMENTAL and the default state is PAUSED.

Command> CREATE READONLY CACHE GROUP readcache
        AUTOREFRESH INTERVAL 5 SECONDS
        FROM sales.readtab
        (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));

Autorefresh considerations:

Autorefresh state: Since the autorefresh state is set to PAUSED by default, you can perform a manual load as the initial load of the cache group. This is the recommended method. See Manually Load the Cache Group in Oracle TimesTen In-Memory Database Cache Guide.

However, if you set the autorefresh state to ON when creating the cache group or anytime after cache group creation but before a manual load, then a full autorefresh is automatically requested to perform the initial load of the cache group. There can be risks of performing a full autorefresh for large cache groups. See Disabling Full Autorefresh in Oracle TimesTen In-Memory Database Cache Guide.
Autorefresh mode and interval: With AUTOREFRESH MODE INCREMENTAL INTERVAL defined, committed changes on cached Oracle database tables are automatically refreshed to the TimesTen cache tables based on the autorefresh interval of the cache group. Incremental autorefresh uses Oracle database objects to track committed changes on cached Oracle database tables. Transactional consistency is maintained for cache groups belonging to the same autorefresh interval.

The autorefresh interval determines how often autorefresh operations occur in minutes, seconds or milliseconds. You can only set the autorefresh interval during cache group creation. Cache groups with the same autorefresh interval are refreshed within the same transaction and are managed by a single cache agent. You can improve the performance of your cache groups by placing them in separate autorefresh intervals, which achieves parallelism.

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.

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.

See Managing the Autorefresh State.

The following example demonstrates how changes to the Oracle database cache tables are automatically refreshed to the cache groups on TimesTen.

Use SQL*Plus, as the Oracle database schema user, to insert a new row, delete an existing row, and update an existing row in the Oracle Database readtab table, and commit the changes:

SQL> INSERT INTO readtab VALUES (3, 'Welcome');
1 row created.
SQL> DELETE FROM readtab WHERE keyval=2;
1 row deleted.
SQL> UPDATE readtab SET str='Hi' WHERE keyval=1;
1 row updated.
SQL> COMMIT;
Commit complete.

Since the read-only cache group was created specifying incremental autorefresh with an interval of 5 seconds, the sales.readtab cache table in the readcache cache group is automatically refreshed after 5 seconds with the committed changes on the cached Oracle database sales.readtab table as shown in Figure 12-4.

Figure 12-4 Automatically Refresh the TimesTen Cache Table with Oracle Database Updates

Description of "Figure 12-4 Automatically Refresh the TimesTen Cache Table with Oracle Database Updates"

As the TimesTen cache administration user, use the ttIsql utility to query the contents of the sales.readtab cache table after the readcache cache group has been automatically refreshed with the committed changes on the cached Oracle database table:

Command> SELECT * FROM sales.readtab;
< 1, Hi >
< 3, Welcome >
2 rows found.

See Automatically Refreshing a Cache Group in Oracle TimesTen In-Memory Database Cache Guide.

Managing the Autorefresh State

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.
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.

You can set the autorefresh state when creating or altering the cache group. The following example modifies the autorefresh state with the ALTER CACHE GROUP statement.

Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE PAUSED;
Command> cachegroups;
 
Cache Group CACHEADMIN.READCACHE:
 
  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined
 
  Root Table: SALES.READTAB
  Table Type: Read Only
 
1 cache group found.

Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE ON;
Command> cachegroups;
 
Cache Group CACHEADMIN.READCACHE:
 
  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined
 
  Root Table: SALES.READTAB
  Table Type: Read Only
 
1 cache group found.

See Automatically Refreshing a Cache Group in Oracle TimesTen In-Memory Database Cache Guide.

Disabling Full Autorefresh

There can be risks of performing a full autorefresh for large cache groups as loading large amounts of data can overwhelm either temporary space or the Oracle cache administration user tablespace.

TimesTen requests a full autorefresh:

If you set the autorefresh state to ON when creating the cache group or anytime after cache group creation but before a manual load, the first load of the cache group is a full autorefresh.
TimesTen automatically performs a full autorefresh when recovery is requested.

However, if performance is a concern, you can disallow full autorefresh requests for all cache groups defined with incremental autorefresh by setting the DisableFullAutorefresh cache configuration parameter to 1. If you do so, both the initial load and any recovery requires a manual load.

Command> call ttCacheConfig('DisableFullAutorefresh',,,'1');
< DisableFullAutorefresh, <NULL>, <NULL>, 1 >
1 row found.

See Disabling Full Autorefresh for Cache Groups in Oracle TimesTen In-Memory Database Cache Guide.

Manually Load the Cache Group

As the TimesTen cache administration user, you perform the initial load of the cache group with the LOAD CACHE GROUP statement. The LOAD CACHE GROUP statement only loads committed inserts on the cached Oracle database tables into the TimesTen cache tables.

New cache instances are loaded into the cache tables, but cache instances that already exist in the cache tables are not updated or deleted even if the corresponding rows in the cached Oracle database tables have been updated or deleted. A manual load operation is primarily used to initially populate a cache group.

A manual load on a read-only cache group with autorefresh can only occur when the autorefresh state is PAUSED. Once the manual load completes, the autorefresh state automatically changes from PAUSED to ON. After which, incremental autorefresh starts.

If you know that there is a large amount of data to load, you can portion the data by specifying the COMMIT EVERY n ROWS clause and request parallel loading across several threads by specifying the PARALLEL clause.

The following example loads the contents of the Oracle database sales.readtab table into the TimesTen sales.readtab cache table in the readcache cache group. The example commits every 256 rows and specifies 3 threads to run concurrently.

Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS PARALLEL 3;
2 cache instances affected.

Figure 12-5 shows that the Oracle Database data is loaded into the sales.readtab cache table.

Figure 12-5 Loading a Read-Only Cache Group

Description of "Figure 12-5 Loading a Read-Only Cache Group"

Grant the SELECT privilege on the sales.readtab cache table to the TimesTen cache administration user so that this user can issue a SELECT query on this table.

Command> GRANT SELECT ON sales.readtab TO cacheadmin;

Query the contents of sales.readtab cache table.

Command> SELECT * FROM sales.readtab;
< 1, Hello >
< 2, World >
2 rows found.

See Loading and Refreshing a Cache Group in Oracle TimesTen In-Memory Database Cache Guide.

Manually Refresh the Read-Only Cache Group

You can also manually refresh the read-only cache group using the REFRESH CACHE GROUP SQL statement. REFRESH CACHE GROUP replaces cache instances in the TimesTen cache tables with the most current data from the cached Oracle database tables including cache instances that are already exist in the cache tables.

A refresh operation is primarily used to update the contents of a cache group with committed changes on the cached Oracle database tables after the cache group has been initially populated.

For a static cache group, a refresh operation is equivalent to issuing an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement on the cache group. In effect, all committed inserts, updates and deletes on the cached Oracle database tables 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.

The following example refreshes cache instances in the TimesTen cache tables within the readcache cache group from the cached Oracle database tables:

Command> REFRESH CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.

See Loading and Refreshing a Cache Group in Oracle TimesTen In-Memory Database Cache Guide and Unloading the Cache Group.

Unloading the Cache Group

You can delete some or all cache instances from the cache tables in a cache group with the UNLOAD CACHE GROUP statement. Unlike the DROP CACHE GROUP statement, the cache tables themselves are not dropped when a cache group is unloaded.

The following example unloads all cache instances from all cache tables in the readcache cache group. A commit frequency is specified, so the operations is performed over several transactions by committing every 256 rows:

Command> UNLOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.

See Unloading a Cache Group in Oracle TimesTen In-Memory Database Cache Guide.