3Prepared statements are inherently cached for a single session. 3That is, if a prepared statement is executed many times (or if a single cursor 3is opened many times), the same prepared query plan is used by that session. 3If a session prepares a statement and then executes it many times, its performance 3is essentially unaffected by using the SQL statement cache, because the statement 3is optimized just once, during the PREPARE statement.3
3If other sessions also prepare that same statement, however, or if the 3first session prepares the statement several times, then the statement cache 3usually provides a direct performance benefit, because the database server 3only calculates the query plan once. Of course, the original session might 3gain a (small) benefit from the statement cache, even if it prepares the statement 3only once, because other sessions use less memory, and the database server 3does less work for the other sessions.
3For information on optimization settings, see SAVE EXTERNAL DIRECTIVES, SET OPTIMIZATION, and Optimizer Directives.
For information about the STMT_CACHE environment variable, see the IBM Informix Guide to SQL: Reference.
For more information about STMT_CACHE, STMT_CACHE_NUMPOOL, STMT_CACHE_HITS, and other configuration parameters that affect the statement cache, as well as cache-related command-line options of the onmode utility, see your IBM Informix Administrator's Reference.
For more information on the performance implications of this feature, on when and how to use the SQL statement cache, on how to monitor the cache with the onstat options, and on how to tune the configuration parameters, see your IBM Informix Performance Guide.