Managing the Cache Administration User's Tablespace

Defragmenting Change Log Tables in the Tablespace

Prolonged use or a heavy workload of the change log tables for cache groups with autorefresh can result in fragmentation of the tablespace.

In order to prevent degradation of the tablespace from fragmentation of the change log tables, TimesTen calculates the percentage of fragmentation for the change log tables as a ratio of used space to the total size of the space. If this ratio falls below a defined threshold, TimesTen alerts you of the necessity for defragmentation of the change log tables by logging a message. By default, this threshold is set to 40%.

You can configure what the fragmentation threshold should be with the ttCacheConfig built-in procedure.

Note:

Messages are logged to the user and support error logs. For details, see Error, Warning, and Informational Messages in the Oracle TimesTen In-Memory Database Operations Guide.

To set the fragmentation threshold, call the ttCacheConfig built-in procedure as the TimesTen cache administration user from any of the TimesTen databases that cache data from the Oracle database. Pass the AutoRefreshLogFragmentationWarningPCT string to the Param parameter and the threshold setting as a numeric string to the Value parameter.

To set the time interval for how often to calculate the fragmentation percentage, call the ttCacheConfig built-in procedure as the TimesTen cache administration user from any of the TimesTen databases that cache data from the Oracle database. Pass the AutorefreshLogMonitorInterval string to the Param parameter and the time interval (in seconds) as a numeric string to the Value parameter.

Note:

Do not pass in any values to the tblOwner and tblName parameters as they are not applicable to setting the fragmentation threshold or the time interval for the threshold calculation.

In the following example, the fragmentation threshold is set to 50% and the time interval for calculating the fragmentation threshold is set to 3600 seconds:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheConfig('AutoRefreshLogFragmentationWarningPCT',,,'50');
< AutoRefreshLogFragmentationWarningPCT, <NULL>, <NULL>, 50 >
1 row found.
Command> CALL ttCacheConfig('AutorefreshLogMonitorInterval',,,'3600');
< AutorefreshLogMonitorInterval, <NULL>, <NULL>, 3600 >
1 row found.

To determine the current fragmentation threshold setting, call ttCacheConfig passing the AutoRefreshLogFragmentationWarningPCT string to the Param parameter:

Command> CALL ttCacheConfig('AutoRefreshLogFragmentationWarningPCT');
< AutoRefreshLogFragmentationWarningPCT, <NULL>, <NULL>, 50 >

You can either manually initiate defragmentation or configure TimesTen to automatically defragment. To configure what action is taken when the ratio falls below the fragmentation threshold, call the ttCacheConfig built-in procedure with the AutoRefreshLogDeFragmentAction string to the Param parameter and the desired action as the Value parameter as follows:

Note:

Do not pass in any values to the tblOwner and tblName parameters as they are not applicable to setting the defragmentation action.

Manual. This is the default. No action is taken to defragment the change log tables. Any defragmentation must be performed manually by running the ttCacheAutoRefreshLogDeFrag built-in procedure. See Manually Defragmenting the Change Log Tables for Cache Groups With Autorefresh.
Compact: TimesTen defragments the change log tables.
CompactAndReclaim: TimesTen defragments the change log tables and reclaims the space.

Note:

When reclaiming space, the change log table is briefly locked, which temporarily suspends writing into the base table.

In the following example, the action is set to CompactAndReclaim so that when the fragmentation ratio falls below the threshold, TimesTen defragments the change log tables and reclaims the space:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheConfig('AutoRefreshLogDeFragmentAction',,,'CompactAndReclaim');
< AutoRefreshLogDeFragmentAction, <NULL>, <NULL>, compactandreclaim >
1 row found.

To determine the current fragmentation threshold setting, call ttCacheConfig passing the AutoRefreshLogDeFragmentAction string to the Param parameter:

Command> CALL ttCacheConfig('AutoRefreshLogDeFragmentAction');
< AutoRefreshLogDeFragmentAction , <NULL>, <NULL>, compactandreclaim >

You can discover the fragmentation percentage of the tablespace and when the last defragmentation operation was performed with the following returned columns from the ttCacheAutorefreshStatsGet built-in procedure:

AutoRefreshLogFragmentationPCT: The current fragmentation percentage for the tablespace.
AutoRefreshLogFragmentationTS: The timestamp of when the last fragmentation percentage was calculated.
autorefLogDeFragCnt: The count for how many times the tables in this particular cache group have been defragmented.

See ttCacheConfig and ttCacheAutorefreshStatsGet in the Oracle TimesTen In-Memory Database Reference.

Manually Defragmenting the Change Log Tables for Cache Groups With Autorefresh

To manually initiate a defragmentation of the change log tables, call the ttCacheAutoRefreshLogDeFrag built-in procedure as the TimesTen cache administration user from any of the TimesTen databases that cache data from the Oracle database.

Pass in one of the following strings as the parameter:

