ttIndexAdviceCaptureStart

This procedure enables index advice capture. It is recommended that statistics be updated before you call this procedure, using ttOptEstimateStats and setting the 'invalidate' parameter set to 'yes'. Updating the statistics in this way ensures statistics are up to date and forces statements to be re-prepared during the capture. To set statistics to known values instead, call ttOptSetTblStats with the 'invalidate' parameter set to 'yes'.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see Using the Index Advisor to Recommend Indexes in the Oracle TimesTen In-Memory Database Operations Guide.

Required Privilege

This procedure requires no privilege to start a connection level capture.

This procedure requires ADMIN privileges to start a database level capture.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has no related views.

Syntax

ttIndexAdviceCaptureStart([captureLevel], [captureMode])

Parameters

ttIndexAdviceCaptureStart has these optional parameters:

Parameter Type Description

Parameter	Type	Description
`captureLevel`	`TT_INTEGER`	Supported values for the capture level are: `0` - Outputs index advice at the connection level for the current connection. This is the default value. `1` - Outputs index advice at the database level.
`captureMode`	`TT_INTEGER`	Supported values for the capture mode are: `0` - Provides complete capture of index advice including execution of the SQL statements. This is the default. `31` - Capture is based only on the computed statistics and plan analysis. Queries (`SELECT` statements only) are prepared but not executed. This mode can only be used with connection level captures (`captureLevel=0`).

captureLevel

TT_INTEGER

Supported values for the capture level are:

0 - Outputs index advice at the connection level for the current connection. This is the default value.

1 - Outputs index advice at the database level.

captureMode

TT_INTEGER

Supported values for the capture mode are:

0 - Provides complete capture of index advice including execution of the SQL statements. This is the default.

31 - Capture is based only on the computed statistics and plan analysis. Queries (SELECT statements only) are prepared but not executed. This mode can only be used with connection level captures (captureLevel=0).

Result Set

ttIndexAdviceCaptureStart returns no results

Examples

The following example starts a collection for the Index Advisor at the connection-level.

CALL ttIndexAdviceCaptureStart(0,0);

Note:

It is an error to call this procedure if index advice is already being captured at the level specified by the captureLevel parameter or at the connection level if no level is specified. Connection level captures can be issued concurrently on independent connections without conflict. Outstanding connection level captures that are in progress when a database level capture begins complete as intended.