Implementing Aging in a Cache Group for TimesTen Classic

You can define an aging policy for a cache group in TimesTen Classic that specifies the aging type, the aging attributes, and the aging state. TimesTen Classic supports two aging types, least recently used (LRU) aging and time-based aging.

LRU aging deletes the least recently used or referenced data based on a specified database usage range. Time-based aging deletes data based on a specified data lifetime and frequency of the aging process. You can use both LRU and time-based aging in the same TimesTen database, but you can define only one aging policy for a particular cache group.

An aging policy is specified in the cache table definition of the root table in a CREATE CACHE GROUP statement and applies to all cache tables in the cache group because aging is performed at the cache instance level. When rows are deleted from the cache tables by aging out, the rows in the cached Oracle Database table are not deleted.

You can add an aging policy to a cache group by using an ALTER TABLE statement on the root table. You can change the aging policy of a cache group by using ALTER TABLE statements on the root table to drop the existing aging policy and then add a new aging policy.

This section describes cache group definitions that contain an aging policy.

• LRU Aging in TimesTen Classic

• Time-Based Aging in TimesTen Classic

• Manually Scheduling an Aging Process in TimesTen Classic

• Configuring a Sliding Window in TimesTen Classic

LRU Aging in TimesTen Classic

LRU aging enables you to maintain the amount of memory used in a TimesTen database within a specified threshold by deleting the least recently used data. LRU aging can be defined for all cache group types except static cache groups with autorefresh enabled. LRU aging is defined by default on dynamic cache groups.

Define an LRU aging policy for a cache group by using the AGING LRU clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.

The following example defines an LRU aging policy on the AWT cache group new_customers:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
AGING LRU ON;

There are two LRU aging policies:

• LRU aging based on set thresholds for the amount of permanent memory in use. This is the default. Once you create (or alter) a table to use LRU aging, the LRU aging policy defaults to using the default thresholds for permanent memory in use. See Defining LRU Aging Based on Thresholds for Permanent Memory in Use in the Oracle TimesTen In-Memory Database Operations Guide.

• LRU aging based on row thresholds for a specified root tables of your cache groups. See Defining LRU Aging Based on Row Thresholds for Tables in the Oracle TimesTen In-Memory Database Operations Guide.

Both types of LRU aging can co-exist. Row threshold based aging takes precedence over permanent memory in use based aging.

If a row has been accessed or referenced since the last aging cycle, it is not eligible for LRU aging in the current aging cycle. A row is considered to be accessed or referenced if at least one of the following is true:

• The row is used to build the result set of a SELECT or an INSERT ... SELECT statement.

• The row has been marked to be updated or deleted in a pending transaction.

In a multiple-table cache group, if a row in a child table has been accessed or referenced since the last aging cycle, then neither the related row in the parent table nor the row in the child table is eligible for LRU aging in the current aging cycle.

The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining an LRU aging policy on a cache group:

• Change the aging state of a cache group by specifying the root table and using the SET AGING clause.

• Add an LRU aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING LRU clause.

• Drop the LRU aging policy on a cache group by specifying the root table and using the DROP AGING clause.

To change the aging policy of a cache group from LRU to time-based, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the LRU aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING USE clause to add a time-based aging policy.

You must stop the cache agent before you add, alter or drop an aging policy on a cache group with autorefresh.

Time-Based Aging in TimesTen Classic

Time-based aging deletes data from a cache group based on the aging policy's specified data lifetime and frequency. Time-based aging can be defined for all cache group types in TimesTen Classic.

The data lifetime defines the minimum age of data within the table. The comparison of the time is based on the timestamp, so data may not become a candidate for aging until longer than the specified lifetime (but never less that the specified lifetime).

Define a time-based aging policy for a cache group by using the AGING USE clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.

On the Oracle Database:

The following example are the definitions of the Oracle Database tables that are to be cached in the AWT cache group. The Oracle Database tables are owned by the schema user sales.

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

CREATE TABLE order_item
(orditem_id NUMBER(12) NOT NULL PRIMARY KEY,
 ord_num    NUMBER(10),
 prod_num   VARCHAR2(6),
 quantity   NUMBER(3));

The Oracle cache administration user must be granted the SELECT, INSERT, UPDATE, and DELETE privileges on any cached tables. In this example, the table is the sales.orders and sales.order_item tables.

See Required Privileges for Cache Administration User for Cache Operations for all required privileges for different activities.

On the Oracle database as an administrator, grant the following privileges:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.orders TO cacheadmin;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.order_item TO cacheadmin;

On the TimesTen database:

The following example defines a time-based aging policy on the AWT cache group ordered_items:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP ordered_items
FROM 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))
AGING USE when_placed LIFETIME 45 DAYS CYCLE 60 MINUTES ON,
sales.order_item
 (orditem_id NUMBER(12) NOT NULL,
  ord_num    NUMBER(10),
  prod_num   VARCHAR2(6),
  quantity   NUMBER(3),
  PRIMARY KEY(orditem_id),
  FOREIGN KEY(ord_num) REFERENCES sales.orders(ord_num));

Cache instances that are greater than 45 days old based on the difference between the current system timestamp and the timestamp in the when_placed column of the sales.orders table are candidates for aging. The aging process checks every 60 minutes to see if there are cache instances that can be automatically aged out or deleted from the cache tables.

The AGING USE clause requires the name of a non-nullable TIMESTAMP or DATE column used for time-based aging. We refer to this column as the timestamp column.