Compact: Defragment the change log tables.
CompactAndReclaim: Defragment the change log tables and reclaim the space.

Note:

When reclaiming space, the change log table is briefly locked, which temporarily suspends writing into the base table.

The following example manually defragments the change log tables with the ttCacheAutoRefreshLogDeFrag built-in procedure providing the CompactAndReclaim option:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheAutoRefreshLogDeFrag('CompactAndReclaim');

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

Receiving Notification on Tablespace Usage

In order to avoid the tablespace becoming full, you can configure TimesTen to return a warning to the application when an update operation (such as an UPDATE, INSERT or DELETE statement) is issued on cached Oracle Database tables and causes the usage of the Oracle cache administration user's tablespace to exceed a specified threshold.

Call the ttCacheConfig built-in procedure as the TimesTen cache administration user from any of the TimesTen databases that cache tables from the Oracle database. Pass the AutoRefreshLogTblSpaceUsagePCT string to the Param parameter and the threshold as a numeric string to the Value parameter. The threshold value represents the percentage of space used in the Oracle cache administration user's tablespace upon which a warning is returned to the application when an update operation is issued on a cached Oracle Database table. Do not pass in any values to the tblOwner and tblName parameters as they are not applicable to setting a warning threshold for the usage of the Oracle cache administration user's tablespace.

The Oracle cache administration user must be granted the SELECT privilege on the Oracle Database SYS.DBA_DATA_FILES table in order for the TimesTen cache administration user to set a warning threshold on the Oracle cache administration user's tablespace usage, and for the Oracle cache administration user to monitor its tablespace to determine if the configured threshold has been exceeded.

The following example configures a warning to be returned to the application that issues an update operation on a cached Oracle Database table if it results in the usage of the Oracle cache administration user's tablespace to exceed 80 percent:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheConfig('AutoRefreshLogTblSpaceUsagePCT',,,'80');

To determine the current Oracle cache administration user's tablespace usage warning threshold, call ttCacheConfig passing only the AutoRefreshLogTblSpaceUsagePCT string to the Param parameter:

Command> CALL ttCacheConfig('AutoRefreshLogTblSpaceUsagePCT');
< AutoRefreshLogTblSpaceUsagePCT, <NULL>, <NULL>, 80 >

The default Oracle cache administration user's tablespace usage warning threshold is 0 percent which means that no warning is returned to the application regardless of the tablespace usage. The Oracle cache administration user's tablespace usage warning threshold applies to all TimesTen databases that cache tables from the same Oracle database and have the same Oracle cache administration user name setting.

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

Recovering From a Full Tablespace

By default, when the Oracle cache administration user's tablespace is full, an error is returned to the application when it attempts a DML operation, such as an UPDATE, INSERT or DELETE statement, on a particular cached Oracle Database table.

Rather than TimesTen returning an error to the Oracle Database application when the Oracle cache administration user's tablespace is full, you can configure TimesTen to delete existing rows from the change log tables to make space for new rows when an update operation is issued on a particular cached Oracle Database table. If some of the deleted change log table rows have not been applied to the cache tables, a full autorefresh operation is performed on those cache tables in each TimesTen database that contains the tables upon the next autorefresh cycle.

Call the ttCacheConfig built-in procedure as the TimesTen cache administration user from any of the TimesTen databases that cache tables from the Oracle database. Pass the TblSpaceFullRecovery string to the Param parameter, the owner and name of the cached Oracle Database table to the tblOwner and tblName parameters, respectively, on which you want to configure an action to take if the Oracle cache administration user's tablespace becomes full, and the action itself as a string to the Value parameter.

The following are the valid actions:

None: Return an Oracle Database error to the application when an update operation is issued on the cached Oracle Database table. This is the default action.
Reload: Delete rows from the change log table and perform a full autorefresh operation on the cache table upon the next autorefresh cycle when an update operation is issued on the cached Oracle Database table.

The following example configures an action when the Oracle cache administration user's tablespace becomes full. In this example, rows are deleted from the change log table and a full autorefresh operation is performed on the cache table upon the next autorefresh cycle when an update operation is issued on the sales.customer cached Oracle Database table while the Oracle cache administration user's tablespace is full:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheConfig('TblSpaceFullRecovery','sales','customer','Reload');

To determine the current action to take when an update operation is issued on a particular cached Oracle Database table if the Oracle cache administration user's tablespace is full, call ttCacheConfig passing only the TblSpaceFullRecovery string to the Param parameter, and the owner and name of the cached Oracle Database table to the tblOwner and tblName parameters, respectively:

Command> CALL ttCacheConfig('TblSpaceFullRecovery','sales','customer');
< TblSpaceFullRecovery, SALES, CUSTOMER, reload >

The action to take when update operations are issued on a cached Oracle Database table while the Oracle cache administration user's tablespace is full applies to all TimesTen databases that cache tables from the same Oracle database and have the same Oracle cache administration user name setting.

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