4 Working with the 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:

  • Make sure you are using Oracle TimesTen In-Memory Database Release 11.2.2.4 (or later).

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, make sure 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. Make sure your SQL workload is in the SQL Developer SQL worksheet.

  1. Click the TimesTen Index Advisor button from the SQL worksheet menu bar.

    Figure 4-1 TimesTen Index Advisor from SQL worksheet

    Description of Figure 4-1 follows
    Description of "Figure 4-1 TimesTen Index Advisor from SQL worksheet"

    • If your table statistics are outdated, an information dialog displays information about the outdated tables. TimesTen recommends that you update your table statistics.

    • If your table statistics are up to date, the Index Advisor Configuration dialog displays.

  2. To update your table statistics, click Cancel and follow the steps in "Updating the table and column statistics used by the query optimizer". To proceed without updating your table statistics, click OK.

    Figure 4-2 Outdated table statistics

    Description of Figure 4-2 follows
    Description of "Figure 4-2 Outdated table statistics"

    The Index Advisor Configuration dialog displays.

  3. Select the desired capture mode from the Capture mode drop-down list:

    • Prepare SQL: TimesTen prepares but does not execute the SQL commands from the SQL worksheet. The TimesTen Index Advisor makes recommendations using computed statistics and query plan analysis. This is the default capture mode.

    • Execute SQL: TimesTen executes the SQL commands from the SQL worksheet. The TimesTen Index Advisor makes recommendations using the actual execution of the SQL commands. This capture mode may take longer than the Prepare SQL mode because TimesTen has to complete SQL execution.

    Figure 4-3 Index Advisor configuration

    Description of Figure 4-3 follows
    Description of "Figure 4-3 Index Advisor configuration"

    You can now choose to include optimizer hints for Oracle BI server.

  4. The Include optimizer hints for Oracle BI server checkbox enables you to use optimizer hints that direct the TimesTen query optimizer to generate a specific execution plan. These optimizer hints are recommended queries generated by the Oracle BI server. For more information on using optimizer hints, see "Including optimizer hints for Oracle BI server in a SQL worksheet". If you do not want to use optimizer hints, click Start.

    Figure 4-4 Start collecting data for the TimesTen index advisor without optimizer hints

    Description of Figure 4-4 follows
    Description of "Figure 4-4 Start collecting data for the TimesTen index advisor without optimizer hints"

    The Index Advisor Configuration dialog closes. A TimesTen index advisor pane displays at the bottom of the SQL worksheet. See Figure 4-5.

  5. In the Selector column, select the index recommendations you want to create.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box you select to determine the index recommendations that you want to create.

    • Index Recommendation

      The CREATE INDEX statement that the TimesTen index advisor recommends.

    • Affected Statement Count

      The number of statements that benefit from the recommendation.

    • Created

      Shows if you have already created the index based on the recommendation by the TimesTen index advisor. This value can be Yes or No. If this value is Yes, TimesTen created the index successfully. If this value is No, TimesTen is in the process of creating the index or is unable to create the index.

    To select all index recommendations, click the Select all check box.

    To review the SQL commands that are being evaluated by the TimesTen index advisor, click the SQL tab. The SQL tab is located at the top right of the TimesTen index advisor pane.

  6. Once you select the index recommendations that you want to create, click the Create Selected Indexes button.

    Figure 4-5 Create index recommendations

    Description of Figure 4-5 follows
    Description of "Figure 4-5 Create index recommendations"

    The Create Selected Indexes dialog displays. Locate the Details >> button.

  7. Click Details >>.

    Figure 4-6 Create selected indexes progress

    Description of Figure 4-6 follows
    Description of "Figure 4-6 Create selected indexes progress"

    A details pane expands that shows progress information about creating the selected indexes. Locate the Close button at the bottom of the dialog.

  8. Click Close.

    The Create Selected Indexes dialog closes. TimesTen creates the indexes. To save the index recommendations, see "Saving index recommendations from a 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 "Understanding hints" in the Oracle TimesTen In-Memory Database SQL Reference.

Before using transaction level optimizer hints, make sure you have completed the steps up to step 4 from "SQL worksheet". When you use optimizer hints, make sure 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:

  1. Select the Include optimizer hints for Oracle BI server check box.

    Figure 4-8 Including optimizer hints for Oracle BI server

    Description of Figure 4-8 follows
    Description of "Figure 4-8 Including optimizer hints for Oracle BI server"

    A field with the optimizer hints for Oracle BI server displays in the Index Advisor Configuration dialog. You cannot edit the optimizer hints. By default, the optimizer hints for Oracle BI server are as follows:

    --
