SQL-J Language Reference
Page 80 of 121

RUNTIMESTATISTICS()

RUNTIMESTATISTICS() returns an object that implements the COM.cloudscape.types.RunTimeStatistics interface. A RunTimeStatistics object contains information about the query that just ran--compilation and execution time and a statement execution plan. The information it provides helps you evaluate the way Cloudscape is executing complex queries and may help you determine whether an index is being used as you expected and whether you can rewrite the query to run faster.

Statistics must be on for this function to return an object; it returns an object describing the most recently completed query associated with this connection. For more information about when the object is returned, see SET RUNTIMESTATISTICS statement. If the statistics timing attribute is also on, the object returns timing information as well.

RunTimeStatistics objects are serializable, so you can store them in a table for analysis later. The format and content of these objects typically change from release to release. There will be an upgrade path for stored 3.5 RunTimeStatistics objects in subsequent releases.

Syntax

RUNTIMESTATISTICS()

RUNTIMESTATISTICS() Example

This example uses ij.

-- set ij's maximum display width to very large so
-- you can see the full text
ij >
maximumdisplaywidth 5000;
-- turn on runtimestatistics and statistics timing
ij>
SET RUNTIMESTATISTICS ON;
ij> SET STATISTICS TIMING ON;
-- issue a query
ij>
SELECT COUNT(*)
FROM sys.syscolumns;
-- dump out the run time statistics info
-- for the previous query
ij>
VALUES RunTimeStatistics().toString();
Parse Time: 1011 Bind Time: 1492 Optimize Time: 1082 Generate Time: 3725 Compile Time: 7340 Execute Time: 1242 Begin Compilation Timestamp : 1998-04-27 16:49:00.638 End Compilation Timestamp : 1998-04-27 16:49:07.948 Statement Execution Plan Text: ProjectRestrictResultSet (0): . . . (etc.) -- issue another query
ij>
SELECT COUNT(*)
FROM sys.systables;
-- get the compile and execute times
ij>
SELECT a.getCompileTimeInMillis() AS compileTime,
a.getExecuteTimeInMillis()
AS executeTime
FROM (VALUES RunTimeStatistics()) rts (a);
COMPILETIME |EXECUTETIME ----------------------------------------- 1272 |271 -- issue another query ij>SELECT tablename, COUNT(*)
FROM sys.systables t, sys.syscolumns c
WHERE t.tableid = c.referenceid
GROUP BY tablename;
-- insert some of the info into a tracking table
ij>
INSERT INTO RunTimeStatisticsTable
SELECT a.getStatementName(),
a.getBeginExecutionTimestamp(), a.getEndExecutionTimestamp()
FROM (VALUES RunTimeStatistics()) rts (a);

How to Access and Use the Information

NOTE: The easiest way to view the information returned is to use Cloudview. For information, see Viewing Runtime Statistics in Cloudview in Tuning Cloudscape.

To access the information in the object returned, access one or more of the object's method calls from within an SQL-J statement. For information about the methods, see the API for the COM.cloudscape.types.RunTimeStatistics interface (located in /javadoc in the cloudscape base directory). Executing the toString method provides all the information in the object:

VALUES RUNTIMESTATISTICS().toString()

Executing the getScanStatisticsText method provides information about only those nodes that access a table or index. Using this method instead of the more complete toString method helps you weed out information that is not useful, if all you want to know is the access path:

VALUES RUNTIMESTATISTICS().getScanStatisticsText()

For an in-depth discussion of how to analyze the information, see Working with RunTimeStatistics in Tuning Cloudscape.

Information Presented

If the Statistics timing attribute is on, the RunTimeStatistics object provides information about how long each stage of the statement took. An SQL-J statement has two basic stages within Cloudscape: compilation and execution. Compilation is the work done when the statement is prepared. Compilation is composed of four stages: parsing, binding, optimization, and code generation. Execution is the actual evaluation of the statement. If the statistics timing attribute is off, it shows a "0" time for each stage.

The RunTimeStatistics object also provides information about the statement execution plan. A statement execution plan is composed of a tree of ResultSet nodes. A ResultSet node represents the evaluation of one portion of the statement; it returns rows to a calling (or parent node) and can receive rows from a child node. A node can have one or more children. Starting from the top, if a node has children, it requests rows from the children. Usually only the execution plans of DML statements (queries, inserts, updates, and deletes, not dictionary object creation) are composed of more than one node.

For inserts, updates, and deletes, rows flow out the top, where they are inserted, updated, or deleted. For selects (queries), rows flow out the top into a ResultSet that is returned to the user.

Nodes at the bottom are executed before the nodes above them. For example, a statement might show an IndexScan at the bottom (looking up key values in the index), then an IndexRowToBaseRowResultSet above that (retrieving the corresponding rows), then a SortResultSet on top (sorting the results).

Table 1-6 shows the many possible ResultSet nodes that might appear in an execution plan.

To learn more about how Cloudscape executes statements, see Chapter 4, "DML Statements and Performance" and Appendix A, "Internal Language Transformations" in Tuning Cloudscape.

Table 1-6 Types of ResultSet Nodes in a DML Execution Plan 

ResultSet Node Name

Description

AnyResultSet

Appears at the top of a quantified predicate subquery (EXISTS, IN, ANY, ALL). Calls a child node to evaluate the subquery and returns a row that contains a column with a boolean value that denotes whether the subquery evaluated to true.

CurrentOfResultSet

Appears under a DeleteNode or UpdateNode in the execution tree for a positioned delete or update. It returns the current row, if one exists, from the specified cursor.

DeleteResultSet

Appears at the top of the tree for a DELETE statement. All rows in this set are deleted from the base table and any indexes.

DistinctScalarAggregateResultSet

