Working Memory Areas

To prevent the permanent storage (volumes) being accessed unnecessarily, read and write operations are buffered by the database system in the working memory. For information on when the database system writes information from the working memory to the permanent storage, see Data Storage.

The database system uses the working memory in page units.

The database system divides the working memory it uses into the following areas:

?     I/O buffer cache, which mainly consists of:

0     Data cache (largest part)

0     Converter

0     Internal file directory

?     Catalog cache

?     Shared SQL cache

?     Sequence cache

?     Log queues

?     liveCache database instances only: OMS heap

Working Memory Areas

Example of Working Memory Sizes (Template Desktop PC / Laptop for a MaxDB Database Instance)

Cache

Database Parameters

Maximum Size

I/O buffer cache

CACHE_SIZE = 2500

19.53 MB

Catalog cache

CAT_CACHE_SUPPLY = 1632

12.75 MB

Shared SQL cache

SHAREDSQL_COMMANDCACHESIZE = 262144

256 MB (initial 16 MB)

Sequence cache

SEQUENCE_CACHE = 1

0.01 MB

Log queues

LOG_IO_QUEUE = 50 (size of a log queue)

LOG_IO_QUEUE = 1 (number of log queues)

0.4 MB

I/O Buffer Cache

The database system uses the I/O buffer cache to manage all of the working memory that is available for I/O operations.

?     Data Cache

The database system uses the data cache to store the pages that it has recently read or write-accessed in the data area. This includes, for example, tables, indexes, long values and undo log entries.

The database system always searches for data in the data cache before accessing the data area.

The size of the data cache has the following effect on the performance of the database system:

0     A large data cache makes a high hit rate possible. We recommend a hit rate of at least 98%.

0     If the data cache is on a computer with a small working memory, this can cause the system to swap the pages from the working memory to the hard disk, which reduces performance considerably.

?     Converter Cache

The converter is where the database system stores information about which logical page number is saved at what physical position (MaxDB block address). When the database instance starts, the database system imports the complete converter into the working memory.

See Converter.

If the converter grows while the database is running, and requires more pages, the database system gives it more pages from the I/O buffer cache.

It is not possible to configure the sizes of the data cache and the converter directly; instead, you can configure them indirectly through the size of the I/O buffer cache.

Catalog Cache

The database system stores the following in the catalog cache:

?     Information from the database catalog

?     Information on SQL statements that have already been executed by individual database users (user-specific, only if the SHAREDSQL database parameter has the value NO). If more than one database user executes the same SQL statement, the statement is stored in the catalog cache more than once.

Once the catalog cache is full, the database system moves the information to the data cache.

The database system assigns a catalog cache area to each user task at the start of the database session; this catalog cache is then released after the database session has ended.

The hit rate for the catalog cache should be over 85 percent. The lower limit for the hit rate depends largely on the application.

Shared SQL Cache

If the SHAREDSQL database parameter has the value YES, the database system uses the shared SQL cache.

The shared SQL cache is where the database system stores SQL statements that have already been executed, together with their execution plans. The shared SQL cache is shared by all database users, so the same SQL statement is only stored once.

If the SHAREDSQL database parameter has the value NO, the database system saves the executed SQL statements for each database user individually in the catalog cache.

Sequence Cache

The database system stores all information about sequences in the sequence cache.

Log Queues

The database system assigns each transaction a log queue, where the transaction writes its redo log entries. More than one transaction can write into the same log queue.

See also Logging Data Changes.

Working Memory for the Internal File Directory

When the database instance starts, the database system imports the complete internal file directory into the working memory. The database system stores information for the logical accessing of database objects, such as the assignments of the root pages of the B*-trees to the table IDs, in the internal file directory.

OMS-Heap (only for SAP liveCache Database Instances)

The OMS heap is a specific working memory area only used by SAP liveCache database instances. The OMS heap contains the following data:

?     Local copies of OMS data

When an object within a consistent view is accessed for the first time, the database system writes the corresponding data to the OMS heap.

?     Local application data

The database system copies the data of each OMS version to the OMS heap when it is read.

To access a persistent object, the database system searches first in the OMS heap. If it does not find the object there, it searches in the data cache. If it does not find the object there either, the database system copies the object from the data area to the data cache, and from there to the OMS heap. The database system then makes all changes to the object in the OMS heap. Any objects that are read but not changed within a consistent view are deleted from the OMS heap by the database system. When a transaction ends (COMMIT), the database system writes the OMS data from the OMS heap to the data cache.

The OMS heap grows when the application requests additional working memory. You can configure the maximum size of the OMS heap with the OMS_HEAP_LIMIT liveCache database parameter.

See liveCache Database Parameters.

How does the database system synchronize accesses to critical sections

A critical section is a working memory area for which the database system synchronizes the access of competing tasks through special synchronization tools (regions, reader-writer-locks)

Special Synchronization Tools

Name

Used for

Description

Region

Data cache

Exclusive lock

When a task accesses a critical section, the associated region blocks this critical section for all other tasks.

Reader-writer lock

Catalog cache

Shared SQL cache

Exclusive or shared

If a task write-accesses a critical section, the associated reader-writer lock blocks this critical section for all other write-accesses but allows other read-accesses.

If a task read-accesses a critical section, the associated reader-writer lock blocks this critical section for all other write-accesses but allows other read-accesses.

See also:

Database Parameters

Savepoint

Monitoring

Performance