-- Optimizer hints to be used with TimesTen Index Advisor for Oracle BI server
-- 
autocommit 0;
call ttoptsetflag('rowlock',0);
call ttoptsetflag('tbllock',1);
call ttoptsetflag('mergejoin',0);
call ttoptsetflag('nestedloop',1);

    Locate the Start button at the bottom right of the dialog.

  2. Click Start.

    Figure 4-9 Start collecting data for the TimesTen index advisor with optimizer hints

    Description of Figure 4-9 follows
    Description of "Figure 4-9 Start collecting data for the TimesTen index advisor with optimizer hints"

    The Index Advisor Configuration dialog closes. A TimesTen index advisor pane displays at the bottom of the SQL worksheet. The AUTOCOMMIT mode of your transaction is returned to the AUTOCOMMIT mode that your transaction had prior to running the TimesTen index advisor.

    For more information on the TimesTen index advisor pane and how to create indexes, see "In the Selector column, select the index recommendations you want to create." from "SQL worksheet".

Saving index recommendations from a SQL worksheet

To save index recommendations for future reference, follow these steps:

  1. Click Save Indexes. TimesTen saves all index recommendations. TimesTen does not save individual index recommendations.

    The Save Indexes dialog displays.

  2. Select the directory where you want to save the TimesTen index advisor recommendations.

  3. In the File Name field, define the file name of your TimesTen index advisor recommendations.

    The default filename of the TimesTen index advisor recommendations is connection_name-indexadvice-YYYYMMDDMISS.sql, where connection_name is the name of the connection. YYYYMMDDHHMISS is a timestamp of when the recommendations were made, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, and SS the seconds.

  4. Click Save.

    Figure 4-11 Save index recommendations

    Description of Figure 4-11 follows
    Description of "Figure 4-11 Save index recommendations"

    TimesTen saves the index recommendations.

    The saved file header contains the following information:

    • The timestamp of when TimesTen generated the report.

    • The version of SQL Developer.

    • The version of the TimesTen database.

    • The name of the TimesTen connection.

    For example:

    -- This file was generated by SQL Developer at 2013-04-29 11:08:45
