Specifying the Memory Region Sizes of a Database

TimesTen manages database space using two separate memory regions within a single contiguous memory space. One region contains permanent data and the other contains temporary data.

Permanent data includes the tables and indexes that make up a TimesTen database. When a database is loaded into memory, the contents of the permanent memory region are read from files stored on the file system. The permanent memory region is written to the file system during checkpoint operations. TimesTen stores all data in RAM to achieve exceptional performance. The database throws an error if there is no space left for a new piece of data. PermSize can be increased with a database restart but it cannot be decreased.
Temporary data includes locks, cursors, compiled commands, and other structures needed for command execution and query evaluation. The temporary memory region is created when a database is loaded into memory and is destroyed when it is unloaded.

The connection attributes that control the size of the database when it is in memory are PermSize and TempSize. The PermSize attribute specifies the size of the permanent memory region and the TempSize attribute specifies the size of the temporary memory region.

Note:

See PermSize and TempSize in the Oracle TimesTen In-Memory Database Reference.

The sizes of the permanent and temporary memory regions are set when a database is loaded into memory and cannot be changed while the database is in memory. To change the size of either region, you must unload the database from memory and then reconnect using different values for the PermSize or TempSize attributes. See Loading and Unloading the Database From Memory.

Managing the database size is described in these sections:

Estimating and Modifying the Memory Region Sizes for the Database

Database operations cannot complete successfully without allocation of sufficient memory. First, determine appropriate sizes for the TimesTen permanent and temporary memory regions and the transaction log buffer.

Use the ttSize utility or run the application until you can make reasonable estimates, then set these TimesTen connection attributes:

PermSize: Size of the permanent memory region, in MB, for the database where the actual data is stored. Make sure PermSize is sufficient to hold all the data. You can increase this value, but not decrease it for this database.

In TimesTen Classic, you can decrease the permanent memory region by re-creating the database with a smaller size. See Reducing Database Size for TimesTen Classic.

Note:

The ttSize utility is optimized for databases in TimesTen Classic. See Determining the Value of the PermSize Attribute in the Oracle TimesTen In-Memory Database Scaleout User's Guide for how to evaluate an appropriate value for the PermSize connection attribute for a TimesTen Scaleout database.
TempSize: For TimesTen Classic, TempSize indicates the total amount of memory in MB allocated to the temporary region for the database. For TimesTen Scaleout, TempSize indicates the total amount of memory in MB allocated to the temporary region for an element. Related database operations may fail if TempSize is insufficient. You can change this size with a database restart.
LogBufMB: Size of the internal transaction log buffer, in MB. See Configure Log Buffer and Log File Size Parameters.

See PermSize, TempSize, LogBufMB and LogFileSize in the Oracle TimesTen In-Memory Database Reference.

Next, ensure that the maximum shared memory segment size of your system is large enough to contain the database. Use the maximum number of connections that you expect your database to use. All of the values are in MB (megabytes). Make it larger than the following:

PermSize + TempSize + LogBufMB + 1 + (.043 * connections)

Note:

If a TimesTen Classic database is configured for replication, reconfigure the database sizes for all replicas of the database. Once you have made the change in database size, load the database into memory and restart the cache and replication agents.

If there are multiple TimesTen databases on your system, each using its own shared memory segment, the maximum shared memory segment size must be large enough to accommodate the largest database.

Next, determine the total shared memory allocation you will need (converted to appropriate units). If there are multiple TimesTen databases on your system, the total shared memory allocation must be large enough to accommodate all of them, using the above equation for each database. (Then, on Linux for example, divide this value by the page size, typically 4096 bytes, to get total memory allocation in pages.)

Note:

In TimesTen Classic, additional shared segments may be created either for PL/SQL with the PLSQL_MEMORY_SIZE connection attribute or for client/server with the -server_shmsize configuration option (in timesten.conf). Refer to PLSQL_MEMORY_SIZE in Oracle TimesTen In-Memory Database Reference. If you use the default values or similarly small sizes, there should be enough unused space in shared memory to accommodate these segments.

