Identifier:db2.db_cat_cache_hit_ratio
Database Object Monitored:Database
Category:Buffer Pool and I/O
Description:Catalog cache hit ratio (percent effective)
Equation:100 * (1 - (DELTA(database.cat_cache_inserts) / DELTA(database.cat_cache_lookups)))
Usage Notes:This variable 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 ratio of catalog cache inserts to catalog cache lookups indicates how successful the catalog cache is at avoiding catalog accesses. If the ratio is greater than 0.8 then the catalog cache is performing well. A smaller ratio may indicate that the catalog cache size should be increased.
You should expect a low value immediately following the first connection to the database.
The execution of Data Definition Language (DDL) SQL statements involving a table, view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. Therefore, the heavy use of DDLs may also increase the ratio.
| Note: | For multiple buffer pools, the data is combined and only the aggregate number is returned. |