-- SQL Developer version 4.0.0.11.51
-- Database version: 11.02.02.0005 Oracle TimesTen IMDB version 11.2.2.5.0
-- Connection name: sampledb_1122

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. Make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Right-click the name of the database and select Index Advisor, then select Connection Level Capture.

    Figure 4-12 Collecting data at the connection level

    Description of Figure 4-12 follows
    Description of "Figure 4-12 Collecting data at the connection level"

    • If your table statistics are outdated, an information dialog displays information about the outdated tables. TimesTen recommends that you update your table statistics.

    • If your table statistics are up to date, the Index Advisor Configuration dialog displays.

  2. To update your table statistics, click Cancel and follow the steps in "Updating the table and column statistics used by the query optimizer". To proceed without updating your table statistics, click OK.

    Figure 4-13 Outdated table statistics

    Description of Figure 4-13 follows
    Description of "Figure 4-13 Outdated table statistics"

    The Index Advisor - Connection Level Capture dialog displays.

  3. In the Configuration tab of the Index Advisor - Connect Level Capture dialog, select the desired capture mode from the Capture mode drop-down list:

    • Prepare SQL: TimesTen prepares but does not execute the SQL commands from the workload script. The TimesTen Index Advisor makes recommendations using computed statistics and query plan analysis. This is the default capture mode.

    • Execute SQL: TimesTen executes SQL commands from the workload script. The TimesTen Index Advisor makes recommendations using the actual execution of the workload script. This capture mode may take longer than the Prepare SQL mode because TimesTen has to complete SQL execution.

    Figure 4-14 Index advisor - Connection Level Capture

    Description of Figure 4-14 follows
    Description of "Figure 4-14 Index advisor - Connection Level Capture"

    You are ready to specify your SQL workload script. SQL Developer uses the ttIsql utility to execute the SQL workload script. Locate the Browse button that is to the right of the Workload script field.

  4. Click Browse.

    The Open dialog displays.

  5. Select the directory where you saved your SQL workload script.

  6. Select the SQL workload script for which you want to generate index recommendations.

  7. Click Open.

    Figure 4-16 SQL workload script open dialog

    Description of Figure 4-16 follows
    Description of "Figure 4-16 SQL workload script open dialog"

    The TimesTen index advisor is now ready to collect data for your SQL workload. Locate the Start button.

    To review the SQL commands that are being evaluated by the TimesTen index advisor, click the SQL tab. The SQL tab is located at the top of the Index Advisor - Connection Level Capture dialog.

  8. The Include optimizer hints for Oracle BI server checkbox enables you to use optimizer hints that direct the TimesTen query optimizer to generate a specific execution plan. These optimizer hints are recommended queries generated by the Oracle BI server. For more information on using optimizer hints, see "Including optimizer hints for Oracle BI server for a SQL workload script". If you do not want to use optimizer hints, click Start.

    Figure 4-17 Start collecting data for the TimesTen index advisor

    Description of Figure 4-17 follows
    Description of "Figure 4-17 Start collecting data for the TimesTen index advisor"

    The Index advisor assistant progress dialog displays. Locate the Details >> button.

    If the TimesTen index advisor does not have any recommendations, the Index Recommendation Feedback dialog displays. Close this dialog to return to the main SQL developer page.

  9. Click Details >>.

    Figure 4-18 Index advisor assistant progress

    Description of Figure 4-18 follows
    Description of "Figure 4-18 Index advisor assistant progress"

    A details pane expands that shows progress information about the index advisor connection level capture. Locate the Close button at the bottom of the dialog.

  10. Click Close.

    The Index advisor assistant progress dialog closes. Locate the Index Recommendations tab.

  11. Click the Index Recommendations tab.

    Figure 4-20 Index recommendations for connection level capture

    Description of Figure 4-20 follows
    Description of "Figure 4-20 Index recommendations for connection level capture"

    The Index Recommendations tab of the Index Advisor - Connection Level Capture dialog displays.

  12. In the Selector column, select the index recommendations that you want to create.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box you select to determine the index recommendations that you want to create.

    • Index Recommendation

      The CREATE INDEX statement that the TimesTen index advisor recommends.

    • Affected Statement Count

      The number of statements that benefit from the recommendation.

    • Created

      Shows if you have already created the index based on the recommendation by the TimesTen index advisor. This value can be Yes or No. If this value is Yes, TimesTen created the index successfully. If this value is No, TimesTen is in the process of creating the index or is unable to create the index.

    To select all index recommendations, click the Select all check box.

  13. Once you select the index recommendations that you want to apply, click the Create Selected Indexes button.

    Figure 4-21 Create index recommendations

    Description of Figure 4-21 follows
    Description of "Figure 4-21 Create index recommendations"

    The Creating Selected Indexes dialog displays. Locate the Details >> button.

  14. Click Details >>.

    Figure 4-22 Create Selected Indexes

    Description of Figure 4-22 follows
    Description of "Figure 4-22 Create Selected Indexes"

    A details pane expands that shows progress information about creating the selected indexes. Locate the Close button at the bottom of the dialog.

  15. Click Close.

    The Creating Selected Indexes dialog closes. The indexes are created. To save the index recommendations, see "Saving index recommendations".

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 "Understanding hints" in the Oracle TimesTen In-Memory Database SQL Reference.

Before using transaction level optimizer hints, make sure you have completed the steps up to step 8 from "SQL workload script". When you use optimizer hints, make sure 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:

  1. Select the Include optimizer hints for Oracle BI server check box.

    Figure 4-24 Including optimizer hints for Oracle BI server

    Description of Figure 4-24 follows
    Description of "Figure 4-24 Including optimizer hints for Oracle BI server"

    A field with the optimizer hints for Oracle BI server displays in the Index Advisor - Connection Level Capture dialog. These optimizer hints are not editable. By default, the optimizer hints for Oracle BI server are as follows:

    --
