Categories:

System functions (Query Information) , Table functions

GET_QUERY_OPERATOR_STATS

Returns statistics about individual query operators within a query that has completed. You can run this function for any completed query that was executed in the past 14 days.

You can use this information to understand the structure of a query and identify query operators (e.g. the join operator) that cause performance problems.

For example, you can use this information to determine which operators are consuming the most resources. As another example, you can use this function to identify joins that have more output rows than input rows, which can be a sign of an “exploding” join (e.g. an unintended Cartesian product).

These statistics are also available in the query profile tab in Snowsight. The GET_QUERY_OPERATOR_STATS() function makes the same information available via a programmatic interface.

For more information about finding problematic query operators, see Common query problems identified by Query Profile.

Syntax

GET_QUERY_OPERATOR_STATS( <query_id> )
Copy

Arguments

query_id

The ID of a query. You can use:

  • A string literal (a string surrounded by single quotes).

  • A session variable containing a query ID.

  • The return value from a call to the LAST_QUERY_ID function.

Returns

The GET_QUERY_OPERATOR_STATS function is a table function. It returns rows with statistics about each query operator in the query. For more information, see the Usage notes and Output sections below.

Usage notes

  • This function only returns statistics on queries that have completed.

  • You must have OPERATE or MONITOR privileges on the warehouse where you ran the query.

  • This function provides detailed statistics about each query operator used in the specified query. The possible query operators include:

    • Aggregate: Groups inputs and computes aggregate functions.

    • CartesianJoin: A specialized type of join.

    • Delete: Removes a record from a table.

    • ExternalFunction: Represents processing by an external function.

    • ExternalScan: Represents access to data stored in stage objects.

    • Filter: Represents an operation that filters the rows.

    • Flatten: Processes VARIANT records, possibly flattening them on a specified path.

    • Generator: Generates records using the TABLE(GENERATOR(…)) construct.

    • GroupingSets: Represents constructs such as GROUPING SETS, ROLLUP, and CUBE.

    • Insert: Adds a record to a table either through an INSERT or COPY operation.

    • InternalObject: Represents access to an internal data object (e.g in an Information Schema or the result of a previous query).

    • Join: Combines two inputs on a given condition.

    • JoinFilter: Special filtering operation that removes tuples that can be identified as not possibly matching the condition of a Join further in the query plan.

    • Merge: Performs a MERGE operation on a table.

    • Pivot: Transforms unique values from a column into multiple columns and does any necessary aggregation.

    • Result: Returns the query result.

    • Sort: Orders input on a given expression.

    • SortWithLimit: Produces a part of the input sequence after sorting, typically a result of an ORDER BY ... LIMIT ... OFFSET ... construct.

    • TableScan: Represents access to a single table.

    • UnionAll: Concatenates two inputs.

    • Unload: Represents a COPY operation that exports data from a table to a file in a stage.

    • Unpivot: Rotates a table by transforming columns into rows.

    • Update: Updates a record in a table.

    • ValuesClause: List of values provided with the VALUES clause.

    • WindowFunction: Computes window functions.

    • WithClause: Precedes the body of the SELECT statement, and defines one or more CTEs.

    • WithReference: Instance of a WITH clause.

  • The information is returned as a table. Each row in the table corresponds to one operator. The row contains the execution breakdown and the query statistics for that operator.

    The row may also list operator attributes (these depend on the type of operator).

    Statistics that break down query execution time are expressed as a percentage of the time consumed by the total query.

    For more information about specific statistics, see Output (in this topic).

  • Because this function is a table function, you must use it in a FROM clause and you must wrap it in TABLE(). For example:

    select *
        from table(get_query_operator_stats(last_query_id()));
    
    Copy
  • For each individual execution of a specific query (i.e. a specific UUID), this function is deterministic; it returns the same values each time.

    However, for different executions of the same query text, this function can return different runtime statistics. The statistics depend on many factors. The following factors can have a major impact on the execution and therefore on the statistics returned by this function:

    • The volume of data.

    • The availability of materialized views, and the changes (if any) to the data since those materialized views were last refreshed.

    • The presence or absence of clustering.

    • The presence or absence of previously-cached data.

    • The size of the virtual warehouse.

    The values can also be affected by factors outside the user’s query and data. These factors are usually small. The factors include:

    • Virtual warehouse initialization time.

    • Latency with external functions.

