Control the Invalidation of Commands in the SQL Command Cache

Performance impact: Variable

TimesTen caches compiled commands in the SQL command cache. These commands can be invalidated. An invalidated command is usually re-prepared automatically just before it is re-run. A single command may be prepared several times.

Note:

See Using the Query Optimizer to Choose Optimal Plan for more information on how commands are automatically invalidated.

When you compute statistics, the process of updating and compiling commands may compete for the same locks on certain tables. If statistics are collected in multiple transactions and commands are invalidated after each statistics update, the following issues may occur:

  • A join query that references multiple tables might be invalidated and recompiled more than once.

  • Locks needed for recompilation could interfere with updating statistics, which could result in a deadlock.

You can avoid these issues by controlling when commands are invalidated in the SQL command cache. In addition, you may want to hold off invalidation of all commands if you know that the table and index cardinalities will be changing significantly.

You can control invalidation of the commands, as follows:

  1. Compute statistics without invalidating the commands in the SQL command cache. Set the invalidate option to 0 in either the ttIsql statsupdate command, the ttOptUpdateStats built-in procedure, or the ttOptEstimateStats built-in procedure.

  2. Manually invalidate the commands in the SQL command cache once all statistics have been compiled with the ttOptCmdCacheInvalidate built-in procedure.

The ttOptCmdCacheInvalidate built-in procedure can invalidate commands associated solely with a table or all commands within the SQL command cache. In addition, you can specify whether the invalidated commands are to be recompiled or marked as unusable.

Note:

For complete details on when to optimally calculate statistics, see Compute Exact or Estimated Statistics. In addition, see ttIsql, ttOptUpdateStats, ttOptEstimateStats, or ttOptCmdCacheInvalidate in the Oracle TimesTen In-Memory Database Reference.