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