Hi everyone,

I'm using Informix 14.10.FC8 and I'd like to execute a query just once without it using any cached plan or statement cache - basically, I want it to be fully re‑parsed and re‑optimized for testing purposes.

Is there any supported way (per‑session or per‑statement) to run a query without using the cache, or to force a one‑time reoptimization without disabling caching globally?

Thanks in advance!

You can disable the statement caching during any session, assuming that it is even active (most sites do not enable statement caching), with the SET STATEMENT CACHE OFF statement:

SET STATEMENT CACHE statement
Use the SET STATEMENT CACHE statement to turn on caching or turn off
caching for the current session. This statement is an extension to the ANSI/ISO
standard for SQL.
Syntax
►► SET STATEMENT CACHE
 ON
 ►◄
OFF

You cannot disable data caching, however. but that will not effect the optimization of the statement, only its execution time after the first attempt.

Art

Hi everyone,

I'm using Informix 14.10.FC8 and I'd like to execute a query just once without it using any cached plan or statement cache - basically, I want it to be fully re‑parsed and re‑optimized for testing purposes.

Is there any supported way (per‑session or per‑statement) to run a query without using the cache, or to force a one‑time reoptimization without disabling caching globally?

Thanks in advance!

Thanks

In fact, we need the execution time to be like the first attempt.

You can disable the statement caching during any session, assuming that it is even active (most sites do not enable statement caching), with the SET STATEMENT CACHE OFF statement:

SET STATEMENT CACHE statement
Use the SET STATEMENT CACHE statement to turn on caching or turn off
caching for the current session. This statement is an extension to the ANSI/ISO
standard for SQL.
Syntax
►► SET STATEMENT CACHE
 ON
 ►◄
OFF

You cannot disable data caching, however. but that will not effect the optimization of the statement, only its execution time after the first attempt.

Art

Hi everyone,

I'm using Informix 14.10.FC8 and I'd like to execute a query just once without it using any cached plan or statement cache - basically, I want it to be fully re‑parsed and re‑optimized for testing purposes.

Is there any supported way (per‑session or per‑statement) to run a query without using the cache, or to force a one‑time reoptimization without disabling caching globally?

Thanks in advance!

The only thing you could do then, is:

a) Run it twice and use that second data cached timing for further testing, or

b) Use the "onmode -B reset" to flush and initialize the cache, but that will affect all sessions.

There is no way to set a session to not use the cache.

Thanks

In fact, we need the execution time to be like the first attempt.

You can disable the statement caching during any session, assuming that it is even active (most sites do not enable statement caching), with the SET STATEMENT CACHE OFF statement:

SET STATEMENT CACHE statement
Use the SET STATEMENT CACHE statement to turn on caching or turn off
caching for the current session. This statement is an extension to the ANSI/ISO
standard for SQL.
Syntax
►► SET STATEMENT CACHE
 ON
 ►◄
OFF

You cannot disable data caching, however. but that will not effect the optimization of the statement, only its execution time after the first attempt.

Art

Hi everyone,

I'm using Informix 14.10.FC8 and I'd like to execute a query just once without it using any cached plan or statement cache - basically, I want it to be fully re‑parsed and re‑optimized for testing purposes.

Is there any supported way (per‑session or per‑statement) to run a query without using the cache, or to force a one‑time reoptimization without disabling caching globally?

Thanks in advance!