How To Fix DB6111 - The package cache hit ratio is too low

SAP Error Message - Details

• Message type: E = Error

• Message class: DB6 - DB6: Alert Monitoring and Alert Configuration

• Message number: 111

• Message text: The package cache hit ratio is too low

What causes this issue?

Too large a proportion of the requested description data for executing
SQL commands is not being read using the packet cache, but is being
physically read from the database.

System Response

System catalog data is required extremely often. You should therefore
take particular care to optimize access to it.

How to fix this error?

Increase the size of the packet cache using the command:
<LB>UPDATE DATABASE CONFIGURATION FOR <SID> USING PCKCACHESZ <(>
<<)>Value></>

The system issues an error message and will not allow you to continue with this transaction until the error is resolved.

Error message extract from SAP system. Copyright SAP SE.

Smart SAP Assistant

• What is the cause and solution for SAP error message DB6111 - The package cache hit ratio is too low ?

  The SAP error message DB6111 indicates that the package cache hit ratio is too low, which can lead to performance issues in the database. This message typically arises when the database is unable to efficiently reuse previously executed SQL statements, resulting in increased overhead for parsing and executing queries.
  Cause:
  
  Low Package Cache Size: The package cache may be too small to hold frequently executed SQL statements, leading to a high number of cache misses.
  High Query Variability: If the application generates a large number of unique SQL statements (e.g., due to dynamic SQL), the cache may not be able to store them effectively.
  Insufficient Memory Allocation: The database may not have enough memory allocated to the package cache, causing it to evict entries too frequently.
  Database Configuration: Incorrect database configuration settings can lead to suboptimal performance and low cache hit ratios.
  
  Solution:
  
  
  Increase Package Cache Size: 
  
  Adjust the configuration settings to increase the size of the package cache. This can often be done through the database administration tools or configuration files.
  For example, in SAP HANA, you can adjust the package_cache_size parameter.
  
  
  
  Optimize SQL Statements:
  
  Review and optimize the SQL statements being executed. Reducing the variability of SQL statements can help improve the cache hit ratio.
  Consider using parameterized queries or prepared statements to minimize the number of unique SQL statements.
  
  
  
  Monitor and Analyze:
  
  Use database monitoring tools to analyze the package cache hit ratio and identify patterns or specific queries that are causing low hit ratios.
  Look for long-running queries or those that are frequently executed but not cached.
  
  
  
  Database Tuning:
  
  Perform general database tuning, including optimizing indexes, updating statistics, and ensuring that the database is properly configured for the workload.
  Review other memory-related settings to ensure that the database has sufficient resources.
  
  
  
  Regular Maintenance:
  
  Implement regular maintenance tasks such as clearing the cache, updating statistics, and reorganizing tables to ensure optimal performance.
  
  
  
  Related Information:
  
  Monitoring Tools: Use SAP transaction codes like ST04 (Database Performance Analysis) or ST03 (Workload Analysis) to monitor database performance and cache statistics.
  Database Documentation: Refer to the specific database documentation (e.g., SAP HANA, Oracle, SQL Server) for detailed instructions on adjusting cache settings and optimizing performance.
  SAP Notes: Check SAP Notes for any known issues or recommendations related to the DB6111 error message.
  
  By addressing the underlying causes and implementing the suggested solutions, you can improve the package cache hit ratio and enhance overall database performance.