Capture Data for the TimesTen Index Advisor at the Database 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.

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.

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

    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.

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

  4. Click Details >>.

    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.

    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. You may close this dialog to return to the main SQL developer page.

  7. If the TimesTen index advisor has recommendations, click Details >>.

    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.

    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.

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

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

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

  13. Click Close.

    The Creating Selected Indexes dialog closes. The indexes are created. 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