Output

The function returns the following columns:

Column Name

Data Type

Description

QUERY_ID

VARCHAR

The query ID, which is an internal, system-generated identifier for the SQL statement.

STEP_ID

NUMBER(38, 0)

Identifier of the step in the query plan.

OPERATOR_ID

NUMBER(38, 0)

The operator’s identifier. This is unique within the query. Values start at 0.

PARENT_OPERATORS

ARRAY containing one or more NUMBER(38, 0)

Identifiers of the parent operators for this operator, or NULL if this is the final operator in the query plan (which is usually the Result operator).

OPERATOR_TYPE

VARCHAR

The type of query operator (e.g. TableScan or Filter).

OPERATOR_STATISTICS

VARIANT containing an OBJECT

Statistics about the operator (e.g. the number of output rows from the operator).

EXECUTION_TIME_BREAKDOWN

VARIANT containing an OBJECT

Information about the execution time of the operator.

OPERATOR_ATTRIBUTES

VARIANT containing an OBJECT

Information about the operator. This information depends upon the operator type.

If there is no information for the specific column for the operator, the value is NULL.

Three of these columns contain OBJECTs. Each object contains key/value pairs. The tables below show information about the keys in these tables.

OPERATOR_STATISTICS

The fields in the OBJECTs for the OPERATOR_STATISTICS column provide additional information about the operator. The information can include:

Key

Nested Key (if applicable)

Data Type

Description

dml

Statistics for Data Manipulation Language (DML) queries:

number_of_rows_inserted

DOUBLE

The number of rows inserted into a table (or tables).

number_of_rows_updated

DOUBLE

The number of rows updated in a table.

number_of_rows_deleted

DOUBLE

The number of rows deleted from a table.

number_of_rows_unloaded

DOUBLE

The number of rows unloaded during data export.

external_functions

Information about calls to external functions. If the value of a field, for example retries_due_to_transient_errors, is zero, then the field is not displayed.

total_invocations

DOUBLE

The number of times that an external function was called. (This can be different from the number of external function calls in the text of the SQL statement due to the number of batches that rows are divided into, the number of retries (if there are transient network problems), etc.)

rows_sent

DOUBLE

The number of rows sent to external functions.

rows_received

DOUBLE

The number of rows received back from external functions.

bytes_sent (x-region)

DOUBLE

The number of bytes sent to external functions. If the key includes (x-region), the data was sent across regions (which can impact billing).

bytes_received (x-region)

DOUBLE

The number of bytes received from external functions. If the key includes (x-region), the data was sent across regions (which can impact billing).

retries_due_to_transient_errors

DOUBLE

The number of retries due to transient errors.

average_latency_per_call

DOUBLE

The average amount of time per invocation (call) in milliseconds between the time Snowflake sent the data and received the returned data.

http_4xx_errors

INTEGER

Total number of HTTP requests that returned a 4xx status code.

http_5xx_errors

INTEGER

Total number of HTTP requests that returned a 5xx status code.

average_latency

DOUBLE

Average latency for successful HTTP requests.

avg_throttle_latency_overhead

DOUBLE

Average overhead per successful request due to a slowdown caused by throttling (HTTP 429).

batches_retried_due_to_throttling

DOUBLE

Number of batches that were retried due to HTTP 429 errors.

latency_per_successful_call_(p50)

DOUBLE

50th percentile latency for successful HTTP requests. 50 percent of all successful requests took less than this time to complete.

latency_per_successful_call_(p90)

DOUBLE

90th percentile latency for successful HTTP requests. 90 percent of all successful requests took less than this time to complete.

latency_per_successful_call_(p95)

DOUBLE

95th percentile latency for successful HTTP requests. 95 percent of all successful requests took less than this time to complete.

latency_per_successful_call_(p99)

DOUBLE

