- Schema:
QUERY_INSIGHTS view¶
This Account Usage view displays a row for each insight produced for a query.
Columns¶
Column name |
Type |
Description |
---|---|---|
|
TIMESTAMP_LTZ |
Start time of the query. |
|
TIMESTAMP_LTZ |
End time of the query. |
|
NUMBER |
Total elapsed time of the query (in milliseconds). |
|
VARCHAR |
Internal/system-generated identifier for the SQL statement. |
|
VARCHAR |
The hash value computed based on the canonicalized SQL text. |
|
VARCHAR |
The hash value computed based on the parameterized query. |
|
VARCHAR |
Internal/system-generated identifier for the warehouse that was used. |
|
VARCHAR |
Warehouse that the query executed on, if any. |
|
NUMBER |
Internal/system-generated identifier for the insight. |
|
VARCHAR |
Identifier of the insight type. |
|
VARIANT |
Structured information and details about the insight. |
|
ARRAY |
Array of strings, each containing a recommended action for the insight. |
|
BOOLEAN |
If
|
|
VARCHAR |
Label that identifies the type of performance impact detected by this insight. For the list of labels, see Insight topics. |
Insight topics¶
For the insight_topic
column, the label can be one of the following:
TABLE_SCAN
: Insights about the efficiency of accessing tables. This label applies to the following types of insights:JOIN
: Insights about the efficiency of JOIN operations in the query. This label applies to the following types of insights:AGGREGATE
: Insights about the efficiency of aggregate operations in the query. This label applies to the following types of insights:UNION
: Insights about the efficiency of UNION operations in the query. This label applies to the following types of insights:WAREHOUSE
: Insights about the warehouse used for the query. This label applies to the following types of insights:
Usage notes¶
Latency for the view may be up to 90 minutes.
Examples¶
The following example returns the query insights for the query with the ID
01bd3a9d-0910-8327-0000-09717704c032
:
SELECT query_id, insight_type_id, message, suggestions
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS
WHERE query_id = '01bd3a9d-0910-8327-0000-09717704c032';
The following example returns the query insights for queries that have the same hash of parameterized query text. These are queries that use the same SELECT statement except for the literals specified in the statement.
SELECT query_id, insight_type_id, message, suggestions
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS
WHERE query_parameterized_hash = '4bb66effc1a3c8b4e94a728f7caaa736';
The following example returns the query insights for queries that ran during the past week:
SELECT query_id, insight_type_id, message, suggestions
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS
WHERE start_time > TO_DATE(DATEADD(DAY, -7, CURRENT_DATE()));
The following example returns the query insights for queries that ran during the past week and took more than an hour to complete:
SELECT query_id, insight_type_id, message, suggestions
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS
WHERE start_time > TO_DATE(DATEADD(DAY, -7, CURRENT_DATE()))
AND total_duration > 3600000;
The following example returns the query insights for queries that ran during the past week, took more than an hour to complete,
and used the warehouse with the ID 84412315
:
SELECT query_id, insight_type_id, message, suggestions
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS
WHERE start_time > TO_DATE(DATEADD(DAY, -7, CURRENT_DATE()))
AND total_duration > 3600000
AND warehouse_id = 84412315;