Skip Headers
Oracle® SQL Developer Oracle TimesTen In-Memory Database Support User's Guide
Release 3.1

Part Number E25286-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

8 Interfacing with the TimesTen Query Optimizer

This chapter shows you how to interact with the TimesTen query optimizer in SQL Developer. Topics include:

Updating the table and column statistics used by the query optimizer

The query optimizer uses statistics stored in TimesTen database system tables to determine 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.

You can update statistics for all tables owned by an user or for a specific table owned by an user.

To update statistics for all tables, right-click the Tables node and choose Statistics > Update.

To update statistics for a specific table, click the + to the left of the Tables node. Right-click the name of the table and choose Statistics > Update.

Figure 8-1 Update statistics for a table

Description of Figure 8-1 follows
Description of "Figure 8-1 Update statistics for a table"

If you are unable to find the table, see "Viewing the characteristics of a table".

In the Prompts tab of the Update statistics dialog, choose Invalidate referenced commands to invalidate the execution plans of statements that reference the table on which statistics are updated. Invalidating a statement's execution plan causes that statement to be recompiled or reprepared upon its next execution.

For Interval type for table statistics, choose 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. You must define a range index on the table to compute complete interval statistics.

Click Apply.

Figure 8-2 Update statistics dialog

Description of Figure 8-2 follows
Description of "Figure 8-2 Update statistics dialog"

To view the optimizer statistics of a table, click the name of the table.

Figure 8-3 Statistics for employees table

Description of Figure 8-3 follows
Description of "Figure 8-3 Statistics for employees table"

If you are unable to find the table that you are looking for, see "Locating a TimesTen database object".

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

The bottom section shows the column statistics. For each column, the following is displayed:

For each interval of each column, the following is displayed:

For more information about query optimizer statistics, see "Statistics" in the Oracle 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.

Viewing the execution plan of a statement

A statement must be prepared or compiled before it is executed. When the statement is prepared or compiled, the query optimizer generates an execution plan for the statement.

To view the execution plan of a statement, create the statement in the SQL Worksheet. If you do not have a worksheet open in your connection, right-click the node of the connection name and select Open SQL Worksheet. See Figure 1-3, "Viewing a TimesTen connection".

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

Figure 8-4 Selecting the SQL Worksheet

Description of Figure 8-4 follows
Description of "Figure 8-4 Selecting the SQL Worksheet"

The Select Connection dialog appears. In the Connection field, select the name of the connection. Click OK.

Figure 8-5 Select Connection dialog

Description of Figure 8-5 follows
Description of "Figure 8-5 Select Connection dialog"

A connection to the TimesTen database is established if a connection associated with the selected connection name does not exist.

Before composing 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. For more information about updating statistics, see "Updating the table and column statistics used by the query optimizer".

Compose the statement in the SQL Worksheet. Then choose Explain Plan to display the execution plan for the statement.

Figure 8-6 Viewing explain plan

Description of Figure 8-6 follows
Description of "Figure 8-6 Viewing explain plan"

The execution plan is shown in the Explain Plan pane 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 the table or index the operation is performed on, or if applicable, the expressions of the statement's predicate the operation is processing. Types of operations that can appear in the execution plan include table or index scan (with lock granularity shown), join, group and sort.

Figure 8-7 Viewing the execution plan

Description of Figure 8-7 follows
Description of "Figure 8-7 Viewing the execution plan"

For more information about interpreting an execution plan, see "Optimizer plan" in the Oracle 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 "Viewing SQL query plans" in the Oracle TimesTen In-Memory Database Operations Guide.