Check the Amount of Memory Allocated to a Database

TimesTen uses both permanent and temporary memory regions. The amount of memory allocated for these regions is set by the PermSize and TempSize attributes in the DSN definition for the database.

When the TimesTen database fills up, it is important to determine whether it is the permanent or the temporary memory region that is filling up. Use the ttIsql dssize command to list allocated, in-use, and high water mark sizes for the permanent and temporary memory regions. The dssize command selects the following values from SYS.MONITOR:

  • PERM_ALLOCATED_SIZE

  • PERM_IN_USE_SIZE

  • PERM_IN_USE_HIGH_WATER

  • TEMP_ALLOCATED_SIZE

  • TEMP_IN_USE_SIZE

  • TEMP_IN_USE_HIGH_WATER

The permanent memory region consists of table and index data, while the temporary memory region consists of internal structures, such as locks, sorting areas, and compiled commands.

Keeping transactions short and making sure there is enough temporary space in the database prevents locks from occupying all of the remaining temporary space. You can also use table locks if transactions are acquiring tens of thousands of row locks.

For tips on how to estimate the size of your database, see Size Your Database Correctly in the Oracle TimesTen In-Memory Database Operations Guide.

Permanent Memory Region Filling Up

Consider whether you can drop any indexes. You may want to look at query plans to see which indexes are actually used. See Using ttIsql to View and Change Query Optimizer Plans in the Oracle TimesTen In-Memory Database Operations Guide. You can also use the ttRedundantIndexCheck procedure to discover redundant indexes. The procedure returns suggestions about which indexes to drop.

Use the ttSize utility to estimate the amount of memory used by each table in the database. If the amount of data you need to store is too big, you may need to reset the PermSize attribute for the database to increase the size of the permanent memory region. Alternatively, you may need to partition your data into several different databases if, for example, you cannot shrink the temporary memory region or create a bigger database because of limits on the memory.

Sometimes when the permanent memory region fills up, copying the data out of the database, deleting all the data, and copying it back in frees up space. This can be done more efficiently by using the ttMigrate utility with the -relaxedUpgrade option to migrate the data out, destroy and recreate the database, and migrate the data back in.

Finally, you may have to configure the operating system to allow a larger amount of shared memory to be allocated to a process. You may also have to allocate more swap space for virtual memory.

Temporary Memory Region Filling Up

Some commands may be allocating too much space because of out-of-date statistics. See Update Query Optimizer Statistics for information.

If updating the statistics does not reduce the temporary memory region, disconnect all connections and then reconnect them. Verify that all connections have been disconnected by using the ttStatus utility. That frees up all temporary space, but you must reprepare commands.

Diagnose memory usage by queries. See Check Memory Used by Queries for information.