Prepare for Running the Index Advisor

Before you run the Index Advisor, you can optionally set any relevant optimizer hints and update statistics for the tables included in the SQL workload and force statements to be re-prepared during the capture.

  1. Since the Index Advisor relies on the query plan, set any relevant optimizer hints that you would use for the SQL workload before enabling the Index Advisor and running the workload. See Use Optimizer Hints to Modify the Execution Plan.

  2. Update statistics for tables included in the SQL workload and force statements to be re-prepared during the capture. This provides the most up-to-date statistics for the data collection and causes the statements to be re-prepared based on the latest statistics.

Update statistics for tables included in the SQL workload with one of the following built-in procedures: ttOptUpdateStats, ttOptEstimateStats, or ttOptSetTblStats. In the built-in procedures, set the invalidate parameter to 1 to invalidate all commands that reference the indicated tables and force these commands to be automatically prepared again when re-rund. This ensures that statistics are up to date.

  • The ttOptUpdateStats built-in procedure provides a full update of all statistics for the tables. However, it can be time consuming.

  • The ttOptEstimateStats evaluates statistics based upon a small percentage of rows in the indicated tables.

  • The ttOptSetTblStats sets the statistics to known values provided by you.

Note:

See ttOptUpdateStats, ttOptEstimateStats, and ttOptSetTblStats in the Oracle TimesTen In-Memory Database Reference.

The following example estimates statistics for all tables for the current user by evaluating a random sample of ten percent of the rows in these tables. It also invalidates all commands already prepared that reference these tables.

Command> call ttOptEstimateStats ( '', 1, '10 PERCENT' );