Too many single-use queries in the SQL statement cache

When the database server places many queries that are only used once in the cache, they might replace statements that other applications use often. You can view onstat -g ssc all output to determine if too many unshared SQL statements occupy the cache. If so, you can prevent unshared SQL statements from being fully cached.

Look at the values in the following output columns in the Statement Cache Entries portion of the onstat -g ssc all output. If you see a lot of entries that have both of the following values, too many unshared SQL statements occupy the cache:
  • flags column value of F in the second position

    A value of F in the second position indicates that the statement is currently fully cached.

  • hits column value of 0 or 1

    The hits column shows the number of times the SQL statement has been executed, excluding the first time it is inserted into the cache.

Increase the value of the STMT_CACHE_HITS configuration parameter to prevent unshared SQL statements from being fully cached.


Copyright© 2019 HCL Technologies Limited