5 Working with 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. TimesTen stores table level statistics 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.
TimesTen does not compute table and column statistics as updates occur on regular and cache tables. Instead, TimesTen updates statistics when an explicit request is made.
You can update statistics for all tables owned by a user or for a specific table owned by an user.
-
To update statistics for all tables, right-click the Tables node and select Statistics, then select 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 select Statistics, then select Update.
If you are unable to find the table, see View 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 for which statistics are updated. When you invalidate the execution plan of a statement, TimesTen recompiles or reprepares that statement 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.
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 Locating a TimesTen database object.
In the Statistics tab of the table pane, 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, the following information is displayed:
-
The name of the column
-
The number of intervals the data is divided into 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, SQL Developer displays the following:
-
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 Introduction, "When optimization occurs" in the Oracle TimesTen In-Memory Database Operations Guide or "ttOptUpdateStats" in the Oracle TimesTen In-Memory Database Reference.
Exporting table statistics
You can generate a set of SQL statements that allow you to restore the table statistics to the current state. You can generate SQL statements for a single table or for user tables that the connection user has permission to access. After you export your table statistics to a .sql
file, you can import your table statistics by opening the .sql
file in SQL Developer and running the script file.
To export the current TimesTen table statistics, ensure that you are on the main SQL Developer page.