|Oracle® SQL Developer Oracle TimesTen In-Memory Database Support User's Guide
|PDF · Mobi · ePub|
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
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 "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 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.
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".
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
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 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.
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, make sure you are on the main SQL Developer page.
To save table statistics for a single table, right-click the name of the table and select Statistics, then select Export. To save table statistics for all user tables, right-click Tables and select Statistics, then select Export.
The Export Optimizer Statistics dialog displays. Locate the Save button at the bottom right of the dialog.
The Save Optimizer Statistics Export Commands dialog displays.
Select the directory where you want to save the current TimesTen table statistics.
In the File Name field, define the name of your current TimesTen table statistics.
The default filename of the current TimesTen table statistics is
connection_name is the name of the connection.
YYYYMMDDHHMISS is a timestamp of when the recommendations were made, where
YYYY is the year,
MM the month,
DD the day,
HH the hour,
MI the minutes, and
SS the seconds.
TimesTen saves your current TimesTen table statistics.
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-7, "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.
The Select Connection dialog appears. In the Connection field, select the name of the connection. Click OK.
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.
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.
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.