Using the SQL Statement Cache

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Improving Individual Query Performance > SQL Statement Cache >

Using the SQL Statement Cache

The DBA usually makes the decision to enable the SQL statement cache. If the SQL statement cache is enabled, individual users can decide whether or not to use the SQL statement cache for their specific environment or application.

The database server incurs some processing overhead in managing the SQL statement cache, so a user should not use the SQL statement cache if no other users share the SQL statements in the application.

The following section describes how the DBA can enable the SQL statement cache in one of two modes:

Always use the SQL statement cache unless a user explicitly specifies do not use the cache.
Use the SQL statement cache only when a user explicitly specifies use it.

Enabling the SQL Statement Cache

The database server does not use the SQL statement cache when the STMT_CACHE configuration parameter is 0 (the default value).

Use one of the following methods to change this STMT_CACHE default value:

Update the ONCONFIG file to specify the STMT_CACHE configuration parameter and restart the database server.
If you set the STMT_CACHE configuration parameter to 1, the database server uses the SQL statement cache for an individual user when the user sets the STMT_CACHE environment variable to 1 or executes th e SET STATEMENT CACHE ON statement within an application.
```
STMT_CACHE 1
```
If the STMT_CACHE configuration parameter is 2, the database server stores SQL statements for all users in the SQL statement cache except when individual users turn off the feature with the STMT_CACHE environment variable or the SET STATEMENT CACHE OFF statement.
```
STMT_CACHE 2
```
Use the onmode -e command to override the STMT_CACHE configuration parameter dynamically.
If you use the enable keyword, the database server uses the SQL statement cache for an individual user when the user sets the STMT_CACHE environment variable to 1 or executes the SET STATEMENT CACHE ON statement within an application.
```
onmode -e enable
```
If you use the on keyword, the database server stores SQL statements for all users in the SQL statement cache except when individual users turn off the feature with the STMT_CACHE environment variable or the SET STATEMENT CACHE OFF statement.
```
onmode -e on
```

The following table summarizes the use of the SQL statement cache for a user, depending on the setting of the STMT_CACHE configuration parameter (or the execution of onmode -e) and the use in an application of the STMT_ CACHE environment variable and the SET STATEMENT CACHE statement.

STMT_ CACHE Configuration Parameter or onmode -e	STMT_CACHE Environment Variable	SET STATEMENT CACHE Statement	Resulting Behavior
0 (default)	Not applicable	Not applicable	Statement cache not used
1	0 (or not set)	OFF	Statement cache not used
1	1	OFF	Statement cache not used
1	0 (or not set)	ON	Statement cache used
1	1	ON	Statement cache used
1	1	Not executed	Statement cache used
1	0	Not executed	Statement cache not used
2	1 (or not set)	ON	Statement cache used
2	1 (or not set)	OFF	Statement cache not used
2	0	ON	Statement cache used
2	0	OFF	Statement cache not used by user
2	0	Not executed	Statement cache not used by user
2	1 (or not set)	Not executed	Statement cache used by user

Placing Statements in the Cache

SELECT, UPDATE, INSERT and DELETE statements can be placed in the SQL statement cache, with some exceptions. When the database server checks if an SQL statement is in the cache, it must find an exact match.

For a complete list of the exceptions and a list of requirements for an exact match, see SET STATEMENT CACHE in the IBM Informix: Guide to SQL Syntax.