Catalog cache hit ratio (CtlgCacheHitRatio)

This parameter displays the percentage of catalog references that did not require access to the catalog because the data was already loaded in the cache since the last reporting interval. It indicates how well the catalog cache is helping to avoid actual accesses to the catalog on disk.

A high ratio indicates it is successful in avoiding actual disk I/O accesses. Performance improves when transactions can access table descriptor information from the catalog cache.

Recommendation:

If the catalog cache hit ratio is consistently low, perform one of the following actions:

• Reduce the number of SQL statements in a single unit of work or package - SQL statements that reference tables, views, and aliases use the catalog cache. If many such SQL statements are frequently compiled in a single unit of work, reduce the number of statements that are compiled between commits. If packages contain many such SQL statements, divide the packages to reduce the number of statements in each package.
• Increase the size of the catalog cache - A consistently low hit ratio could indicate that the catalog cache is too small for the database workload. To increase the size of the cache, increase the CATALOGCACHE_SZ database configuration parameter in small increments (2 to 3 pages at a time). Because both the catalog cache and the log buffer (LOGBUFSZ parameter) are allocated from the database heap, evaluate the size of the DBHEAP parameter to ensure that it is large enough to contain both the log buffer and the increased catalog cache. You must restart the database in order for changes to the catalog cache size to take effect.
• Consider the impact of DDL statements - Executing Data Definition Language (DDL) statements that impact a table, view, or alias removes the associated table descriptor from the catalog cache. If DDL statements are frequently executed in this database, consider limiting their execution to times when database activity is low.

Default parameter attributes