For each row, the value in the timestamp column stores the date and time when the row was most recently inserted or updated. The values in the timestamp column is maintained by your application. If the value of this column is unknown for particular rows and you do not want those rows to be aged out of the table, define the timestamp column with a large default value.

You can create an index on the timestamp column to optimize performance of the aging process.

You cannot add a column to an existing table and then use that column as the timestamp column because added columns cannot be defined as non-nullable. You cannot drop the timestamp column from a table that has a time-based aging policy defined.

Specify the lifetime in days, hours, minutes or seconds after the LIFETIME keyword in the AGING USE clause.

The value in the timestamp column is subtracted from the current system timestamp. The result is then truncated to the specified lifetime unit (day, hour, minute, second) and compared with the specified lifetime value. If the result is greater than the lifetime value, the row is a candidate for aging.

After the CYCLE keyword, specify the frequency in which aging occurs in days, hours, minutes or seconds. The default aging cycle is 5 minutes. If you specify an aging cycle of 0, aging is continuous.

The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining a time-based aging policy on a cache group:

• Change the aging state of a cache group by specifying the root table and using the SET AGING clause.

• Change the lifetime by specifying the root table and using the SET AGING LIFETIME clause.

• Change the aging cycle by specifying the root table and using the SET AGING CYCLE clause.

• Add a time-based aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING USE clause.

• Drop the time-based aging policy on a cache group by specifying the root table and using the DROP AGING clause.

To change the aging policy of a cache group from time-based to LRU, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the time-based aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING LRU clause to add an LRU aging policy.

You must stop the cache agent before you add, alter or drop an aging policy on a cache group with autorefresh.

Manually Scheduling an Aging Process in TimesTen Classic

Use the ttAgingScheduleNow built-in procedure to manually start a one-time aging process on a specified table or on all tables that have an aging policy defined.

The aging process starts as soon as you call the built-in procedure unless there is already an aging process in progress. Otherwise the manually started aging process begins when the aging process that is in progress has completed. After the manually started aging process has completed, the start of the table's next aging cycle is set to the time when ttAgingScheduleNow was called if the table's aging state is ON.

The following example shows how the ttAgingScheduleNow built-in procedure starts a one-time aging process on the sales.orders table based on the time ttAgingScheduleNow is called:

Command> CALL ttAgingScheduleNow('sales.orders');

Rows in the sales.orders root table that are candidates for aging are deleted as well as related rows in the sales.order_item child table.

When you call the ttAgingScheduleNow built-in procedure, the aging process starts regardless of whether the table's aging state is ON or OFF. If you want to start an aging process on a particular cache group, specify the name of the cache group's root table when you call the built-in procedure. If the ttAgingScheduleNow built-in procedure is called with no parameters, it starts an aging process and then resets the start of the next aging cycle on all tables in the TimesTen database that have an aging policy defined.

Calling the ttAgingScheduleNow built-in procedure does not change the aging state of any table. If a table's aging state is OFF when you call the built-in procedure, the aging process starts, but it is not scheduled to run again after the process has completed. To continue aging a table whose aging state is OFF, you must call ttAgingScheduleNow again or change the table's aging state to ON.

To manually control aging on a cache group, disable aging on the root table by using an ALTER TABLE statement with the SET AGING OFF clause. Then call ttAgingScheduleNow to start an aging process on the cache group.

Configuring a Sliding Window in TimesTen Classic

You can use time-based aging to implement a sliding window for a cache group.

In a sliding window configuration, new rows are inserted into and old rows are deleted from the cache tables on a regular schedule so that the tables contain only the data that satisfies a specific time interval.

You can configure a sliding window for a cache group by using incremental autorefresh mode and defining a time-based aging policy. The autorefresh operation checks the timestamp of the rows in the cached Oracle Database tables to determine whether new data should be refreshed into the TimesTen cache tables. The system time and the time zone must be identical on the Oracle Database and TimesTen systems.

If the cache group does not use incremental autorefresh mode, you can configure a sliding window by using a LOAD CACHE GROUP, REFRESH CACHE GROUP, or INSERT statement, or a dynamic load operation to bring new data into the cache tables.

The following example configures a sliding window on the read-only cache group recent_shipped_orders:

CREATE READONLY CACHE GROUP recent_shipped_orders
AUTOREFRESH MODE INCREMENTAL INTERVAL 1440 MINUTES STATE ON
FROM 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))
AGING USE when_shipped LIFETIME 30 DAYS CYCLE 24 HOURS ON;

New data in the sales.orders cached Oracle Database table are automatically refreshed into the sales.orders TimesTen cache table every 1440 minutes. Cache instances that are greater than 30 days old based on the difference between the current system timestamp and the timestamp in the when_shipped column are candidates for aging. The aging process checks every 24 hours to see if there are cache instances that can be aged out of the cache tables. Therefore, this cache group stores orders that have been shipped within the last 30 days.

The autorefresh interval and the lifetime used for aging determine the duration that particular rows remain in the cache tables. It is possible for data to be aged out of the cache tables before it has been in the cache tables for its lifetime. For example, for a read-only cache group if the autorefresh interval is 3 days and the lifetime is 30 days, data that is already 3 days old when it is refreshed into the cache tables is deleted after 27 days because aging is based on the timestamp stored in the rows of the cached Oracle Database tables that gets loaded into the TimesTen cache tables, not when the data is refreshed into the cache tables.