Online Defragmentation of TimesTen Databases

Online defragmentation continuously scans user tables and defragments the row pages. This process helps eliminate fragmentation in user table row pages and frees up memory for future use. It keeps the TimesTen database running and eliminates the need for database downtime. To keep CPU usage low, the online defragmentation process periodically sleeps, releasing resources for other processes. You can set the speed of the online defragmentation process, which determines how much CPU is allocated to it.

There are three first connection attributes that control online defragmentation:
  • DefragThreshold
  • DefragSpeed
  • DefragCycle

For details, see DefragThreshold, DefragSpeed, and DefragCycle in the Oracle TimesTen In-Memory Database Reference.

You can configure online defragmentation by setting the values of the first connection attributes when you connect to the database. This is an example of a DSN specification for a database, database1 configured for online defragmentation.
[database1]
DataStore=/disk1/databases/database1
LogDir=/disk1/logs
DatabaseCharacterSet=AL32UTF8
DbEncrypted=AES256
DbKeyWallet=/disk1/wallets
DefragThreshold=3
DefragSpeed=2
DefragCycle=50
  • The DefragThreshold is configured to 3, even slightly fragmented pages are defragmented, helping reclaim memory faster.
  • The DefragSpeed is set to 2, allows defragmentation to run at a moderate speed, reclaiming space efficiently and CPU utilization.
  • The DefragCycle is set to 50, so the process runs every 50 seconds to check for and resolve fragmentation.
To view the current values of the attributes, use the ttDBConfig or ttConfiguration (see ttConfiguration) built-in procedures:
Command> CALL ttDBConfig ('DefragThreshold');
< DEFRAGTHRESHOLD, 3 >
1 row found.

Command> CALL ttDBConfig ('DefragSpeed');
< DEFRAGSPEED, 2 >
1 row found.

Command> CALL ttDBConfig ('DefragCycle');
< DEFRAGCYCLE, 50 >
1 row found.

To modify the values of these attributes, see ttDBConfig in the Oracle TimesTen In-Memory Database Reference.

You can use the ttPageLevelTableInfo built-in procedure to determine the degree of fragmentation in the tables. The parameters such as PHYSICAL_PGCNT, PHYSICAL_NONFULLPAGES, PHYSICAL_FREESLOTS estimate the number of rows on each partially filled page. If the partially filled pages contain only a few rows, then it indicates a highly fragmented row page. For more details, see ttPageLevelTableInfo in the Oracle TimesTen In-Memory Database Reference.

If you configure your application with LockWait=0, application transactions may experience lock conflicts with the defragmentated operations. The defragmentation operations are guaranteed to commit at least once every second, regardless of the DefragSpeed value. If increasing the LockWait value raises concerns about potential deadlocks, it is advisable to disable the defragmentation (DefragSpeed=0) during the peak workload periods and re-enable it during the low workload times or maintenance windows to perform defragmentation. See LockWait in the Oracle TimesTen In-Memory Database Reference.