Viewing a Query Plan from the System PLAN Table

The optimizer prepares the query plans. For the last SQL statement to run, you can instruct that the plan be stored in the system PLAN table:

  1. Instruct TimesTen to generate the plan and store it in the system PLAN table.

  2. Prepare the statement means calling the ODBC SQLPrepare function or JDBC Connection.prepareStatement() method on the statement. TimesTen stores the plan into the PLAN table.

  3. Read the generated plan within the SYS.PLAN table.

The stored plan is updated automatically whenever the command is reprepared. Re-preparation occurs automatically if one or more of the following occurs:

  • A table in the statement is altered.

  • If indexes are created or dropped.

  • The application invalidates commands when statistics are updated with the invalidate option in the ttOptUpdateStats built-in procedure.

  • The user invalidates commands with the ttOptCmdCacheInvalidate built-in procedure.

Note:

See Control the Invalidation of Commands in the SQL Command Cache. For more information on the built-in procedures, see ttOptUpdateStats and ttOptCmdCacheInvalidate in the Oracle TimesTen In-Memory Database Reference.

For these cases, read the PLAN table to view how the plan has been modified.