99th percentile latency for successful HTTP requests. 99 percent of all successful requests took less than this time to complete.

input_rows

INTEGER

The number of input rows. This can be missing for an operator with no input edges from other operators.

io

Information about the I/O (input/output) operations performed during the query.

scan_progress

DOUBLE

The percentage of data scanned for a given table so far.

bytes_scanned

DOUBLE

The number of bytes scanned so far.

percentage_scanned_from_cache

DOUBLE

The percentage of data scanned from the local disk cache.

bytes_written

DOUBLE

Bytes written (e.g. when loading into a table).

bytes_written_to_result

DOUBLE

Bytes written to a result object.

For example, select * from . . . would produce a set of results in tabular format representing each field in the selection.

In general, the results object represents whatever is produced as a result of the query, and bytes_written_to_result represents the size of the returned result.

bytes_read_from_result

DOUBLE

Bytes read from a result object.

external_bytes_scanned

DOUBLE

Bytes read from an external object (e.g. a stage).

network

network_bytes

DOUBLE

The amount of data sent over the network.

output_rows

INTEGER

The number of output rows. This can be missing for the operator that returns the results to the user (which is usually the RESULT operator).

pruning

Information on table pruning.

partitions_scanned

DOUBLE

The number of partitions scanned so far.

partitions_total:

DOUBLE

The total number of partitions in a given table.

spilling

Information about disk usage for operations in which intermediate results do not fit in memory.

bytes_spilled_remote_storage

DOUBLE

The volume of data spilled to remote disk.

bytes_spilled_local_storage

DOUBLE

The volume of data spilled to local disk.

extension_functions

Information about calls to extension functions. If the value of a field is zero then the field is not displayed.

Java UDF handler load time

DOUBLE

amount of time for the Java UDF handler to load.

Total Java UDF handler invocations

DOUBLE

number of times the Java UDF handler is invoked.

Max Java UDF handler execution time

DOUBLE

maximum amount of time for the Java UDF handler to execute.

Avg Java UDF handler execution time

DOUBLE

average amount of time to execute the Java UDF handler.

Java UDTF process() invocations

DOUBLE

number of times the Java UDTF process method was invoked.

Java UDTF process() execution time

DOUBLE

amount of time to execute the Java UDTF process.

Avg Java UDTF process() execution time

DOUBLE

average amount of time to execute the Java UDTF process.

Java UDTF's constructor invocations

DOUBLE

number of times the Java UDTF constructor was invoked.

Java UDTF's constructor execution time

DOUBLE

amount of time to execute the Java UDTF constructor.

Avg Java UDTF's constructor execution time

DOUBLE

average amount of time to execute the Java UDTF constructor.

Java UDTF endPartition() invocations

DOUBLE

number of times the Java UDTF endPartition method was invoked.

Java UDTF endPartition() execution time

DOUBLE

amount of time to execute the Java UDTF endPartition method.

Avg Java UDTF endPartition() execution time

DOUBLE

average amount of time to execute the Java UDTF endPartition method.

Max Java UDF dependency download time

DOUBLE

maximum amount of time to download the Java UDF dependencies.

Max JVM memory usage

DOUBLE

peak memory usage as reported by the JVM.

Java UDF inline code compile time in ms

DOUBLE

compile time for the Java UDF inline code.

Total Python UDF handler invocations

DOUBLE

number of times the Python UDF handler was invoked.

Total Python UDF handler execution time

DOUBLE

total execution time for Python UDF handler.

Avg Python UDF handler execution time

DOUBLE

average amount of time to execute the Python UDF handler.

Python sandbox max memory usage

DOUBLE

peak memory usage by the Python sandbox environment.

Avg Python env creation time: Download and install packages

DOUBLE

average amount of time to create the Python environment, including downloading and installing packages.

Conda solver time

DOUBLE

amount of time to run the Conda solver to solve Python packages.

Conda env creation time

DOUBLE

amount of time to create the Python environment.

Python UDF initialization time

DOUBLE

amount of time to initialize the Python UDF.

Number of external file bytes read for UDFs

DOUBLE

