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.
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 ttShmSize
utility or run the application until you can make
reasonable estimates, then set these TimesTen connection attributes:
DSN
: It is specified in theodbc.ini
file. The DSN appears inside square brackets at the top of the DSN definition on a line by itself. For example,[Sampledb]
.-
PermSize
: Size of the permanent memory region, in MB, for the database where the actual data is stored. Make surePermSize
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:
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 ifTempSize
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. Connections
: Maximum number of connections that you expect your database will use.
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:
$ ttShmSize -connstr "DSN=sampledb;PermSize=512;TempSize=128;LogBufMB=256;Connections=2048" The required shared memory size is 1178028616 bytes.
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:
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 thev$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.