See "Working with RunTimeStatistics" in
for information
on how to configure this.
Most statements have at least one result set associated with
them, and some complex statements may have many result sets
associated with them. Some statements, for example DDL statements
such as CREATE TABLE, have no result sets associated with them.
Each row in this table describes a particular result set used
by a particular statement. Rows
in this table are typically joined with rows in
SYSXPLAIN_STATEMENTS during analysis:
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt_id = rs.stmt_id
Rows in this table are added
automatically when
has been configured appropriately. The
rows remain in the table until you delete them or drop the table.
The following table shows the contents of the SYSXPLAIN_RESULTSETS system
table.
SYSXPLAIN_RESULTSETS system table
For each column in the SYSXPLAIN_RESULTSETS system table, this table provides its data type, its length in bytes, whether it is nullable, and a description of its contents.
Column Name
Type
Length
Nullable
Contents
RS_ID
CHAR
36
false
A unique identifier for this particular row.
OP_IDENTIFIER
VARCHAR
32,672
false
A code indicating what type of result set these statistics
are for.
Common result set types include TABLESCAN, INDEXSCAN, and PROJECTION.
OP_DETAILS
VARCHAR
32,672
true
Additional string information which varies for each
different type of result set. Interpreting this information currently
requires reading the
source code to know what values are being displayed here.
NO_OPENS
INTEGER
10
true
Number of times this result set was opened during
execution of the containing statement.
NO_INDEX_UPDATES
INTEGER
10
true
The number of index updates performed by this
result set. This value is NULL for result sets used by queries, but may have
a non-zero value for modification statements such as INSERT, UPDATE, or
DELETE.
LOCK_MODE
CHAR
2
true
A code indicating the locking level that was used
for this result set: 'EX' for exclusive table-level locking,
'SH' for share table-level locking,
'IX' for exclusive row-level locking, or
'IS' for share row-level locking.
LOCK_GRANULARITY
CHAR
1
true
A code indicating the locking granularity that
was used for this result set: 'T' for table-level locking, or
'R' for row-level locking.
PARENT_RS_ID
CHAR
36
true
The result sets for a particular statement are
arranged in a parent-child tree structure. The output rows from one
result set are delivered as the input rows to its parent. This column
stores the identifier of the parent result set. For the outermost
result set in a particular statement, this column is NULL. Note that sometimes
there are multiple result sets with the same parent result set (that is,
some nodes have multiple children): for example, a UNION result set will have
two child result sets, representing the two sets of rows which are UNIONed
together.
EST_ROW_COUNT
DOUBLE
52
true
The optimizer's estimate of the total number of rows
for this result set.
EST_COST
DOUBLE
52
true
The optimizer's estimated cost for this result set.
The value indicates the number of milliseconds that the optimizer
estimates it will take to process this result set.
AFFECTED_ROWS
INTEGER
10
true
This column is non-null only for INSERT, UPDATE, and
DELETE result sets. For those result sets, this column holds the number of
rows which were inserted, updated, or deleted, respectively.
DEFERRED_ROWS
CHAR
1
true
This column is only non-null for INSERT, UPDATE, and
DELETE result sets. For those result sets, this column holds 'Y' if
the INSERT/UPDATE/DELETE is being performed using deferred change semantics,
and holds 'N' otherwise. Deferred change semantics are used
when self-referencing is taking place.
INPUT_ROWS
INTEGER
10
true
This column is used for SORT, AGGREGATE, and GROUPBY
result sets, and indicates the number of rows that were input to the
result set, and thus were sorted by the sorter.
SEEN_ROWS
INTEGER
10
true
For join and set nodes, this is the number of rows
seen by the "left" side of the processing. For aggregate, group, sort,
normalize, materialize, and certain other nodes, this is the number of
rows seen.
SEEN_ROWS_RIGHT
INTEGER
10
true
For join and set nodes, this is the number of rows
seen by the "right" side of the processing. For example, in the statement
select country
from countries
union
select country
from countries
where region = 'Africa'
the UNION result set has SEEN_ROWS = 6 and SEEN_ROWS_RIGHT = 19.
FILTERED_ROWS
INTEGER
10
true
This column holds the number of rows which were
eliminated from the result set during processing.
RETURNED_ROWS
INTEGER
10
true
This column holds the number of rows which were
returned by the result set to its caller. Generally speaking, the number
of returned rows is the number of rows INPUT or SEEN, minus the number
of rows FILTERED.
EMPTY_RIGHT_ROWS
INTEGER
10
true
This column is used for left outer joins, and, if not null,
holds the number of empty rows which had to be constructed because no
existing rows met the join criteria.
INDEX_KEY_OPT
CHAR
1
true
This column records when the Index Key Optimization is
used. The Index Key Optimization is a special optimization which occurs when
a query references the MAX or MIN value of a column which happens to have an
index, and so the MIN or MAX computation can be performed by fetching the
first or last, respectively, entry in the index, as in:
select max(country_iso_code)
from countries
SCAN_RS_ID
CHAR
36
true
If this resultset is one of the resultset types which
performs a scan of a table or index, this column contains the id value
which identifies the particular row in SYSXPLAIN_SCAN_PROPS that
describes the statistics related to the scan behavior.
SORT_RS_ID
CHAR
36
true
If this resultset is one of the resultset types which
performs a sort of a table or index, this column contains the id value
which identifies the particular row in SYSXPLAIN_SORT_PROPS that
describes the statistics related to the sort behavior. The most common
situations which involve sorting of the data are when processing the
ORDER BY and GROUP BY clauses.
STMT_ID
CHAR
36
false
This column will contain the ID value which identifies
the particular statement for which this result set was executed. Note that
there may be multiple result sets executed for a single statement, so a join
between the SYSXPLAIN_STATEMENTS table and the SYSXPLAIN_RESULTSETS table
may retrieve multiple rows.
TIMING_ID
CHAR
36
true
If statistics timings were not being captured, this column
will have a NULL value. If statistics timings were being captured, this
column will contain the id value which can be used as a foreign key to join
with the SYSXPLAIN_RESULTSET_TIMINGS row which has the timing information
for this resultset.