number of external file bytes read for UDFs.

Number of external files accessed for UDFs

DOUBLE

number of external files accessed for UDFs.

EXECUTION_TIME_BREAKDOWN

The fields in the OBJECTs for the EXECUTION_TIME_BREAKDOWN column are shown below.

Key

Data Type

Description

overall_percentage

DOUBLE

The percentage of the total query time spent by this operator.

initialization

DOUBLE

Time spent setting up query processing.

processing

DOUBLE

Time spent processing the data by the CPU.

synchronization

DOUBLE

Time spent synchronizing activities between participating processes.

local_disk_io

DOUBLE

Time during which processing was blocked while waiting for local disk access.

remote_disk_io

DOUBLE

Time during which processing was blocked while waiting for remote disk access.

network_communication

DOUBLE

Time during which processing was waiting for network data transfer.

OPERATOR_ATTRIBUTES

Each output row describes one operator in the query. The table below shows the possible types of operators (e.g. the Filter operator). For each type of operator, the table shows the possible attributes (e.g. the expression used to filter the rows).

The operator attributes are stored in the OPERATOR_ATTRIBUTES column, which is of type VARIANT and contains an OBJECT. The OBJECT contains key/value pairs. Each key corresponds to one attribute of the operator.

Operator Name

Key

Data Type

Description

Aggregate

functions

ARRAY of VARCHAR

List of functions computed.

grouping_keys

ARRAY of VARCHAR

The group-by expression.

CartesianJoin

additional_join_condition

VARCHAR

Non-equality join expression.

equality_join_condition

VARCHAR

Equality join expression.

join_type

VARCHAR

Type of join (INNER).

Delete

table_name

VARCHAR

Name of updated table.

ExternalScan

stage_name

VARCHAR

The name of the stage from which the data is read.

stage_type

VARCHAR

The type of the stage.

Filter

filter_condition

VARCHAR

The expression used to filter data.

Flatten

input

VARCHAR

The input expression used to flatten data.

Generator

row_count

NUMBER

Value of the input parameter ROWCOUNT.

time_limit

NUMBER

Value of the input parameter TIMELIMIT.

GroupingSets

functions

ARRAY of VARCHAR

List of functions computed.

key_sets

ARRAY of VARCHAR

List of grouping sets.

Insert

input_expression

VARCHAR

Which expressions are inserted.

table_names

ARRAY of VARCHAR

List of table names to which records are added.

InternalObject

object_name

VARCHAR

The name of the accessed object.

Join

additional_join_condition

VARCHAR

Non-equality join expression.

equality_join_condition

VARCHAR

Equality join expression.

join_type

VARCHAR

Type of join (INNER, OUTER, LEFT JOIN, etc.).

JoinFilter

join_id

NUMBER

The operator id of the join used to identify tuples that can be filtered out.

Merge

table_name

VARCHAR

Name of updated table.

Pivot

grouping_keys

ARRAY of VARCHAR

Remaining columns on which the results are aggregated.

pivot_column

ARRAY of VARCHAR

Resulting columns of pivot values.

Result

expressions

ARRAY of VARCHAR

List of expressions produced.

Sort

sort_keys

ARRAY of VARCHAR

Expression defining the sorting order.

SortWithLimit

offset

NUMBER

Position in the ordered sequence from which produced tuples are emitted.

rows

NUMBER

Number of rows produced.

sort_keys

ARRAY of VARCHAR

Expression defining the sorting order.

TableScan

columns

ARRAY of VARCHAR

List of scanned columns.

extracted_variant_paths

ARRAY of VARCHAR

List of paths extracted from variant columns.

table_alias

VARCHAR

Alias of table being accessed.

table_name

VARCHAR

Name of table being accessed.

Unload

location

VARCHAR

Stage where data is saved.

Unpivot

expressions

ARRAY of VARCHAR

Output columns of the unpivot query.

Update

table_name

VARCHAR

Name of updated table.

ValuesClause

value_count

NUMBER

Number of produced values.

values

VARCHAR

List of values.

WindowFunction

functions

ARRAY of VARCHAR

