page104
86
partition_scan_ratio NUMBER,
partitions_scanned NUMBER,
partitions_total NUMBER
);
The latency for
QUERY_HISTORY
may be up to 45 minutes.
You can now insert candidate records from
QUERY_HISTORY
. In this example you
can identify those records with a micro-partition scanned to a total ratio greater than 50
percent; you may want to change the predicates to suit your requirements.
INSERT INTO long_table_scan
SELECT query_id,
warehouse_name,
warehouse_size,
partitions_scanned / partitions_total AS partition_scan_ratio,
partitions_scanned,
partitions_total
FROM snowflake.account_usage.query_history
WHERE warehouse_name 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 ( partitions_scanned / partitions_total ) > 0.5;
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,
partition_scan_ratio,
partitions_scanned,
partitions_total
FROM long_table_scan;
ChapTer 3
The Query profiler