onmode and W arguments: Reset statement cache attributes (SQL administration API)

Use the onmode and W arguments with the admin() or task() function to change whether and when a statement can be inserted into the SQL cache.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-EXECUTE FUNCTION--+-admin-+--(--"onmode"--,--"W"--,---------->
                     '-task--'                           

>--+-"STMT_CACHE_HITS"--,--"hits"-----+--)--;------------------><
   '-"STMT_CACHE_NOLIMIT"--,--"value"-'         

Element Description Key Considerations
hits The number of hits (references) to a statement before it is fully inserted in the SQL statement cache. Possible values are:
  • 0 = Insert all qualified statements and their memory structures in the cache.
  • 1 or more = Exclude ad hoc queries from entering the cache.
value Whether statements are inserted in the SQL statement cache. Possible values are:
  • 0 = The database server does not insert statements into the cache.
  • 1 = The database server always inserts statements in the cache.

Usage

Use this function to reset the value of the STMT_CACHE_HITS or STMT_CACHE_NOLIMIT configuration parameter while the database server is online. The new value affects only the current instance of the database server; the value is not recorded in the ONCONFIG file. If you shut down and restart the database server, the value of the parameter reverts to the value in the ONCONFIG file.

If you set the value of STMT_CACHE_HITS equal to 0, the database server inserts all qualified statements and their memory structures in the cache. If the value is greater than 0 and the number of times the SQL statement has been executed is less than the value of STMT_CACHE_HITS, the database server inserts key-only entries in the cache. The database server inserts qualified statements in the cache after the specified number of hits has occurred for the statement. The new value of STMT_CACHE_HITS displays in the #hits field of the onstat -g ssc output.

If none of the queries are shared, set STMT_CACHE_NOLIMIT to 0 to prevent the database server from allocating a large amount of memory for the statement cache.

This function is equivalent to the onmode -W command.

Example

The following example prevents ad hoc queries from entering the SQL statement cache:
EXECUTE FUNCTION task("onmode","W","STMT_CACHE_HITS","1");

Copyright© 2019 HCL Technologies Limited