Finally, if you want to allow for database invalidation, there must be at least twice as much physical memory as the size of the largest TimesTen database. If you do not allow for this, but an invalidation does occur, you cannot reload the database into memory until all processes and connections that used the database have been found and terminated.

See Linux Prerequisites in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

Monitoring PermSize and TempSize Attributes

The SYS.V$MONITOR and SYS.GV$MONITOR system views contains several columns that can be used to monitor usage of PermSize and TempSize.

These columns include PERM_ALLOCATED_SIZE, TEMP_ALLOCATED_SIZE, PERM_IN_USE_SIZE, PERM_IN_USE_HIGH_WATER, TEMP_IN_USE_SIZE, and TEMP_IN_USE_HIGH_WATER. Each of these columns show in KB units the currently allocated size of the database and the in-use size of the database. The system updates this information each time a connection is made or released and each time a transaction is committed or rolled back.

For example, you could evaluate the temporary space usage by running a full workload and watching the high water mark (TEMP_IN_USE_HIGH_WATER) of the temporary space usage. The high water mark can be reset using the ttMonitorHighWaterReset built-in procedure. And if necessary, you can change TempSize to a value of the observed TEMP_IN_USE_HIGH_WATER value and add 10%.

Note:

You can also use the ttIsql dssize command to provide this information. See Using the ttIsql dssize Command and ttIsql in the Oracle TimesTen In-Memory Database Reference.

You can monitor block-level fragmentation in the database with the SYS.V$BLOCK_INFO or SYS.GV$BLOCK_INFO system tables or by calling the ttBlockInfo built-in procedure.

See SYS.GV$MONITOR, SYS.V$MONITOR, SYS.GV$BLOCK_INFO or SYS.V$BLOCK_INFO in the Oracle TimesTen In-Memory Database System Tables and Views Reference for details on these views. See ttBlockInfo in the Oracle TimesTen In-Memory Database Reference.

Reducing Database Size for TimesTen Classic

Once a TimesTen Classic database has been defined with a particular size for the permanent region (indicated by the PermSize DSN attribute), it cannot be reduced to a smaller size, even if tables or rows are deleted.

To reduce the allocated size of the permanent region of a TimesTen Classic database, run the ttMigrate utility to save a copy of the database and then re-create the database with a smaller permanent region size and restore the data.

Perform these steps to reduce the permanent region size of a TimesTen Classic database:

Disconnect applications from the database with the ttAdmin -disconnect command. See Disconnecting From a Database in TimesTen Classic in this book and ttAdmin in the Oracle TimesTen In-Memory Database Reference.
Use the ttMigrate -c option to create a data file for the database.
```
ttMigrate -c database1 /tmp/database1
```
Unload the TimesTen Classic database from memory. See Unloading a Database From Memory for TimesTen Classic.
Create a new DSN definition for the new copy of the database with a smaller PermSize value. To modify the original DSN rather than create a new one, you must destroy the original TimesTen Classic database using the ttDestroy utility before restoring from the backup.
Recreate the TimesTen Classic database by using ttIsql with AutoCreate=1.
```
ttIsql -connstr "dsn=database1;AutoCreate=1" -e "quit"
```
The database is empty at this point.
Restore the backup by using the ttMigrate -r and -relaxedUpgrade options.
```
ttMigrate -r -relaxedUpgrade database1 /tmp/database1
```

Note:

The permanent region size of a TimesTen Classic database cannot be reduced below the size that is required by the data currently stored in the database. This value can be determined by querying the perm_in_use_size column of the v$monitor system view.
You can also use this procedure to compact the TimesTen Classic database in order to reduce fragmentation caused by partially full table pages or fragmentation of the heap buffers that store index nodes and out-of-line values.

Receiving Out-of-Memory Warnings

Applications must call the ttWarnOnLowMemory built-in procedure to receive out-of memory warnings.

TimesTen Classic also provides two general connection attributes that determine when a low memory warning should be issued: PermWarnThreshold and TempWarnThreshold. Both attributes take a percentage value.