Capture the Data Used for Generating Index Recommendations

Call the ttIndexAdviceCaptureStart and ttIndexAdviceCaptureEnd built-in procedures to capture the information needed by the Index Advisor to generate index recommendations.

  1. Call the ttIndexAdviceCaptureStart built-in procedure to start the process to collect index information.

  2. Run the SQL workload.

  3. Call the ttIndexAdviceCaptureEnd built-in procedure to end the index information collection process.

Note:

After the data collection process ends, you can retrieve the index recommendations as described in Retrieve Index Recommendations and Data Collection Information.

When you call the ttIndexAdviceCaptureStart built-in procedure to initiate the data collection process, provide the following:

  • In the captureLevel parameter, specify whether the index information is to be collected for the current connection or for the entire database. You can run multiple connection-level captures concurrently for independent connections without conflict. A database-level capture can take place in parallel with a connection-level capture. Since there is no conflict between a database-level and a connection-level capture, any outstanding connection-level captures that are already in progress when a database-level capture is initiated completes as intended. However, an error is returned if you initiate a second request for a database-level capture while the first is still active; an error is also returned if a second request for a connection-level capture from the same connection is initiated while the first connection-level capture is still active.

    If you invoke ttIndexAdviceCaptureStart for a database-level capture, any outstanding connection-level captures that are already in progress complete.

  • The captureMode parameter designates that you want the data collection performed on one of the following scenarios:

    • Perform the collection of index information using the current processing of the SQL workload.

    • Base the collection of index information not on a current processing of the SQL workload, but on existing computed statistics and query plan analysis. In this scenario, the SQL statements have been prepared, but not run. This mode can only be performed with a connection-level capture.

To complete the capture, call the ttIndexAdviceCaptureEnd built-in procedure that ends either an active connection-level capture from the same connection or an active database-level capture. Completing a database-level capture requires the ADMIN privilege.

If a connection fails during a capture, the following occurs:

  • If the capture is a connection-level capture, the capture ends and all associated resources are freed.

  • If the capture is a database-level capture, the capture continues until another user with ADMIN privileges connects and invokes the ttIndexAdviceCaptureEnd built-in procedure to end a database-level capture.

If temporary space becomes full during a capture, an active capture ends and the data collected during the capture is saved.

Note:

Run ttIndexAdviceCaptureDrop to free the temporary space after a capture. See Drop Data Collected for the Index Advisor and Finalize Results.

The following example starts a collection for the Index Advisor at the connection-level for the current processing of a SQL workload:

call ttIndexAdviceCaptureStart(0,0);

The following example ends the collection for the connection-level capture:

call ttIndexAdviceCaptureEnd(0);

Note:

See ttIndexAdviceCaptureStart and ttIndexAdviceCaptureEnd in the Oracle TimesTen In-Memory Database Reference.