onmode -W: Change settings for the SQL statement cache

Read syntax diagramSkip visual syntax diagram

>>-onmode-- -W--+-STMT_CACHE_HITS--hits-----+------------------><

Element Purpose Key Considerations
STMT_CACHE_HITS hits Specifies the number of hits (references) to a statement before it is fully inserted in the SQL statement cache Set hits to 1 or more to exclude ad hoc queries from entering the cache. You can only increase or reset the value of STMT_CACHE_HITS. The new value displays in the #hits field of the onstat -g ssc output. If hits = 0, the database server inserts all qualified statements and its memory structures in the cache. If hits > 0 and the number of times the SQL statement has been executed is less than STMT_CACHE_HITS, the database server inserts key-only entries in the cache. It inserts qualified statements in the cache after the specified number of hits have been made to the statement. ONCONFIG Parameter: STMT_CACHE_HITS
STMT_CACHE_NOLIMIT value Controls whether statements are inserted in the SQL statement cache. If value = 0, the database server inserts no statements in the cache. If value = 1, the database server always inserts statements in the cache. If none of the queries are shared, turn off STMT_CACHE_NOLIMIT to prevent the database server from allocating a large amount of memory for the cache.ONCONFIG Parameter: STMT_CACHE_NOLIMIT

Copyright© 2018 HCL Technologies Limited