Home | Previous Page | Next Page   Tuning Database Server Performance > Tuning I/O for Tables and Indexes >

Keeping Small Tables in Memory

Tables or indexes or fragments of tables or indexes that are in constant use and are smaller than 10 or 12 megabytes can be made memory resident to increase lookup efficiency. For fragmented tables or indexes, you can specify residency for individual fragments. Memory-resident tables are cached in the buffer pool of the coserver where the table, index, or fragment exists.

To specify that a table be cached in the buffer pool, use the SET Residency statement, as shown in the following examples:

SET TABLE tab1 MEMORY_RESIDENT

A memory-resident table or index remains in the buffer pool until one of the following events occurs:

Each time the database server is started you must specify the tables and indexes that you want to cache in the buffer pool.

If you cache tables in the buffer pool, you should monitor memory buffers carefully to make sure that they are being used efficiently and that table or index fragments resident in the buffer pool do not cause foreground writes to occur. To monitor memory buffers and memory-resident tables and display the number of resident buffers for each partition, use onstat -P. To list all memory buffers, use onstat -B.

For complete information about using the SET Residency statement, refer to the IBM Informix: Guide to SQL Syntax.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]