-- Optimizer hints to be used with TimesTen Index Advisor for Oracle BI server
-- 
autocommit 0;
call ttoptsetflag('rowlock',0);
call ttoptsetflag('tbllock',1);
call ttoptsetflag('mergejoin',0);
call ttoptsetflag('nestedloop',1);

    Locate the Start button at the bottom right of the dialog.

  2. Click Start.

    Figure 4-25 Start collecting data for the TimesTen index advisor with optimizer hints

    Description of Figure 4-25 follows
    Description of "Figure 4-25 Start collecting data for the TimesTen index advisor with optimizer hints"

    The Index advisor assistant progress dialog displays. Locate the Details >> button.

  3. Click Details >>.

    Figure 4-26 Index advisor assistant progress

    Description of Figure 4-26 follows
    Description of "Figure 4-26 Index advisor assistant progress"

    A details pane expands that shows progress information about the index advisor connection level capture. Locate the Close button at the bottom of the dialog.

  4. Click Close.

    The Index advisor assistant progress dialog closes. Locate the Index Recommendations tab.

  5. Click the Index Recommendations tab.

    Figure 4-28 Index recommendations for connection level capture

    Description of Figure 4-28 follows
    Description of "Figure 4-28 Index recommendations for connection level capture"

    The Index Recommendations tab of the Index Advisor - Connection Level Capture dialog displays.

  6. In the Selector column, select the index recommendations that you want to create.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box you select to determine the index recommendations that you want to create.

    • Index Recommendation

      The CREATE INDEX statement that the TimesTen index advisor recommends.

    • Affected Statement Count

      The number of statements that benefit from the recommendation.

    • Created

      Shows if you have already created the index recommendation. This value can be Yes or No.

    To select all index recommendations, click the Select all check box.

  7. Once you select the index recommendations that you want to apply, click the Create Selected Indexes button.

    Figure 4-29 Create index recommendations

    Description of Figure 4-29 follows
    Description of "Figure 4-29 Create index recommendations"

    The Creating Selected Indexes dialog displays. Locate the Details >> button.

  8. Click Details >>.

    Figure 4-30 Creating Selected Indexes

    Description of Figure 4-30 follows
    Description of "Figure 4-30 Creating Selected Indexes"

    A details pane expands that shows progress information about creating the selected indexes. Locate the Close button at the bottom of the dialog.

  9. Click Close.

    The Creating Selected Indexes dialog closes. The indexes are created. The AUTOCOMMIT mode of your transaction is returned to the AUTOCOMMIT mode that your transaction had prior to running the TimesTen index advisor. To save the index recommendations, see "Saving index recommendations".

Database level capture

Before attempting to capture data for the TimesTen Index Advisor, make sure 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, make sure 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, make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Right-click the name of the database and select Index Advisor, then select Database Level Capture.

    Figure 4-32 Collecting data at the database level

    Description of Figure 4-32 follows
    Description of "Figure 4-32 Collecting data at the database level"

    • If your table statistics are outdated, an information dialog displays information about the outdated tables. It is recommended that your table statistics are up to date.

    • If your table statistics are up to date, the Index Advisor - Connection Level Capture dialog displays.

  2. To update your table statistics, click Cancel and follow the steps in "Updating the table and column statistics used by the query optimizer". To proceed without updating your table statistics, click OK.

    Figure 4-33 Outdated table statistics

    Description of Figure 4-33 follows
    Description of "Figure 4-33 Outdated table statistics"

    The Index Advisor - Database Capture dialog displays. Locate the Start button at the bottom of the dialog.

  3. In the Control tab of the Index Advisor - Connect Level Capture dialog, click Start.

    Figure 4-34 Index Advisor - Database Capture

    Description of Figure 4-34 follows
    Description of "Figure 4-34 Index Advisor - Database Capture"

    The Index Advisor Database Capture In Progress dialog displays. Locate the Details >> button.

  4. Click Details >>.

    Figure 4-35 Index advisor database capture progress

    Description of Figure 4-35 follows
    Description of "Figure 4-35 Index advisor database capture progress"

    A details pane expands that shows progress information about the index advisor database level capture. Locate the Close button at the bottom of the dialog.

  5. Click Close.

    The Index Advisor Database Capture In Progress dialog closes.

  6. Once you have captured your desired SQL workload, click Stop.

    Figure 4-37 Stop database capture

    Description of Figure 4-37 follows
    Description of "Figure 4-37 Stop database capture"

    If the TimesTen index advisor has recommendations, the Index Advisor Database Capture In Progress dialog displays.

    If the TimesTen index advisor does not have any recommendations, the Index Recommendation Feedback dialog displays. Close this dialog to return to the main SQL developer page.

  7. Click Details >>.

    Figure 4-38 Index advisor database capture progress

    Description of Figure 4-38 follows
    Description of "Figure 4-38 Index advisor database capture progress"

    A details pane expands that shows progress information about the index advisor database capture. Locate the Close button at the bottom of the dialog.

  8. Click Close.

    The Index Advisor Database Capture In Progress dialog closes. The Index Advisor Database Capture dialog shows information about the completed database capture. Locate the Index Recommendations tab at the top of the dialog.

  9. Click the Index Recommendations tab.

    Figure 4-40 Index recommendations for database capture

    Description of Figure 4-40 follows
    Description of "Figure 4-40 Index recommendations for database capture"

    The Index Recommendations tab of the Index Advisor - Database Capture dialog displays.

  10. In the Selector column, select the index recommendations that you want to create.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box you select to determine the index recommendations that you want to create.

    • Index Recommendation

      The CREATE INDEX statement that the TimesTen index advisor recommends.

    • Affected Statement Count

      The number of statements that benefit from the recommendation.

    • Created

      Shows if you have already created the index recommendation. This value can be Yes or No.

    To select all index recommendations, click the Select all check box.

  11. Once you select the index recommendations that you want to apply, click the Create Selected Indexes button.

    Figure 4-41 Create index recommendations

    Description of Figure 4-41 follows
    Description of "Figure 4-41 Create index recommendations"

    The Creating Selected Indexes dialog displays. Locate the Details >> button.

  12. Click Details >>.

    Figure 4-42 Creating Selected Indexes

    Description of Figure 4-42 follows
    Description of "Figure 4-42 Creating Selected Indexes"

    A details pane expands that shows progress information about creating the selected indexes. Locate the Close button at the bottom of the dialog.

  13. Click Close.

    The Creating Selected Indexes dialog closes. The indexes are created. To save the index recommendations, see "Saving index recommendations".

