This page provides an overview of the TimesTen Index Advisor and an example of running it. For more details, refer to the TimesTen Documentation

Overview

TimesTen Index Advisor evaluates a given SQL workload and recommends optimal indexes to improve the query response time.

The Index Advisor supports three different modes: connection-level (default mode), connection-level with SQL preparation only, and database-level. The table below describes the usage scenarios and the corresponding commands required for each of the modes specified.

ttIsql
connect "DSN=xxx;UID=xxx;PWD=xxx";
call ttOptUpdateStats(,1);
call ttIndexAdviceCaptureStart(0);

<SQL query 1>;
<SQL query 2>;
...
<SQL query n>;

call ttIndexAdviceCaptureEnd(0);
call ttIndexAdviceCaptureOutput(0);
quit;

ttIsql
connect "DSN=xxx;UID=xxx;PWD=xxx";
call ttOptUpdateStats(,1);
call ttIndexAdviceCaptureStart(0,31);

<SQL query 1>;
<SQL query 2>;
...
<SQL query n>;

call ttIndexAdviceCaptureEnd(0);
call ttIndexAdviceCaptureOutput(0);
quit;

ttIsql
connect "DSN=xxx;UID=xxx;PWD=xxx";
call ttOptUpdateStats(,1);
call ttIndexAdviceCaptureStart(1);
quit;

<run the SQL workload>

ttIsql
connect "DSN=xxx;UID=xxx;PWD=xxx";
call ttIndexAdviceCaptureEnd(1);
call ttIndexAdviceCaptureOutput(1);
quit;

Prerequisite: Index Advisor is available in TimesTen 11.2.2.4 or later releases.

Running Index Advisor in ttIsql

The following section provides step-by-step instructions on how to run the Index Advisor at connection-level (SQL preparation only).

1. Obtain the SQL Query Workload

The first step is to identify and obtain the SQL queries that require performance tuning or that may benefit from additional indexes. The SQL query workload can be one query or multiple queries.

Note: if you cannot get the physical SQL queries, or your workload spans multiple connections, run index advisor using the database-level commands mentioned in the table in the Overview section above.


2. Update TimesTen Statistics

Index Advisor recommends optimal indexes by evaluating the SQL workload based on TimesTen table statistics (rows and columns). Statistics must be up-to-date before running the Index Advisor.

Assuming the database DSN is TT_AGGR_STORE and the tables your SQL workload access are under two users EXALYTICS and SCOTT, you can run the following commands to collect statistics for all tables under these two users.

ttIsql
Command> connect "DSN=TT_AGGR_STORE;UID=EXALYTICS;PWD=<PASSWORD OF EXALYTICS>"; 
Command> call ttOptUpdateStats(,1);

Command> connect "DSN=TT_AGGR_STORE;UID=SCOTT;PWD=<PASSWORD OF SCOTT>"; 
Command> call ttOptUpdateStats(,1);
Command> quit;

Note: Updating statistics may take a while depending on the number of rows in the table and CPU speed. Typically you need to update the statistics after the table incurs major changes to the data set. For example, if you updated the statistics yesterday and there has been no bulk changes to the tables, it is not necessary to update the statistics again.

3. Run Index Advisor Commands

As an example, here's how to run the Index Advisor using the connection-level (SQL preparation only). The benefit of running the query with PREPARE only is that the query may run for a while without proper indexes. Using (0,31) specifies to the Index Advisor to skip the execution portion.

ttIsql
Command> connect "DSN=TT_AGGR_STORE;UID=EXALYTICS;PWD=<PASSWORD OF EXALYTICS>"; 

Command> call ttIndexAdviceCaptureStart(0,31);  -- (0,31) specifies connection level with SQL Prepare only

Command> <SQL 1>;
Command> <SQL 2>;
Command> ...
Command> <SQL n>;

Command> call ttIndexAdviceCaptureEnd(0);
Command> call ttIndexAdviceCaptureOutput(0); 
Command> quit;

A sample output from "call ttIndexAdviceCaptureOutput(0)" looks like:

call ttIndexAdviceCaptureOutput(0);
< 2, create unique hash index FACT_AGG_TT_17M_i57 on EXALYTICS.FACT_AGG_TT_17M(DEST_AIRPO00039C2A); >
< 1, create hash index FACT_AGG_TT_17M_i57 on EXALYTICS.FACT_AGG_TT_17M(DEST_AIRPO00039C2A); >
< 1, create hash index FACT_AGG_TT_08Y_i34 on EXALYTICS.FACT_AGG_TT_08Y(DEP_TIME_B00039BA6); >
< 2, create hash index FACT_AGG_TT_08Y_i38 on EXALYTICS.FACT_AGG_TT_08Y(DEP_YEAR00039C0A); >
< 1, create hash index FACT_AGG_TT_08Y_i40 on EXALYTICS.FACT_AGG_TT_08Y(DISTANCE_G00039B9F); >
5 rows found.

You should review the indexes recommended by the index advisor, some of them might already exist. Create the recommended indexes, and then try your SQL queries again to determine if there is any performance improvement.

Note that the first two indexes recommended have the same index name FACT_AGG_TT_17M_i57. This is by design: we first try to create this index as a unique hash index for optimal performance, but if it fails due to unique constraints, we will create it as a non-unique hash index, as the second row suggested.

Follow the summary of in the Overview section for commands to use for the other two modes.

4. Run Index Advisor Commands Again, If Necessary

After you create the indexes recommended, sometimes it helps to repeat step #3 and re-run Index Advisor to get additional indexes.

Running Index Advisor in SQL Developer

SQL Developer 4.0 provides users with a GUI interface to run the Index Advisor.

Check out the SQL Developer - TimesTen Index Advisor viewlet, and the SQL Developer documentation - TimesTen Index Advisor section for details.

Download the latest SQL Developer software here.