See "Working with RunTimeStatistics" in
for information
on how to configure this.
Each row in this table describes a single table/index scan for
a particular result set used by a particular statement. Rows
in this table are typically joined with rows in
SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis:
select st.stmt_text, sp.no_visited_rows
from my_stats.sysxplain_scan_props sp,
my_stats.sysxplain_resultsets rs,
my_stats.sysxplain_statements st
where st.stmt_id = rs.stmt_id and
rs.scan_rs_id = sp.scan_rs_id and
rs.op_identifier = 'TABLESCAN' and
sp.scan_object_name = 'COUNTRIES'
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_SCAN_PROPS system
table.
SYSXPLAIN_SCAN_PROPS system table
For each column in the SYSXPLAIN_SCAN_PROPS 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
SCAN_RS_ID
CHAR
36
false
A unique identifer for this particular row. Referenced
by the foreign key SCAN_RS_ID in SYSXPLAIN_RESULTSETS.
SCAN_OBJECT_NAME
VARCHAR
128
true
The name of the object being scanned. If this is a
scan of a table or index, the table name or index name appears here.
If this is a scan of the internal index created for a constraint, the
constraint name appears here.
For complex join queries, the object being scanned may be an intermediate
result, in which case a description such as 'Temporary HashTable'
appears.
SCAN_OBJECT_TYPE
CHAR
1
false
A code indicating the type of object being scanned.
Codes include 'T' for Table, 'I' for Index, and 'C' for
Constraint.
SCAN_TYPE
CHAR
8
false
The type of scan being performed. Scan types include
'HEAP', 'BTREE', and 'SORT'.
ISOLATION_LEVEL
CHAR
3
true
The isolation level being used for this scan.
Isolation levels are identified by a code: 'RU' for Read Uncommitted,
'RC' for Read Committed, 'RR' for Repeatable Read,
and 'SE' for Serializable.
NO_VISITED_PAGES
INTEGER
10
true
Number of database pages that this scan touched.
For btree scans this number only includes the leaf pages visited.
NO_VISITED_ROWS
INTEGER
10
true
Number of database rows that were examined by this scan.
This number includes all rows, including those rows marked deleted,
those rows that don't meet qualification, and those rows which were
returned by the scan.
NO_QUALIFIED_ROWS
INTEGER
10
true
Number of rows that satisfied the qualifiers for this scan.
NO_VISITED_DELETED_ROWS
INTEGER
10
true
Number of the database rows that were examined by this
scan which were found to be rows that were marked deleted.
NO_FETCHED_COLUMNS
INTEGER
10
true
Number of columns that were fetched from each qualifying
row.
BITSET_OF_FETCHED_COLUMNS
VARCHAR
32,672
true
Description of the columns which were fetched from each
qualifying row.
BTREE_HEIGHT
INTEGER
10
true
For a scan of type BTREE, this column holds the height
of the BTREE index. The typical height of a BTREE is 2-4; BTREE heights
larger than this should only be seen with very large indexes. A tree
with one page has a height of 1. Total number of pages visited in a
scan of a BTREE should be (BTREE_HEIGHT - 1 + NO_VISITED_PAGES). For an
extremely small BTREE, the btree height may be negative (-1).
For other types of scans, this column is NULL.
FETCH_SIZE
INTEGER
10
true
The number of pages fetched at a time
when the scan is retrieving pages from disk.
START_POSITION
VARCHAR
32,672
true
For index and constraint scans, a
textual representation of the operator, if any, which was used to position
the beginning of the index/constraint scan.
STOP_POSITION
VARCHAR
32,672
true
For index and constraint scans, a
textual representation of the operator, if any, which was used to position
the end of the index/constraint scan.
SCAN_QUALIFIERS
VARCHAR
32,672
true
If the query specified values which are to be used to
limit the rows that are scanned, information about those values is
captured in this column.
NEXT_QUALIFIERS
VARCHAR
32,672
true
If the query specified values which are to be used to
limit the rows that are scanned, information about those values is
captured in this column.
HASH_KEY_COLUMN_NUMBERS
VARCHAR
32,672
true
For hash joins, this column contains information about which
column is being used to hash the rows that are joined.
HASH_TABLE_SIZE
INTEGER
10
true
For hash joins, this column contains information about
the size of the hash table that will be used to hold the rows being
joined. This hash table is an intermediate result, and will be
discarded at the end of the query. If the hash table cannot fit in
memory, it will automatically spill over to disk. Since the
spillover to disk can have significant performance implications,
this value can provide a clue that the hash table was
unexpectedly too large to fit in memory.