Changing the size of the SQL statement cache

You can analyze onstat -g ssc all output to determine if the SQL statement cache is too small. If the size of the cache is too small, you can change it.

To determine if the size of the SQL statement cache is too small:

  1. Run onstat -g ssc all to determine if the cache is too small.
  2. Look at the values in the following output columns in the Statement Cache Entries portion of the onstat -g ssc all output:
    • The flags column shows the current status of an SQL statement in the cache.

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

      A value of - in the second position indicates that only the statement text (key-only entry) is in the cache. Entries with this - value in the second position appear in the onstat -g ssc all output, but not in the onstat -g ssc output.

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

    If you do not see fully cached entries for statements that applications use most and the value in the hits column is large for the entries that do occupy the cache, then the SQL statement cache is too small.

To change the size of the SQL statement cache:

  1. Update the value of the STMT_CACHE_SIZE configuration parameter.
  2. Restart the database server for the new value to take effect.

Copyright© 2019 HCL Technologies Limited