1. Cache Guide
  2. Managing a Caching Environment
  3. Managing the Cache Administration User's Tablespace
  4. Defragmenting Change Log Tables in the 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.