Using the SVL_QUERY_SUMMARY View
To analyze query summary information by stream, do the following:
Run the following query to determine your query ID:Copy
select query, elapsed, substring from svl_qlog order by query desc limit 5;
Examine the truncated query text in the
substringfield to determine which
queryvalue represents your query. If you have run the query more than once, use the
queryvalue from the row with the lower
elapsedvalue. That is the row for the compiled version. If you have been running many queries, you can raise the value used by the LIMIT clause used to make sure your query is included.
Select rows from SVL_QUERY_SUMMARY for your query. Order the results by stream, segment, and step:Copy
select * from svl_query_summary where query = MyQueryID order by stm, seg, step;
Map the steps to the operations in the query plan using the information in Mapping the Query Plan to the Query Summary. They should have approximately the same values for rows and bytes (rows * width from the query plan). If they don’t, see Table Statistics Missing or Out of Date for recommended solutions.
See if the
is_diskbasedfield has a value of
t(true) for any step. Hashes, aggregates, and sorts are the operators that are likely to write data to disk if the system doesn't have enough memory allocated for query processing.
is_diskbasedis true, see Insufficient Memory Allocated to the Query for recommended solutions.
labelfield values and see if there is an AGG-DIST-AGG sequence anywhere in the steps. Its presence indicates two-step aggregation, which is expensive. To fix this, change the GROUP BY clause to use the distribution key (the first key, if there are multiple ones).
maxtimevalue for each segment (it is the same across all steps in the segment). Identify the segment with the highest
maxtimevalue and review the steps in this segment for the following operators.
maxtimevalue doesn't necessarily indicate a problem with the segment. Despite a high value, the segment might not have taken a long time to process. All segments in a stream start getting timed in unison. However, some downstream segments might not be able to run until they get data from upstream ones. This effect might make them seem to have taken a long time because their
maxtimevalue will include both their waiting time and their processing time.
BCAST or DIST: In these cases, the high
maxtimevalue might be the result of redistributing a large number of rows. For recommended solutions, see Suboptimal Data Distribution.
HJOIN (hash join): If the step in question has a very high value in the
rowsfield compared to the
rowsvalue in the final RETURN step in the query, see Hash Join for recommended solutions.
SCAN/SORT: Look for a SCAN, SORT, SCAN, MERGE sequence of steps just prior to a join step. This pattern indicates that unsorted data is being scanned, sorted, and then merged with the sorted area of the table.
See if the rows value for the SCAN step has a very high value compared to the rows value in the final RETURN step in the query. This pattern indicates that the execution engine is scanning rows that are later discarded, which is inefficient. For recommended solutions, see Insufficiently Restrictive Predicate.
maxtimevalue for the SCAN step is high, see Suboptimal WHERE Clause for recommended solutions.
rowsvalue for the SORT step is not zero, see Unsorted or Missorted Rows for recommended solutions.
bytesvalues for the 5–10 steps that precede the final RETURN step to get an idea of the amount of data that is being returned to the client. This process can be a bit of an art.
For example, in the following query summary, you can see that the third PROJECT step provides a
rowsvalue but not a
bytesvalue. By looking through the preceding steps for one with the same
rowsvalue, you find the SCAN step that provides both rows and bytes information:
If you are returning an unusually large volume of data, see Very Large Result Set for recommended solutions.
See if the
bytesvalue is high relative to the
rowsvalue for any step, in comparison to other steps. This pattern can indicate that you are selecting a lot of columns. For recommended solutions, see Large SELECT List.