Multiple SQL statement cache pools

Under some circumstances when the SQL statement cache is enabled, the database server allocates memory from one pool for query structures.

These circumstances are:
  • When the database server does not find a matching entry in the cache
  • When the database server finds a matching key-only entry in the cache and the hit count reaches the value of the STMT_CACHE_HITS configuration parameter

This one pool can become a bottleneck as the number of users increases. The STMT_CACHE_NUMPOOL configuration parameter allows you to configure multiple sscpools.

You can monitor the pools in the SQL statement cache to determine the following situations:
  • The number of SQL statement cache pools is sufficient for your workload.
  • The size or limit of the SQL statement cache is not causing excessive memory management.

Copyright© 2020 HCL Technologies Limited