page312
400
First, identify candidate long-running queries, noting that not all long-running
queries will suffer from join explosion.
SELECT query_id
FROM TABLE ( information_schema.query_history())
WHERE query_type IN ( 'SELECT', 'CREATE_TABLE_AS_SELECT' )
AND warehouse_name IS NOT NULL
AND execution_status = 'SUCCESS'
AND bytes_scanned > 0
AND total_elapsed_time > 1000;
With the candidate
query_id
values identified, you can investigate each for
CartesianJoin
operations.
SELECT operator_type,
operator_id,
operator_attributes,
operator_statistics:output_rows / operator_statistics:input_rows AS
row_multiple
FROM TABLE ( get_query_operator_stats('<YOUR_QUERY_ID_HERE>'))
WHERE operator_type = 'CartesianJoin';
Join explosions are usually caused by a missing join condition in the query
predicates (
WHERE
clause).
Guidance on resolving join explosions is provided in Chapter 3. You can find more
information on join explosion at
https://community.snowflake.com/s/article/
Recognizing-Row-Explosion
and
https://docs.snowflake.com/en/sql-reference/
functions/get_query_operator_stats#identifying-exploding-join-operators.
Long Compilation Time
Long compilation time identifies records where the compilation time exceeds the
execution time. Please refer to Chapter 3 for a full explanation.
SELECT query_id,
warehouse_name,
warehouse_size,
compilation_time,
ChapTer 10
OpTimizing perfOrmanCe