Catalog Cache component

Catalog Cache Hit Rate component

The Catalog Cache Hit Rate component monitors the catalog cache allocated for the database. The catalog cache is memory that temporarily stores table descriptors—information about tables, views, and aliases in a condensed internal format—found in the database catalog. A transaction that compiles a SQL statement looks in the catalog cache first for descriptors of objects referenced in the SQL statement. If these descriptors are available in cache, the transaction uses descriptor information in cache and avoids having to access the database catalog.

Required monitor switch

None

Displayed statistics

At each refresh, Spotlight captures the catalog cache hit rate and displays it for this component. The hit rate is the percentage of catalog cache lookups that resulted in retrieving the descriptor information from the catalog cache.

Additional statistics

The following statistics are captured but not displayed for this component at each refresh.

• The number of catalog cache lookups—The number of times that the catalog cache was accessed to get table descriptor information. This counter includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever a table, view, or alias name is processed during the compilation of an SQL statement.

• The number of catalog cache overflows—The number of times that an insert failed because the cache was full. Catalog cache space becomes available again when table descriptor information that is not being used is purged from the cache.

Note: Once a transaction has a catalog cache overflow event, every ensuing attempt by the same transaction to insert table descriptor information into the cache will also result in an overflow. The transaction involved will continue, but its descriptor information will not be inserted.

Associated drilldown

Click the Catalog Cache Hit Rate component to open the Databases drilldown. This drilldown provides details about the database or partition that you are monitoring. The Caching tab on the drilldown is in immediate focus to help you pinpoint problems related to catalog cache usage in the database or partition.

Related topics

Database drilldown