7 Interfacing with the TimesTen Query Optimizer

You can interact with the TimesTen query optimizer by updating the table and column statistics that are stored in the TimesTen database and used by the query optimizer to generate execution plans. You can also display the execution plan for a statement.

Update the table and column statistics used by the query optimizer

The query optimizer uses statistics stored in TimesTen database system tables to compute the optimal execution plan for a statement. Table-level statistics such as the number of rows in a table are stored in the SYS.TBL_STATS table. Column-level statistics such as the minimum and maximum value, and the number of unique values in a column are stored in the SYS.COL_STATS table.

Table and column statistics are not computed as updates occur on regular and cache tables. Instead statistics are updated when an explicit request is made to compute them.

Click the + to the left of the Tables node to view the list of tables. Right-click the name of the table that you want to update the optimizer statistics for.

If you are unable to find the table that you are looking for, see "Locate a TimesTen database element" for information.

After you right-click the name of the table that you want to update the optimizer statistics for, select Statistics > Update.

In the Prompts tab of the Update statistics dialog box, put a check in the Invalidate referenced commands check box if you want the invalidate the execution plans of statements that reference the table whose statistics will be updated. Invalidating a statement's execution plan causes that statement to be recompiled or reprepared upon its next execution.

In the Interval type for table statistics pull-down menu, select Complete interval to divide the rows of the table into two or more intervals and compute statistics on each interval, or Single interval to compute statistics on the entire set of rows as a single interval. A range index must be defined on the table in order to collect complete interval statistics.

Click the Apply button to update statistics for the table.

To view the optimizer statistics of a table, click the name of the table. If you are unable to find the table that you are looking for, see "Locate a TimesTen database element" for information.

In the Statistics tab within the table's tab, the top section shows the table statistics such as:

• The number of rows in the table

• A timestamp indicating when statistics were most recently updated for the table

The bottom section shows the column statistics. For each column, it shows:

• Its name

• The number of intervals the data was divided into in order to compute statistics

• Total number of NULL values

• Total number of non-NULL unique values

• Total number of rows in each interval

For each interval of each column, it shows:

• The number of unique values other than the most frequently occurring value

• The number of rows that contain a value other than the most frequently occurring value

• The number of rows that contain the most frequently occurring value

• The minimum value

• The maximum value

• The most frequently occurring value

For more information about query optimizer statistics, see "Statistics" in the Oracle TimesTen In-Memory Database Cache Introduction, "When optimization occurs" in the Oracle TimesTen In-Memory Database Operations Guide, or "ttOptUpdateStats" in the Oracle TimesTen In-Memory Database Reference.

View the execution plan of a statement

Before a statement can be executed, it must first be prepared or compiled. The query optimizer generates an optimal execution plan for the statement when the statement is being prepared.

To view the execution plan of a statement, you must first type the statement in SQL Worksheet. If you do not have a worksheet open in your connection, right-click the node that contains the name of your database connection and select Open SQL Worksheet.

If you do not have a connection open, click the SQL Worksheet icon to establish a connection and then open a worksheet for that connection.

In the Select Connection dialog box, select the name of the connection in the Connection pull-down menu that you want to open a SQL Worksheet for, and then click the OK button.

A connection to the TimesTen database will be established if a connection based on the selected connection name does not already exist.

Before typing the statement in SQL Worksheet, make sure appropriate indexes have been created on the tables that are being referenced in the statement, and that table and column statistics have been updated among other performance tuning tasks. For more information about updating statistics, see "Update the table and column statistics used by the query optimizer".

Type the statement in SQL Worksheet. Then click the Explain Plan icon to display the execution plan for the statement.

The execution plan is shown in the Explain Plan tab and is displayed in a hierarchical format in reverse chronological order such that the operation at the bottom of the tree is processed first and the operation at the top of the tree is processed last. For each operation, the plan shows which table or index the operation is performed on, or which expressions of the statement's predicate the operation is processing, if applicable. Types of operations that can appear in the execution plan include table or index scan (with lock granularity shown), join, group and sort.

For more information about interpreting an execution plan, see "Optimizer plan" in the Oracle TimesTen In-Memory Database Cache Introduction or "Viewing SQL query plans" in the Oracle TimesTen In-Memory Database Operations Guide.

For more information about query performance tuning, see "SQL tuning" in the Oracle TimesTen In-Memory Database Operations Guide.