Saving index recommendations

These steps describe how to save index recommendations for future reference. These steps are valid for "Database level capture" and "SQL workload script" for a connection level capture.

  1. Click Save Indexes. By default, all index recommendations are saved. You cannot save individual index recommendations.

    The Save Indexes dialog displays.

  2. Select the directory where you want to save the TimesTen index advisor recommendations.

  3. In the File Name field, define the file name of your TimesTen index advisor recommendations.

    The default filename of the TimesTen index advisor recommendations is connection_name-indexadvice-YYYYMMDDMISS.sql, where connection_name is the name of the connection. YYYYMMDDHHMISS is a timestamp of when the recommendations were made, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, and SS the seconds.

  4. Click Save.

    Figure 4-45 Save index recommendations

    Description of Figure 4-45 follows
    Description of "Figure 4-45 Save index recommendations"

    Your index recommendations are saved.

    The saved file contains a header with following information:

    • The timestamp of when the report was generated.

    • The version of SQL Developer.

    • The version of the TimesTen database.

    • The name of the TimesTen connection.

    For example:

    -- This file was generated by SQL Developer at 2013-04-29 11:08:45
-- SQL Developer version 4.0.0.11.51
-- Database version: 11.02.02.0005 Oracle TimesTen IMDB version 11.2.2.5.0
-- Connection name: sampledb_1122

Viewing the TimesTen index advisor capture state

The TimesTen index advisor capture state enables you to view the status of connection and database level captures. To view the TimesTen index advisor capture state for a database, make sure you are on the main SQL Developer page.

  1. Right-click the name of the database and select Index Advisor, then select Index Advisor Capture State.

    Figure 4-46 Viewing the TimesTen index advisor capture state

    Description of Figure 4-46 follows
    Description of "Figure 4-46 Viewing the TimesTen index advisor capture state"

    The Index Advisor Capture State dialog displays and you see the current status of connection and database level captures.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Connection ID

      The connection ID of the connection that initiated the TimesTen index advisor capture.

    • Capture Level

      The capture level of the TimesTen index advisor capture. This value can be Connection or Database.

    • Capture Mode

      The capture mode of the TimesTen index advisor capture. This value can be Execute SQL or Prepare SQL.

    • Capture State

      The capture state of the TimesTen index advisor capture. This value can be In Progress or Completed.

    • Prepared Count

      The number of prepared SQL statements during the capture period.

    • Execute Count

      The number of executed SQL statements during the capture period.

    • Start Time

      The timestamp of when the index advisor capture began. The timestamp format is YYYY-MM-DD HH:MI:SS.FF, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, SS the seconds, and FF the milliseconds.

    • End Time

      The timestamp of when the index advisor capture ended. The timestamp format is YYYY-MM-DD HH:MI:SS.FF, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, SS the seconds, and FF the milliseconds. This value is empty if the Capture State is in In Progress.

  2. Click Refresh to update the table with the most current information of the TimesTen index advisor captures.

    Figure 4-47 Click refresh

    Description of Figure 4-47 follows
    Description of "Figure 4-47 Click refresh"

    The Index Advisor Capture State dialog is refreshed with the most recent capture state information.