Statement cache directive

Use the statement cache directive to prevent the statement from being stored in the statement cache. The AVOID_STMT_CACHE optimizer directive forces the optimizer to reoptimize the statement every time that the statement is run.

Statement cache directive

AVOID_STMT_CACHE
[ comments ]
Element Description Restrictions Syntax
comments Text documenting the directive Must appear between comment symbols Character string

The AVOID_STMT_CACHE directive is useful for those statements whose query plans can change significantly depending on the values of the placeholders that are passed to the database server when the statements are run. Using a cached query plan for those sorts of statements might result in poor performance.

For example, the following statement is not stored in the statement cache:

SELECT {+AVOID_STMT_CACHE}
    c.customer_num, c.lname, o.order_date
    FROM customer c, order o
    WHERE c.customer_num = o.customer_num;