See "Working with RunTimeStatistics" in
for information
on how to configure this.
Rows in this table are typically joined with rows in
SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis.
select s.stmt_text, rs.op_identifier,
srt.no_input_rows, srt.no_output_rows
from my_stats.sysxplain_sort_props srt,
my_stats.sysxplain_resultsets rs,
my_stats.sysxplain_statements s
where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_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_SORT_PROPS system
table.
SYSXPLAIN_SORT_PROPS system table
For each column in the SYSXPLAIN_SORT_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
SORT_RS_ID
CHAR
36
false
A unique identifier for this row. Matches the corresponding
value of SORT_RS_ID in the my_stats.SYSXPLAIN_RESULTSETS row for the result set
which required this sort to be performed.
SORT_TYPE
CHAR
2
true
A code indicating the type of sort that was performed.
The code values include 'IN' for an internal sort, and 'EX'
for an external sort. An internal sort is one which was entirely
performed in-memory and did not overflow to any temporary files, while an
external sort used one or more external files.
NO_INPUT_ROWS
INTEGER
10
true
Number of rows which were provided to the sorter.
NO_OUTPUT_ROWS
INTEGER
10
true
Number of rows which were returned by the sorter.
Note that this may be fewer rows than were input, for example when
the sorter is performing GROUP BY processing or is eliminating
duplicates.
NO_MERGE_RUNS
INTEGER
10
true
Number of merge runs which were provided. This value
will be NULL for an internal sort, but for an external sort it
indicates how many times the intermediate sort files were merged together.
External sorts are far more expensive than internal sorts, and each
additional merge run that an external sort must perform adds
considerably more to the overhead of the sort.
MERGE_RUN_DETAILS
VARCHAR
32,672
true
Additional information about the size of the merge runs.
This value will be NULL for an internal sort.
ELIMINATE_DUPLICATES
CHAR
1
true
A code indicating whether or not this sort
eliminated duplicates from the input. Valid values are 'Y' and
'N'.
This column only applies for a sort which was NOT performing GROUP BY
aggregation; for GROUP BY sorts this column is always NULL. See the
DISTINCT_AGGREGATE column for the corresponding information for
aggregating sorts.
IN_SORT_ORDER
CHAR
1
true
A code indicating whether or not the rows which were
input to the sorter were already in sort order, which can happen if
the rows were retrieved by using an index, or if an earlier phase of
processing had already sorted the data. The code is 'Y' if the
rows are already in sorted order, and 'N' otherwise.
DISTINCT_AGGREGATE
CHAR
1
true
A code indicating whether the aggregation process
was computing distinct aggregates or not. Valid values are 'Y' and
'N'.