SQL statement cache information in onstat -g ssc output

The onstat -g ssc command displays summary information for the SQL statement cache.

The onstat -g ssc command displays the following information for the SQL statement cache.

Table 1. SQL statement cache information in onstat -g ssc output
Column Description
#lrus The number of LRU queues. Multiple LRU queues facilitate concurrent lookup and insertion of cache entries.
currsize The number of bytes currently allocated to entries in the SQL statement cache
maxsize The number of bytes specified in the STMT_CACHE_SIZE configuration parameter
poolsize The cumulative number of bytes for all pools in the SQL statement cache. Use the onstat -g ssc pool option to monitor individual pool usage.
#hits Setting of the STMT_CACHE_HITS configuration parameter, which specifies the number of times that a query is executed before it is inserted into the cache
nolimit Setting of STMT_CACHE_NOLIMIT configuration parameter

The onstat -g ssc command lists the following information for each fully cached entry in the cache. The onstat -g ssc all option lists the following information for both the fully cached entries and key-only entries.

Column Description
lru The LRU identifier
hash The hash-bucket identifier
ref_cnt The number of sessions currently using this statement
hits The number of times that users read the query from the cache, excluding the first time the statement entered the cache
flags Shows flag codes.

The flag codes for position 1 are:

D
Indicates that the statement has been dropped

A statement in the cache can be dropped (not used any more) when one of its dependencies has changed. For example, when you run UPDATE STATISTICS for the table, the optimizer statistics might change, making the query plan for the SQL statement in the cache obsolete. In this case, the database server marks the statement as dropped the next time that it tries to use it.

-
Indicates that the statement has not been dropped

The flag codes for position 2 are:

F
Indicates that the cache entry is fully cached and contains the memory structures for the query
I
Indicates that the statement is in the process of being moved to a fully cached state
-
Indicates that the statement is not fully cached

A statement is not fully cached when the number of times the statement has been executed is less than the value of the STMT_CACHE_HITS configuration parameter. Entries with this - value in the second position appear in the onstat -g ssc all but not in the onstat -g ssc output.

heap_ptr Pointer to the associated heap for the statement
database Database against which the SQL statement is executed
user User executing the SQL statement
statement Statement text as it would be used to test for a match

Copyright© 2019 HCL Technologies Limited