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;