Capture Data for the TimesTen Index Advisor at the Connection Level

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" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

You have the following options:

Capture data from a 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.

    • 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" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide. To proceed without updating your table statistics, click OK.

    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.

    You are ready to choose if you want to use optimizer hints.

  4. The Include optimizer hints for Oracle BI server checkbox allows 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" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide. If you do not want to use optimizer hints, click Start.

    The Index Advisor Configuration dialog closes. A TimesTen index advisor pane displays at the bottom of the SQL worksheet.

  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 recommendation. This value can be Yes or No.

    If you want to select all index recommendations, click the Select all check box.

    If you want 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.

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

  7. Click Details >>.

    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. If you want to save the index recommendations, see Saving index recommendations from a 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.

    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
    

Capture data from a 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.

    • 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" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide. To proceed without updating your table statistics, click OK.

    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.

    You are ready to specify your 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.

    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 allows 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" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide. If you do not want to use optimizer hints, click Start.

    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 >>.

    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.

    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 recommendation. This value can be Yes or No.

    If you want to select all index recommendations, click the Select all check box.

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

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

  14. Click Details >>.

    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. TimesTen created the indexes. If you want to save the index recommendations, see "Saving index recommendations" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

Related Topics

SQL Developer: TimesTen Tasks

SQL Developer Concepts and Usage