Fragmented Autorefresh Change Log Table Space

Change log tables can become fragmented when a high water mark occurs as the result of change logs building up when TimesTen is shut down, for example. If change log tables have become fragmented, you can:

  • Coalesce their indexes. This can be done without preventing DML changes to the base tables.

  • Perform an online segment shrink. This can be done without preventing DML changes to the base tables.

  • Rebuild the change log tables.

Check to see whether space is being wasted:

  1. Determine the name of the change log table by running the cacheInfo.sql script on the Oracle database.

  2. Calculate the size of the change log table. Call the result A. Adapt the name of the change log table in this example.

    SELECT table_name, ROUND((BLOCKS*8),2)||'KB' "size" 
 FROM user_tables
 WHERE table_name LIKE 'TT_05_%_L";

  3. Calculate the size of the data in the change log table. Call the result B. Adapt the name of the change log table in this example.

    SELECT table_name, ROUND((num_rows*avg_row_length/1024),2)|| 'KB' "size"
 FROM user_tables
 WHERE table_name LIKE 'TT_05_%_L';

  4. If (B/A)*100 is greater than 50 percent, then there is at least 40 percent space wasted (assuming a PCTFREE storage parameter set to 10). If there is at least 40 percent space wasted, defragmenting the change log table is recommended.

Perform these steps to defragment the change log table:

  1. Alter the cache group to set the autorefresh state to PAUSED.
  2. Copy the rows in the change log table to a temporary table.
  3. Truncate the change log table.
  4. Insert the rows from the temporary table to the change log table.
  5. Alter the cache group to set the autorefresh state to ON.

For more information on managing this tablespace, see Managing the Cache Administration User's Tablespace in the Oracle TimesTen In-Memory Database Cache Guide.