Description

This page is the complete listing of all steps, on all slices, for a query, which I have termed a “step plan”.

A step plans tells you exactly what work a query did, and should be used rather than using EXPLAIN, as the query plans from EXPLAIN have a number of serious problems.

Related Documentation

1. Critique of query plans
2. Step plans

Columns

Column Descriptions

qid

The query ID. This is really the WLM query ID; leader node queries have no query ID, at least, not that you see in the system tables. They do really have query IDs, of course, but they’re on the leader node only and they never show up in the system tables. All we see from the leader node are transaction IDs and process IDs.

stream

The stream ordinal. A query is composed of streams, which execute serially. Stream numbers begin at 0, execute in numeric order, and reset to 0 for each query (so are unique on a per-query basis).

segment

The segment ordinal. A stream is composed of segments, which execute concurrently. Segment numbers begin at 0 but do not reset to 0 per stream, and so are unique, on a per-query basis.

step

The step ordinal. A segment is composed of steps, which execute concurrently. Step numbers begin at 0, but they reset to 0 per segment.

node

The node ID. Nodes count from 0 and are contiguous.

slice

The slice ID. Slices count from 0 and are contiguous and unique across all nodes.

There is a single special case “slice”, which used to be number 6411 but now seems to be a value which varies by query and which is slightly above 12800, and this is in fact the leader node.

step type

The human readable step type description.

rows

Number of rows processed by the step.

Now, this used to be actual, genuine, correct number of rows processed by a slice (unlike stl_query_metrics, which is always inaccurate, for a range of reason).

However, these days, it looks to me like slices can access any block on their node, and so you can see quite uneven numbers between slices; but, also, I see at times numbers which multiples of 10 - and it looks odd, to me.

I’m still thinking this number probably is the actual, genuine correct number of rows processed by a slice, but it may well be now odd things are happening with the numbers of rows processed by each slice.

bytes

Number of bytes processed by the step.

Same issues as described in rows.

segment:start

The start time is the time a segment (process) started in its slice. All steps, on a given slice, in the same segment, have the same start time. This is because the segment is composed of all its steps - they are not independent entities; really what we’re looking at here is the starting of the segment, and that means all the steps it is composed of have also started.

The start times for the same segment but across slices varies a little, as although all of the slices will be commanded at the same time to start those segments, naturally, there will be very small variations between slices.

segment:end

The end time is the time a segment (process) finished running on its slice. All steps, on a given slice, in the same segment, have the same end time.

segment:duration

Duration of the step. This is computed by subtracting the step end time from the segment start time.

table

Sometime a step operates on table. When this occurs, this column contains the name of the table. Remember this name is valid only in the database the query operated upon.

(Note on 2023-01-07, I can in fact in stv_tbl_perm look up the database the table is in, and show its name here, which I intend to do.)

notes

For step types aggregate, hash join, scan and unique, this column provides step-specification additional information.