Represents nongrouped aggregation when there are one or more distinct aggregates. (Only one DISTINCT aggregate per query block is allowed.) A sort eliminates the duplicates when doing this type of aggregation. Does the aggregation for the following query:

select COUNT(c1), sum(distinct c2) from t1

GroupedAggregateResultSet

Represents grouped aggregation. Does the aggregation for the following query:

select c1, sum(c1) from t1 GROUP BY c1

HashJoinExistsJoinResultSet

Implements a hash join for an EXISTS type of join (one in which the right side of the join is probed only once per outer row).

HashJoinLeftOuterJoinResultSet

Implements a hash join for a left outer join. (Right outer joins are internally transformed into left outer joins during compilation.) Applies the outer join clause to the joined row to see if it qualifies. Returns those rows that qualify.

HashJoinResultSet

Implements a hash join between two tables. Returns rows from the join.

HashScanResultSet

Implements a hash scan (the inner table or virtual table) of a hash join. Displays the key columns.

IndexRowToBaseRowResultSet

Gets a row id from an index key in an index scan (IndexScanResultSet) and returns the matching row from the base table.

IndexScanResultSet

Represents a scan on an index. Can have qualifiers, which are the predicates that are evaluated in the store level before the IndexScanResultSet gets them.

  • An index scan with start and stop conditions is called a matching index scan; the start condition positions the scan at a starting point in the leaf level of the index; the scan traverses the leaf level of the index until the stop condition evaluates to false.
  • An index scan without start and stop conditions is called a non-matching index scan; the scan traverses the entire leaf level of the index.

For more information about start and stop conditions and qualifiers for index traversal, see What's Optimizable? in Tuning Cloudscape.

For both types of index scans, qualifiers restrict the rows returned from the scan.

InsertResultSet

Appears at the top of the tree for an INSERT statement. All rows in this set are inserted into the target table and any indexes.

MaterializeResultSet

Represents the materialization of an ExternalVirtualTable into a temp table.

NestedLoopExistsJoinResultSet

Implements a nested loop join for an EXISTS type of join (one in which the right side of the join is probed only once per outer row).

NestedLoopJoinResultSet

Implements a nested loop join between two tables. Returns rows from the join.

NestedLoopLeftOuterJoinResultSet

Implements a nested loop join for a left outer join. (Right outer joins are internally transformed into left outer joins during compilation.) Applies the outer join clause to the joined row to see if it qualifies. Returns those rows that qualify.

NormalizeResultSet

Does any necessary type conversions. Converts values in source row to the types that the parent ResultSet expects, and returns that row.

OnceResultSet

Appears at the top of an expression subquery (=, <>, >, >=, <, <= compared to a scalar subquery). Returns a row with the matching value from the subquery, if a single matching row exists, or a NULL if no matching value exists. The node throws an exception if a cardinality violation occurs (if more than one row is returned by the subquery).

ProjectRestrictResultSet

Can apply high-level restrictions and projections; evaluates those restrictions that are too expensive to push down to the base table or index (e.g., method calls and subqueries). Evaluates projections such as column elimination and expression evaluation for result columns. It also evaluates those restrictions that cannot be evaluated in a single table, such as t1.c1 = t2.c2.

Includes two boolean fields, restriction and projection.

RowResultSet

Represents a row constructor and returns a single row. It has no children. For example, appears at the bottom of the tree for the statement "VALUES (1,2,3)".

ScalarAggregateResultSet

Represents nongrouped aggregation when there are no distinct aggregates. No sort is involved when doing this type of aggregation. Does the aggregation for the following query:

SELECT COUNT(c1), SUM(c2) FROM t1

SortResultSet

Represents a sort or an aggregation. Returns the result rows from the sort or aggregation.

SortResultSet represents sorts for ordering or duplication elimination. Aggregation has been broken out into new ResultSets. (See ScalarAggregateResultSet, DistinctScalarAggregateResultSet, and GroupedAggregateResultSet.) A boolean field, eliminateDuplicates indicates whether the SortResultSet eliminates duplicates.

TableScanResultSet

Represents a scan on a base table. Can have qualifiers, which are the predicates that are evaluated in the store level before the
TableScanResultSet gets them.

UnionResultSet

Represents a UNION. Receives rows from the left ResultSet and from the right ResultSet and merges them.

UpdateResultSet

Appears at the top of the tree for an UPDATE statement. All rows in this set are updated in the table and in any indexes.

VTIResultSet

A ResultSet returned by an ExternalVirtualTable.

Subset of Information Presented

The portions of the statement plan that are returned in the getScanStatisticsText method are:

The output also contains information denoting the beginning and ending of a subquery block. (All nodes within the demarcation belong to a subquery.)

IndexScanResultSets and TableScanResultSets are the leaf nodes in the statement execution plan tree. The order in which the IndexScanResultSets and TableScanResultSets appear in the output String generally reflects their positions within the join order of the query block in which they appear.

The only current exception is for queries with UNIONs. There is no demarcation in the output between the two sides of a UNION. A UNION can appear within a derived table or in a subquery in the FROM list.

Multi-Threaded Applications

RUNTIMESTATISTICS() may not work as you would expect in an environment where threads are sharing a connection. RUNTIMESTATISTICS() returns the information from the most recently completed statement; if multiple threads are executing statements, you don't really know which one executed last. The solution is to put the execution of the statement you want to evaluate and the call to RUNTIMESTATISTICS() within a Java sync block and to similarly limit all other JDBC calls.

Non-DML Statements

The information returned for DDL statements may be sketchy. RUNTIMESTATISTICS() is most meaningful for DML statements.

The ResultSets that can make up a query execution plan are subject to change.

The information contained in the String returned by getQueryPlan, toString, or getScanStatisticsText is subject to change.