SQL Query Optimization Messages › Execution-Time Messages › Execution-Time Summary Messages

Execution-Time Summary Messages

This section describes the summary execution messages written to the SYSADM.SYSMSG table when a plan is closed.

There is a header message for changes in plan, and with plan for each statement, followed by detail messages for each table.

Messages are grouped by plan and statement, but they are ordered in the reverse sequence in which they were first executed.

Plan Header

  *** EXECUTION STATS FOR PLAN authId.planName

Statement Header

  STMT NBR: nnnnnnnnn TYPE: xxxxxxxxxxxx EXECUTIONS: nnnnnnnnn ROWS: nnnnnnnnn

TYPE

Type of statement as indicated by the following table:

Type Code Process

FETCH CRS

Open/Fetch Cursor

SELECT INTO

Select Into

INSERT ROW

Insert Row

INSERT SET

Insert Searched

UPDATE CRS

Update Positioned

UPDATE SET

Update Searched

DELETE CRS

Delete Positioned

DELETE SET

Delete Searched

EXECUTIONS

Number of times the statement was executed

ROWS

Number of rows fetched, inserted, updated, or deleted.

Table Level

These messages are in order by query block level and position, and table process sequence.

 LVL NBR ------ TABLE NAME ------ --SETS-- --INDEX- --DATA-- --QUAL-- ---I/O--
 nnn nnn xxxxxxxxxxxxxxxxxxxxxxxx nnnnnnnn nnnnnnnn nnnnnnnn nnnnnnnn nnnnnnnn

LVL and NBR

These fields indicate the query block in which the table is used. LVL is zero for subselects, and for subqueries indicates the level of nesting. When there are multiple select blocks at the same level, NBR indicates the order in which the select block appears in the SQL statement.

TABLE NAME

The base table name or kind of temporary table. If you have referenced the same table multiple times, or tables with the same name but different authId, in the same FROM clause, these messages are in the order the tables appear in the FROM clause. If you referenced a view with a join, the view reference has been expanded into a reference for each table in the view's FROM clause.

SETS

If the statement is executed multiple times, or the table is an inner table in a nested-loop join, or in a correlated subquery, this is the number of times a base table has been searched or a temporary table built. Divide SETS by statement EXECUTIONS to compute SETS per execution.

INDEX

The total number of index entries read.

DATA

The total number of data records read. The difference between INDEX and DATA is the number of rows rejected by low-order predicates, and the additional index intersection probes made if index intersection is used.

QUAL

The total number of qualified by Compound Boolean Selection (CBS) rows. Any rows rejected by predicates evaluated by the SQL subsystem, are not included. The difference between QUAL and DATA is the number of rows rejected by data scanning, that is to say, predicates evaluated by the Compound Boolean Selection Facility that reference a column that is not in the scan index. Also, if index merging is used, the difference includes the elimination of duplicates, where the same row was found in multiple indexes.

I/O

The total physical read and write I/O commands charged to requests accessing the table. A read-only task may be charged write I/Os if the buffer it needs is in a write pend status.

The ratio (INDEX + DATA) divided by I/O will tend to be the lowest for sequential access in data area physical sequence. Random access requires more I/O because each level of the index may need to be read for each row.