Usage-Based Aging

Usage-based aging enables you to maintain the amount of memory used in a database within a specified threshold by removing the least recently used (LRU) data.

Define LRU aging for a new table by using the AGING LRU clause of the CREATE TABLE statement. Aging begins automatically if the aging state is ON.

You can use the ALTER TABLE statement to perform the following tasks:

Enable or disable the aging state on a table that has an aging policy defined by using the ALTER TABLE statement with the SET AGING {ON|OFF} clause.
Add an LRU aging policy to an existing table by using the ALTER TABLE statement with the ADD AGING LRU [ ON | OFF ] clause.
Drop aging on a table by using the ALTER TABLE statement with the DROP AGING clause.

Call the ttAgingScheduleNow built-in procedure to schedule when aging starts. See Scheduling When Aging Starts.

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

The row is inserted.
The row is used to build the result set of a SELECT statement.
The row has been flagged to be updated or deleted.
The row is used to build the result set of an INSERT SELECT statement.

To change aging from LRU to time-based on a table, first drop aging on the table by using the ALTER TABLE statement with the DROP AGING clause. Then add time-based aging by using the ALTER TABLE statement with the ADD AGING USE clause.

Note:

When you drop LRU aging or add LRU aging to tables that are referenced in commands, TimesTen marks the compiled commands invalid. The commands need to be recompiled.

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 initially uses the default thresholds for permanent memory in use. See Defining LRU Aging Based on Thresholds for Permanent Memory in Use.
LRU aging based on row thresholds for a set of tables. See Defining LRU Aging Based on Row Thresholds for Tables.

Both types of LRU aging can co-exist. Row threshold based aging takes precedence over permanent memory in use based aging. For each cycle after row based aging is processed, the current permanent space usage is used as the threshold for LRU aging of tables that use permanent memory in use threshold aging.

Defining LRU Aging Based on Thresholds for Permanent Memory in Use

You can define that LRU aging occurs based on set thresholds for the amount of permanent memory in use.

This method uses the PERM_IN_USE_SIZE metric to determine the thresholds for LRU aging across all tables. If the high threshold is reached, TimesTen deletes rows from all tables in order to reduce the permanent memory in use.

Call the ttAgingLRUConfig built-in procedure to specify the LRU aging attributes. The attribute values apply to all tables in the database that have an LRU aging policy. If you do not call the ttAgingLRUConfig built-in procedure, then the default values for the attributes are used.

The following table summarizes the LRU aging attributes:

LRU Aging Attribute	Description
`LowUsageThreshhold`	The percent of the database `PermSize` at which LRU aging is deactivated.
`HighUsageThreshhold`	The percent of the database `PermSize` at which LRU aging is activated.
`AgingCycle`	The number of minutes between aging cycles.

If you set a new value for AgingCycle after an LRU aging policy has already been defined, aging occurs based on the current time and the new cycle time. For example, if the original aging cycle is 15 minutes and LRU aging occurred 10 minutes ago, aging is expected to occur again in 5 minutes. However, if you change the AgingCycle parameter to 30 minutes, then aging occurs 30 minutes from the time you call the ttAgingLRUConfig procedure with the new value for AgingCycle.

Defining LRU Aging Based on Row Thresholds for Tables

After a table or cache group is enabled for LRU aging, you can use the ttAgingTableLRUConfig built-in procedure to configure that LRU aging occurs based on row thresholds for a set of tables.

In some situations, the permanent memory in use based aging may result in excessive purging of data from specific tables. You may find that using row based aging for such tables enables a better space management to be achieved. When you set LRU aging using the ttAgingTableLRUConfig built-in procedure, it sets thresholds based on rows for specified tables and on cache instances when applied to specified cache groups. You can limit the number of rows deleted from a specified table by setting row thresholds for the table.

Both types of LRU aging can co-exist. Table-based aging takes precedence over permanent memory in use based aging. Thus, a table configured with the attributes specified with the ttAgingTableLRUConfig built-in procedure is aged solely based on the specified row thresholds. For each cycle after row based aging is processed, the current permanent space usage is used as the threshold for LRU aging of tables that use permanent memory in use threshold aging.

The following table summarizes the LRU aging attributes:

If you have tables or cache group tables in a parent/child relationship, provide the parent table to ensure that cascade delete operations perform correctly.

LRU Aging Attribute	Description
`tblOwner`	TimesTen table or cache group owner.
`tblName`	Name of a table. Provide the cache root table for a cache group. LRU aging automatically deletes rows from child tables.
`LowRowsThreshhold`	The number of the rows at which LRU aging is deactivated. LRU aging stops when the number of rows reaches `LowRowsThreshold` rows in the table.
`HighRowsThreshhold`	The number of the rows at which LRU aging is activated. LRU aging starts when the number of rows reaches `HighRowsThreshold` rows in the table.
`AgingCycle`	The number of minutes between aging cycles. The defaults and behavior is the same as the `AgingCycle` in the `ttAgingLRUConfig` built-in procedure.

If both LowRowsThreshold and HighRowsThreshold are set to zero, table based LRU aging is disabled for this table. LRU aging on this table is switched to be based on the permanent memory in use threshold. See Defining LRU Aging Based on Thresholds for Permanent Memory in Use.

See ttAgingTableLRUConfig in the Oracle TimesTen In-Memory Database Reference.

The following example sets the aging threshold for rows in the user1.table1 table to a low threshold of 10K rows and a high threshold of 100K rows. The aging cycle is set to run at the default of once every minute.

Command> Call ttAgingTableLRUConfig('user1', 'table1', 10000, 100000);
< USER1, TABLE1, 10000, 100000, 1 >
1 row found.

The following example sets the aging threshold for rows in the user1.table1 table to a low threshold of 5K rows and a high threshold of 12K rows. The aging cycle is set to run once every two minutes.

Command> Call ttAgingTableLRUConfig('user1', 'table1', 5000, 12000, 2);
< USER1, TABLE1, 10000, 100000, 0 >
1 row found.

The following example turns off aging by setting the low and high thresholds to zero.

Command> Call ttAgingTableLRUConfig('user1', 'table1', 0, 0);
< USER1, TABLE1, 0, 0, 1 >
1 row found.

You can retrieve the threshold settings by running the built-in procedure with just the schema owner and table name.

Command> Call ttAgingTableLRUConfig('user1', 'table1');
< USER1, TABLE1, 10000, 100000, 1 >
1 row found.