The Package Cache Hit Rate component monitors the package cache allocated for the database or partition. Package cache is memory that temporarily stores package and section information required for the execution of static and dynamic SQL statements. When applications obtain static SQL information from package cache, they eliminate I/O on system catalogs. When applications obtain dynamic SQL information from package cache, they avoid the cost of compiling the SQL statements.
At each refresh, Spotlight captures the package cache hit ratio and displays it for this component.
The package cache hit ratio is the main indicator of cache efficiency. If you have a high hit rate (over 80%), the cache is performing well. A low hit rate could mean that the package cache size is too small and should be increased.
The following statistics are captured but not displayed for this component at each refresh.
The number of package cache lookups—The number of package cache lookups that have resulted in applications actually retrieving required SQL information from the package cache.
The number of package cache overflows—The number of all package cache inserts that have resulted in overflows. An overflow occurs when the limit set by the PCKCACHESZ database configuration parameter is exceeded. The package cache is forced to borrow memory from other entities in database shared memory, causing possible lock list escalation and overall performance degradation.
Click the Package 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 is in immediate focus to help you pinpoint problems related to package cache usage in the database or partition.