6 Working with TimesTen Index Advisor
SQL Developer supports the TimesTen Index Advisor which can evaluate a SQL workload and recommend indexes. The indexes that the TimesTen Index Advisor recommends can improve the performance for the following: joins, single table scans, and ORDER BY
or GROUP BY
operations. The TimesTen Index Advisor can collect index information for the current connection or for the entire database.
Note:
SQL Developer uses procedures from the TimesTen Index Advisor to recommend indexes. For more information about the TimesTen Index Advisor, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.
Before you use the TimesTen Index Advisor ensure that you are using Oracle TimesTen In-Memory Database Release 11.2.2.4 (or higher).
This chapter provides information about working with the TimesTen index advisor. Topics include:
Connection level capture
Before attempting to capture data for the TimesTen Index Advisor, ensure that your table statistics have been updated in the past 24 hours. Up-to-date table statistics provide the most up-to-date statistics for data collection and allow statements to be prepared with the optimized query plan. For more information about updating table statistics, see Updating the table and column statistics used by the query optimizer.
There are two ways to capture data for the TimesTen Index Advisor at the connection level.
SQL worksheet
You can capture data for the TimesTen Index Advisor at the connection level from the SQL Developer SQL worksheet. Ensure that your SQL workload is in the SQL Developer SQL worksheet.
Including optimizer hints for Oracle BI server in a SQL worksheet
Use transaction level optimizer hints to direct the TimesTen query optimizer to generate a specific execution plan.
Note:
For more information about optimizer hints, see "Use optimizer hints to modify the execution plan" in the Oracle TimesTen In-Memory Database Operations Guide.
Before using transaction level optimizer hints, ensure that you have completed the steps up to step 4 from SQL worksheet. When you use optimizer hints, ensure to not use any DDL statements or enable AUTOCOMMIT
mode in your SQL workload.
To use optimizer hints for Oracle BI server:
SQL workload script
You can capture data for the TimesTen Index Advisor at the connection level using the SQL queries from a SQL workload script file. Ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.
Including optimizer hints for Oracle BI server for a SQL workload script
Use transaction level optimizer hints to direct the TimesTen query optimizer to generate a specific execution plan.
Note:
For more information about transaction level optimizer hints, see "Use optimizer hints to modify the execution plan" in the Oracle TimesTen In-Memory Database Operations Guide.
Before using transaction level optimizer hints, ensure you have completed the steps up to step 8 from SQL workload script. When you use optimizer hints, ensure to not use any DDL statements or enable AUTOCOMMIT
mode in your SQL workload.
To use optimizer hints for Oracle BI server, follow these steps:
Database level capture
Before attempting to capture data for the TimesTen Index Advisor, ensure that your table statistics have been updated in the past 24 hours. Up-to-date table statistics provide the most up-to-date statistics for data collection and allow statements to be prepared with the optimized query plan. For more information about updating table statistics, see Updating the table and column statistics used by the query optimizer.
Also, ensure that you have granted ADMIN
privileges to the TimesTen connection user. For more information on the GRANT
SQL statement, see "GRANT" in the Oracle TimesTen In-Memory Database SQL
Reference.
To collect data for the TimesTen index advisor at the database level, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.
Saving index recommendations
These steps describe how to save index recommendations for future reference. These steps are valid for Database level capture for a database level capture and SQL workload script for a connection level capture.