Instruct TimesTen to Store the Plan in the System PLAN Table

Before you can view the plan in the system PLAN table, enable the creation of entries in the PLAN table with the plan generation option as follows:

  • For transaction level optimizer hints, call the built-in ttOptSetFlag procedure and enable the GenPlan flag.

  • For statement level optimizer hints, set TT_GENPLAN(1), which is only in effect for the statement. After the statement runs, the plan generation option takes on the value of the GenPlan transaction level optimizer hint.

Note:

See Use Optimizer Hints to Modify the Execution Plan for details on statement level and transaction level optimizer hints.

This informs TimesTen that all subsequent calls to the ODBC SQLPrepare function or JDBC Connection.prepareStatement() method in the transaction should store the resulting plan in the current SYS.PLAN table.

The SYS.PLAN table only stores one plan, so each call to the ODBC SQLPrepare function or JDBC Connection.prepareStatement() method overwrites any plan currently stored in the table.

If a command is prepared with plan generation option set, it is also recompiled for plan generation. Thus, the plan is generated even though the plan for another query was found in the SYS.PLAN table.

You can use showplan in ttIsql to test the query and optimizer hints, which enables plan generation as well as shows the query plan for the statements in the transaction. Autocommit must be off. 

autocommit 0;
showplan 1;