Data-Dictionary Cache

The first time that the database server accesses a table, it retrieves the information that it needs about the table (such as the column names and data types) from the system catalog tables on disk. Once the database server has accessed the table, it places that information in the data-dictionary cache in shared memory.

Figure 6 shows how the database server uses this cache for multiple users. User 1 accesses the column information for tabid 120 for the first time. The database server puts the column information in the data-dictionary cache. When user 2, user 3 and user 4 access the same table, the database server does not have to read from disk to access the data-dictionary information for the table. Instead, it reads the dictionary information from the data-dictionary cache in memory.

Figure 6. Data-Dictionary Cache

The database server still places pages for system catalog tables in the buffer pool, as it does all other data and index pages. However, the data-dictionary cache offers an additional performance advantage, because the data-dictionary information is organized in a more efficient format and organized to allow fast retrieval.

Data-Dictionary Configuration

The database server uses a hashing algorithm to store and locate information within the data-dictionary cache. The DD_HASHSIZE and DD_HASHMAX configuration parameters control the size of the data-dictionary cache. To modify the number of buckets in the data-dictionary cache, use DD_HASHSIZE (must be a prime number). To modify the number of tables that can be stored in one bucket, use DD_HASHMAX.

For medium to large systems, you can start with the following values for these configuration parameters:

• DD_HASHSIZE 503
• DD_HASHMAX 4

With these values, you can potentially store information about 2012 tables in the data-dictionary cache, and each hash bucket can have a maximum of 4 tables.

If the bucket reaches the maximum size, the database server uses a least recently used mechanism to clear entries from the data dictionary.

Monitoring the Data-Dictionary Cache

Use onstat -g dic to monitor the data-dictionary cache. If commonly used tables are not listed in the data-dictionary cache, try increasing its size.

Figure 7 shows sample output for onstat -g dic.

Dictionary Cache: Number of lists: 31, Maximum list size: 10

list#  size  refcnt  dirty?  heapptr        table name
--------------------------------------------------------
   9     1      0      no     a210330   dawn@atlanta:informix.sysprocedures

  16     1      0      no     a46a420   dawn@atlanta:informix.orders

Total number of dictionary entries: 2

The onstat -g dic output has the following fields.

Field

Description

Number of Lists

Number of buckets that DD_HASHSIZE specifies

Maximum List Size

Number of tables allowed in each bucket

List #

Bucket number

Size

Number of tables in the bucket

Ref cnt

Number of times that users have used the data-dictionary information for this table from the cache

Dirty

Designation if the data-dictionary information is no longer valid

Heap ptr

Heap pointer

Table name

Name of the table that the data-dictionary information describes