List of functions computed.

WithClause

name

VARCHAR

Alias of WITH clause.

If an operator is not listed, no attributes are produced, and the value is reported as {}.

Note

  • The following operators do not have any operator attributes and therefore are not included in the table of OPERATOR_ATTRIBUTES:

    • UnionAll

    • ExternalFunction

Examples

Retrieving data about a single query

This example shows the statistics for a SELECT that joins two small tables.

Run the SELECT statement:

select x1.i, x2.i
    from x1 inner join x2 on x2.i = x1.i
    order by x1.i, x2.i;
Copy

Get the query ID:

set lqid = (select last_query_id());
Copy

Call GET_QUERY_OPERATOR_STATS() to get statistics about the individual query operators in the query:

select * from table(get_query_operator_stats($lqid));
+--------------------------------------+---------+-------------+--------------------+---------------+-----------------------------------------+-----------------------------------------------+----------------------------------------------------------------------+
| QUERY_ID                             | STEP_ID | OPERATOR_ID | PARENT_OPERATORS   | OPERATOR_TYPE | OPERATOR_STATISTICS                     | EXECUTION_TIME_BREAKDOWN                      | OPERATOR_ATTRIBUTES                                                  |
|--------------------------------------+---------+-------------+--------------------+---------------+-----------------------------------------+-----------------------------------------------+----------------------------------------------------------------------|
| 01a8f330-0507-3f5b-0000-43830248e09a |       1 |           0 |               NULL | Result        | {                                       | {                                             | {                                                                    |
|                                      |         |             |                    |               |   "input_rows": 64                      |   "overall_percentage": 0.000000000000000e+00 |   "expressions": [                                                   |
|                                      |         |             |                    |               | }                                       | }                                             |     "X1.I",                                                          |
|                                      |         |             |                    |               |                                         |                                               |     "X2.I"                                                           |
|                                      |         |             |                    |               |                                         |                                               |   ]                                                                  |
|                                      |         |             |                    |               |                                         |                                               | }                                                                    |
| 01a8f330-0507-3f5b-0000-43830248e09a |       1 |           1 |              [ 0 ] | Sort          | {                                       | {                                             | {                                                                    |
|                                      |         |             |                    |               |   "input_rows": 64,                     |   "overall_percentage": 0.000000000000000e+00 |   "sort_keys": [                                                     |
|                                      |         |             |                    |               |   "output_rows": 64                     | }                                             |     "X1.I ASC NULLS LAST",                                           |
|                                      |         |             |                    |               | }                                       |                                               |     "X2.I ASC NULLS LAST"                                            |
|                                      |         |             |                    |               |                                         |                                               |   ]                                                                  |
|                                      |         |             |                    |               |                                         |                                               | }                                                                    |
| 01a8f330-0507-3f5b-0000-43830248e09a |       1 |           2 |              [ 1 ] | Join          | {                                       | {                                             | {                                                                    |
|                                      |         |             |                    |               |   "input_rows": 128,                    |   "overall_percentage": 0.000000000000000e+00 |   "equality_join_condition": "(X2.I = X1.I)",                        |
|                                      |         |             |                    |               |   "output_rows": 64                     | }                                             |   "join_type": "INNER"                                               |
|                                      |         |             |                    |               | }                                       |                                               | }                                                                    |
| 01a8f330-0507-3f5b-0000-43830248e09a |       1 |           3 |              [ 2 ] | TableScan     | {                                       | {                                             | {                                                                    |
|                                      |         |             |                    |               |   "io": {                               |   "overall_percentage": 0.000000000000000e+00 |   "columns": [                                                       |
|                                      |         |             |                    |               |     "bytes_scanned": 1024,              | }                                             |     "I"                                                              |
|                                      |         |             |                    |               |     "percentage_scanned_from_cache": 1, |                                               |   ],                                                                 |
|                                      |         |             |                    |               |     "scan_progress": 1                  |                                               |   "table_name": "MY_DB.MY_SCHEMA.X2" |
|                                      |         |             |                    |               |   },                                    |                                               | }                                                                    |
|                                      |         |             |                    |               |   "output_rows": 64,                    |                                               |                                                                      |
|                                      |         |             |                    |               |   "pruning": {                          |                                               |                                                                      |
|                                      |         |             |                    |               |     "partitions_scanned": 1,            |                                               |                                                                      |
|                                      |         |             |                    |               |     "partitions_total": 1               |                                               |                                                                      |
|                                      |         |             |                    |               |   }                                     |                                               |                                                                      |
|                                      |         |             |                    |               | }                                       |                                               |                                                                      |
| 01a8f330-0507-3f5b-0000-43830248e09a |       1 |           4 |              [ 2 ] | JoinFilter    | {                                       | {                                             | {                                                                    |
|                                      |         |             |                    |               |   "input_rows": 64,                     |   "overall_percentage": 0.000000000000000e+00 |   "join_id": "2"                                                     |
|                                      |         |             |                    |               |   "output_rows": 64                     | }                                             | }                                                                    |
|                                      |         |             |                    |               | }                                       |                                               |                                                                      |
| 01a8f330-0507-3f5b-0000-43830248e09a |       1 |           5 |              [ 4 ] | TableScan     | {                                       | {                                             | {                                                                    |
|                                      |         |             |                    |               |   "io": {                               |   "overall_percentage": 0.000000000000000e+00 |   "columns": [                                                       |
|                                      |         |             |                    |               |     "bytes_scanned": 1024,              | }                                             |     "I"                                                              |
|                                      |         |             |                    |               |     "percentage_scanned_from_cache": 1, |                                               |   ],                                                                 |
|                                      |         |             |                    |               |     "scan_progress": 1                  |                                               |   "table_name": "MY_DB.MY_SCHEMA.X1" |
|                                      |         |             |                    |               |   },                                    |                                               | }                                                                    |
|                                      |         |             |                    |               |   "output_rows": 64,                    |                                               |                                                                      |
|                                      |         |             |                    |               |   "pruning": {                          |                                               |                                                                      |
|                                      |         |             |                    |               |     "partitions_scanned": 1,            |                                               |                                                                      |
|                                      |         |             |                    |               |     "partitions_total": 1               |                                               |                                                                      |
|                                      |         |             |                    |               |   }                                     |                                               |                                                                      |
|                                      |         |             |                    |               | }                                       |                                               |                                                                      |
+--------------------------------------+---------+-------------+--------------------+---------------+-----------------------------------------+-----------------------------------------------+----------------------------------------------------------------------+
Copy

