![]() |
Tuning Databases and Applications
|
Reference Manual |
Working with RunTimeStatisticsCloudscape provides a language-level tool for evaluating the performance and the execution plans of statements, the RUNTIMESTATISTICS() built-in function. OverviewWhen a special attribute (RunTimeStatistics) is turned on for a connection, Cloudscape creates an object that implements the COM.cloudscape.types.RunTimeStatistics interface for each statement executed within the Connection until the attribute is turned off. For the most recently executed query, the object displays information about:
The exact details presented, as well as the format of presentation, may change. You access the object created with the RUNTIMESTATISTICS() built-in function. You generally do not work with the object directly, but instead call one of its methods directly on the function. See RUNTIMESTATISTICS() in the Cloudscape Reference Manual. How You Use ItNOTE: Cloudview presents an easy-to-use interface for runtime statistics. See Viewing Runtime Statistics in Cloudview. These are the basic steps for working with the RUNTIMESTATISTICS() function.
These steps have shown you how you would work with RunTimeStatistics within ij. The basic steps for working with RunTimeStatistics are the same in a java program. For a complete coding example, see the sample program JBMSTours.RunTimein the JBMSTours application. Run the program for examples of several different kinds of statements and the different output they produce. NOTE: The exact content and format of the statement execution plan are subject to change. Analyzing the InformationStatistics TimingIf 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 while the statement is prepared. Compilation is composed of the following 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 zero time for each stage.) Statement Execution PlanThe RunTimeStatistics object also provides information about the statement execution plan. A statement execution plan is composed of a tree of result set nodes. A result set 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 example, consider the following query: This simple query involves one node only--reading all the data out of the Countries table. It involves a single node with no children. This result set node is called a TableScanResultSet. RunTimeStatistics' text for this node looks something like this: TableScanResultSet for COUNTRIES at read committed isolation level using share row locking chosen by the optimizer.
Consider this second, more complex query:
SELECT Country When executed, this query involves two nodes--one to retrieve qualifying rows (the restriction is done at this node) and one to project the requested columns. So, at bottom, there is a TableScanResultSet for scanning the table. The qualifier (Region = `Central America') is evaluated in this node. These data are passed up to the parent node, called a ProjectRestrictResultSet, in which the rows are projected--only the country column is needed (the first column in the table). RunTimeStatistics' text for these two nodes looks something like this: ProjectRestrictResultSet (1):
Other, more complex queries such as joins and unions have other types of result set nodes. For inserts, updates, and deletes, rows flow out of the top, where they are inserted, updated, or deleted. For selects (queries), rows flow out of the top into a result set that is returned to the user. Table 1-6 in the Cloudscape Reference Manual shows the many possible ResultSet nodes that might appear in an execution plan. Some examples are:
In addition, read Chapter 4, "DML Statements and Performance", for more information about some of the ways in which Cloudscape executes statements. The statement execution plan shows how long each node took to evaluate, how many rows were retrieved, whether an index was used, and so on. If an index was used, it shows the start and stop positions for the matching index scan. Looking at the plan may help you determine whether to add an index or to rewrite the query. Subset of Statement Execution PlanYou can execute a method called getScanStatisticsText on the RunTimeStatistics interface that returns a string describing only a subset of the full statement execution plan. It provides information only about those nodes that access a table or index. Using this method is the easiest way to find out whether Cloudscape used an index or scanned the entire table and what the join order was. Optimizer EstimatesRuntime statistics show the optimizer estimates for a particular node. They show the optimizer's estimated row count and the optimizer's "estimated cost." The estimated row count is the query optimizer's estimate of the number of qualifying rows for the table or index for the entire life of the query. If the table is the inner table of a join, the estimated row count will be for all the scans of the table, not just for a single scan of the table. NOTE: You can execute a method called getEstimatedRowCount, which returns the optimizer's estimated row count. (For a tip that explains when that might be useful, see Using RunTimeStatistics to Get an Approximate Row Count for a Table in the Cloudscape Developer's Guide.) The estimated cost consists of a number, which is a relative number; it does not correspond directly to any time estimate. It is not, for example, the number of milliseconds or rows. Instead, the optimizer constructs this number for each possible access path. It compares the numbers and chooses the access path with the smallest number. Viewing Runtime Statistics in CloudviewCloudview makes working with runtime statistics easy. To use it:
Figure 3-5 ResultSet nodes displayed by Cloudview Figure 3-5 shows the ResultSet nodes for the query used as an example in Join Order Case Study. It allows you to see at a glance that the query was executed as a nested loop join; that FlightAvailability is the outer table in the join (because it is the first ResultSet); and that Cloudscape used an index to look up values on the inner table, Flights (because there is an Index scan on Flights, with the name of the index, Flights_Pk). IndexRowToBaseRowResultSet is the node in which Cloudscape uses the row ID it got from the index key in the index scan to return the matching row from the base table.
|
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |