page101
83
INSERT INTO long_execution_time
SELECT query_id,
warehouse_name,
warehouse_size,
total_elapsed_time / 1000 AS query_execution_time_ms,
partitions_scanned,
partitions_total
FROM snowflake.account_usage.query_history
WHERE cluster_number IS NOT NULL //Exclude cached results
AND execution_status = 'SUCCESS' //Include completed queries
AND bytes_scanned > 0 //Must have scanned data
AND total_elapsed_time > 1000 //Execution time must be over 1s,
queries which used compute
AND TO_DATE ( start_time ) > DATEADD ( day, -7, TO_DATE ( current_
timestamp()));
You can find further information on
QUERY_HISTORY
at
https://docs.snowflake.
com/en/sql-reference/account-usage/query_history
.
SELECT query_id,
warehouse_name,
warehouse_size,
query_execution_time_ms,
partitions_scanned,
partitions_total
FROM long_execution_time;
From the previous result set, use
GET_QUERY_OPERATOR_STATS
to examine statistics
for individual operators within a single query.
SELECT *
FROM TABLE ( get_query_operator_stats('<your query_id here>'));
I leave it to you to refine the previous queries.
ChapTer 3
The Query profiler