Identifying “exploding” join operators

The following example shows how to use GET_QUERY_OPERATOR_STATS to examine a complicated query. This example looks for operators within a query that produce many more rows than were input to that operator.

This is the query to be analyzed:

select *
from t1
    join t2 on t1.a = t2.a
    join t3 on t1.b = t3.b
    join t4 on t1.c = t4.c
;
Copy

Get the query ID of the previous query:

set lid = last_query_id();
Copy

The following query shows the ratio of output rows to input rows for each of the join operators in the query.

select
        operator_id,
        operator_attributes,
        operator_statistics:output_rows / operator_statistics:input_rows as row_multiple
    from table(get_query_operator_stats($lid))
    where operator_type = 'Join'
    order by step_id, operator_id;

+---------+-------------+--------------------------------------------------------------------------+---------------+
| STEP_ID | OPERATOR_ID | OPERATOR_ATTRIBUTES                                                      | ROW_MULTIPLE  |
+---------+-------------+--------------------------------------------------------------------------+---------------+
|       1 |           1 | {  "equality_join_condition": "(T4.C = T1.C)",   "join_type": "INNER"  } |  49.969249692 |
|       1 |           3 | {  "equality_join_condition": "(T3.B = T1.B)",   "join_type": "INNER"  } | 116.071428571 |
|       1 |           5 | {  "equality_join_condition": "(T2.A = T1.A)",   "join_type": "INNER"  } |  12.20657277  |
+---------+-------------+--------------------------------------------------------------------------+---------------+
Copy

After you identify the exploding joins, you can review each join condition to verify that the condition is correct.