-- =====================================================
-- TABLE 1: QUERY_HISTORY_SUMMARY
-- This table provides all query overview data for drilling down
-- =====================================================
CREATE OR REPLACE TABLE QUERY_HISTORY_SUMMARY AS
SELECT
-- Query Identification
q.QUERY_ID,
q.QUERY_HASH,
q.QUERY_PARAMETERIZED_HASH,
LEFT(q.QUERY_TEXT, 100) as QUERY_TEXT_PREVIEW,
q.QUERY_TYPE,
q.QUERY_TAG,
-- Timing Information
q.START_TIME,
q.END_TIME,
q.TOTAL_ELAPSED_TIME,
q.COMPILATION_TIME,
q.EXECUTION_TIME,
-- User and Session Info
q.USER_NAME,
q.USER_TYPE,
q.ROLE_NAME,
q.ROLE_TYPE,
q.SESSION_ID,
-- Warehouse Information
q.WAREHOUSE_ID,
q.WAREHOUSE_NAME,
q.WAREHOUSE_SIZE,
q.WAREHOUSE_TYPE,
q.CLUSTER_NUMBER,
-- Database Context
q.DATABASE_ID,
q.DATABASE_NAME,
q.SCHEMA_ID,
q.SCHEMA_NAME,
q.USER_DATABASE_NAME,
q.USER_SCHEMA_NAME,
-- Execution Status
q.EXECUTION_STATUS,
q.ERROR_CODE,
LEFT(q.ERROR_MESSAGE, 200) as ERROR_MESSAGE_PREVIEW,
-- Performance Metrics
q.BYTES_SCANNED,
q.PERCENTAGE_SCANNED_FROM_CACHE,
q.BYTES_WRITTEN,
q.ROWS_PRODUCED,
q.ROWS_INSERTED,
q.ROWS_UPDATED,
q.ROWS_DELETED,
-- Resource Usage
q.CREDITS_USED_CLOUD_SERVICES,
q.BYTES_SPILLED_TO_LOCAL_STORAGE,
q.BYTES_SPILLED_TO_REMOTE_STORAGE,
q.PARTITIONS_SCANNED,
q.PARTITIONS_TOTAL,
-- Queue Times
q.QUEUED_PROVISIONING_TIME,
q.QUEUED_REPAIR_TIME,
q.QUEUED_OVERLOAD_TIME,
q.TRANSACTION_BLOCKED_TIME,
-- Classification Buckets for easy filtering
CASE
WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
ELSE '5+ minutes'
END as DURATION_BUCKET,
CASE
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
ELSE '100+ cents'
END as CREDIT_BUCKET,
CASE
WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'SPILLED'
ELSE 'NO_SPILL'
END as SPILL_STATUS,
CASE
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) > 0 THEN 'QUEUED'
ELSE 'NOT_QUEUED'
END as QUEUE_STATUS,
-- Analysis metadata
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.QUERY_ID IS NOT NULL
ORDER BY q.START_TIME DESC;
-- =====================================================
-- TABLE 2: QUERY_DETAILS_COMPLETE
-- This table provides complete details for any specific query
-- =====================================================
CREATE OR REPLACE TABLE QUERY_DETAILS_COMPLETE AS
SELECT
-- Core Query Information
q.QUERY_ID,
q.QUERY_TEXT,
q.QUERY_HASH,
q.QUERY_HASH_VERSION,
q.QUERY_PARAMETERIZED_HASH,
q.QUERY_PARAMETERIZED_HASH_VERSION,
q.QUERY_TYPE,
q.QUERY_TAG,
-- Timing Details (all in milliseconds)
q.START_TIME,
q.END_TIME,
q.TOTAL_ELAPSED_TIME,
q.COMPILATION_TIME,
q.EXECUTION_TIME,
q.QUEUED_PROVISIONING_TIME,
q.QUEUED_REPAIR_TIME,
q.QUEUED_OVERLOAD_TIME,
q.TRANSACTION_BLOCKED_TIME,
q.CHILD_QUERIES_WAIT_TIME,
q.QUERY_RETRY_TIME,
q.QUERY_RETRY_CAUSE,
q.FAULT_HANDLING_TIME,
q.LIST_EXTERNAL_FILES_TIME,
-- User and Authentication
q.USER_NAME,
q.USER_TYPE,
q.ROLE_NAME,
q.ROLE_TYPE,
q.SECONDARY_ROLE_STATS,
q.SESSION_ID,
-- Warehouse and Compute
q.WAREHOUSE_ID,
q.WAREHOUSE_NAME,
q.WAREHOUSE_SIZE,
q.WAREHOUSE_TYPE,
q.CLUSTER_NUMBER,
q.QUERY_LOAD_PERCENT,
-- Database Context
q.DATABASE_ID,
q.DATABASE_NAME,
q.SCHEMA_ID,
q.SCHEMA_NAME,
q.USER_DATABASE_ID,
q.USER_DATABASE_NAME,
q.USER_SCHEMA_ID,
q.USER_SCHEMA_NAME,
-- Execution Results
q.EXECUTION_STATUS,
q.ERROR_CODE,
q.ERROR_MESSAGE,
q.IS_CLIENT_GENERATED_STATEMENT,
-- Data Processing Metrics
q.BYTES_SCANNED,
q.PERCENTAGE_SCANNED_FROM_CACHE,
q.BYTES_WRITTEN,
q.BYTES_WRITTEN_TO_RESULT,
q.BYTES_READ_FROM_RESULT,
q.ROWS_PRODUCED,
q.ROWS_WRITTEN_TO_RESULT,
q.ROWS_INSERTED,
q.ROWS_UPDATED,
q.ROWS_DELETED,
q.ROWS_UNLOADED,
q.BYTES_DELETED,
-- Partitioning
q.PARTITIONS_SCANNED,
q.PARTITIONS_TOTAL,
CASE
WHEN q.PARTITIONS_TOTAL > 0 THEN
ROUND((q.PARTITIONS_SCANNED::FLOAT / q.PARTITIONS_TOTAL::FLOAT) * 100, 2)
ELSE 0
END as PARTITION_SCAN_PERCENTAGE,
-- Memory and Spilling
q.BYTES_SPILLED_TO_LOCAL_STORAGE,
q.BYTES_SPILLED_TO_REMOTE_STORAGE,
q.BYTES_SENT_OVER_THE_NETWORK,
-- Credits and Cost
q.CREDITS_USED_CLOUD_SERVICES,
-- Data Transfer
q.OUTBOUND_DATA_TRANSFER_CLOUD,
q.OUTBOUND_DATA_TRANSFER_REGION,
q.OUTBOUND_DATA_TRANSFER_BYTES,
q.INBOUND_DATA_TRANSFER_CLOUD,
q.INBOUND_DATA_TRANSFER_REGION,
q.INBOUND_DATA_TRANSFER_BYTES,
-- External Functions
q.EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,
q.EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,
q.EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,
q.EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,
q.EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,
-- Query Acceleration
q.QUERY_ACCELERATION_BYTES_SCANNED,
q.QUERY_ACCELERATION_PARTITIONS_SCANNED,
q.QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR,
-- Transaction Information
q.TRANSACTION_ID,
q.PARENT_QUERY_ID,
q.ROOT_QUERY_ID,
-- System Information
q.RELEASE_VERSION,
-- Performance Ratios and Calculated Fields
CASE
WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
ROUND((q.COMPILATION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
ELSE 0
END as COMPILATION_TIME_PERCENTAGE,
CASE
WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
ROUND((q.EXECUTION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
ELSE 0
END as EXECUTION_TIME_PERCENTAGE,
CASE
WHEN q.BYTES_SCANNED > 0 THEN
ROUND(q.ROWS_PRODUCED::FLOAT / (q.BYTES_SCANNED::FLOAT / 1024 / 1024), 2)
ELSE 0
END as ROWS_PER_MB_SCANNED,
-- Performance Classifications
CASE
WHEN q.TOTAL_ELAPSED_TIME <= 1000 THEN 'VERY_FAST'
WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN 'FAST'
WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN 'MODERATE'
WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN 'SLOW'
ELSE 'VERY_SLOW'
END as PERFORMANCE_CATEGORY,
CASE
WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 90 THEN 'HIGH_CACHE_HIT'
WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 50 THEN 'MEDIUM_CACHE_HIT'
WHEN q.PERCENTAGE_SCANNED_FROM_CACHE > 0 THEN 'LOW_CACHE_HIT'
ELSE 'NO_CACHE_HIT'
END as CACHE_EFFICIENCY,
-- Resource Usage Classification
CASE
WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 AND q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'BOTH_SPILL'
WHEN q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'REMOTE_SPILL'
WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 'LOCAL_SPILL'
ELSE 'NO_SPILL'
END as SPILL_CLASSIFICATION,
-- Analysis metadata
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.QUERY_ID IS NOT NULL
ORDER BY q.START_TIME DESC;
-- =====================================================
-- REFRESH PROCEDURES
-- =====================================================
-- Procedure to refresh Query History Summary
CREATE OR REPLACE PROCEDURE REFRESH_QUERY_HISTORY_SUMMARY()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE QUERY_HISTORY_SUMMARY AS
SELECT
q.QUERY_ID,
q.QUERY_HASH,
q.QUERY_PARAMETERIZED_HASH,
LEFT(q.QUERY_TEXT, 100) as QUERY_TEXT_PREVIEW,
q.QUERY_TYPE,
q.QUERY_TAG,
q.START_TIME,
q.END_TIME,
q.TOTAL_ELAPSED_TIME,
q.COMPILATION_TIME,
q.EXECUTION_TIME,
q.USER_NAME,
q.USER_TYPE,
q.ROLE_NAME,
q.ROLE_TYPE,
q.SESSION_ID,
q.WAREHOUSE_ID,
q.WAREHOUSE_NAME,
q.WAREHOUSE_SIZE,
q.WAREHOUSE_TYPE,
q.CLUSTER_NUMBER,
q.DATABASE_ID,
q.DATABASE_NAME,
q.SCHEMA_ID,
q.SCHEMA_NAME,
q.USER_DATABASE_NAME,
q.USER_SCHEMA_NAME,
q.EXECUTION_STATUS,
q.ERROR_CODE,
LEFT(q.ERROR_MESSAGE, 200) as ERROR_MESSAGE_PREVIEW,
q.BYTES_SCANNED,
q.PERCENTAGE_SCANNED_FROM_CACHE,
q.BYTES_WRITTEN,
q.ROWS_PRODUCED,
q.ROWS_INSERTED,
q.ROWS_UPDATED,
q.ROWS_DELETED,
q.CREDITS_USED_CLOUD_SERVICES,
q.BYTES_SPILLED_TO_LOCAL_STORAGE,
q.BYTES_SPILLED_TO_REMOTE_STORAGE,
q.PARTITIONS_SCANNED,
q.PARTITIONS_TOTAL,
q.QUEUED_PROVISIONING_TIME,
q.QUEUED_REPAIR_TIME,
q.QUEUED_OVERLOAD_TIME,
q.TRANSACTION_BLOCKED_TIME,
CASE
WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
ELSE '5+ minutes'
END as DURATION_BUCKET,
CASE
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
ELSE '100+ cents'
END as CREDIT_BUCKET,
CASE
WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'SPILLED'
ELSE 'NO_SPILL'
END as SPILL_STATUS,
CASE
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) > 0 THEN 'QUEUED'
ELSE 'NOT_QUEUED'
END as QUEUE_STATUS,
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.QUERY_ID IS NOT NULL
ORDER BY q.START_TIME DESC;
RETURN 'QUERY_HISTORY_SUMMARY table refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$$;
-- Procedure to refresh Query Details Complete
CREATE OR REPLACE PROCEDURE REFRESH_QUERY_DETAILS_COMPLETE()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE QUERY_DETAILS_COMPLETE AS
SELECT
q.QUERY_ID,
q.QUERY_TEXT,
q.QUERY_HASH,
q.QUERY_HASH_VERSION,
q.QUERY_PARAMETERIZED_HASH,
q.QUERY_PARAMETERIZED_HASH_VERSION,
q.QUERY_TYPE,
q.QUERY_TAG,
q.START_TIME,
q.END_TIME,
q.TOTAL_ELAPSED_TIME,
q.COMPILATION_TIME,
q.EXECUTION_TIME,
q.QUEUED_PROVISIONING_TIME,
q.QUEUED_REPAIR_TIME,
q.QUEUED_OVERLOAD_TIME,
q.TRANSACTION_BLOCKED_TIME,
q.CHILD_QUERIES_WAIT_TIME,
q.QUERY_RETRY_TIME,
q.QUERY_RETRY_CAUSE,
q.FAULT_HANDLING_TIME,
q.LIST_EXTERNAL_FILES_TIME,
q.USER_NAME,
q.USER_TYPE,
q.ROLE_NAME,
q.ROLE_TYPE,
q.SECONDARY_ROLE_STATS,
q.SESSION_ID,
q.WAREHOUSE_ID,
q.WAREHOUSE_NAME,
q.WAREHOUSE_SIZE,
q.WAREHOUSE_TYPE,
q.CLUSTER_NUMBER,
q.QUERY_LOAD_PERCENT,
q.DATABASE_ID,
q.DATABASE_NAME,
q.SCHEMA_ID,
q.SCHEMA_NAME,
q.USER_DATABASE_ID,
q.USER_DATABASE_NAME,
q.USER_SCHEMA_ID,
q.USER_SCHEMA_NAME,
q.EXECUTION_STATUS,
q.ERROR_CODE,
q.ERROR_MESSAGE,
q.IS_CLIENT_GENERATED_STATEMENT,
q.BYTES_SCANNED,
q.PERCENTAGE_SCANNED_FROM_CACHE,
q.BYTES_WRITTEN,
q.BYTES_WRITTEN_TO_RESULT,
q.BYTES_READ_FROM_RESULT,
q.ROWS_PRODUCED,
q.ROWS_WRITTEN_TO_RESULT,
q.ROWS_INSERTED,
q.ROWS_UPDATED,
q.ROWS_DELETED,
q.ROWS_UNLOADED,
q.BYTES_DELETED,
q.PARTITIONS_SCANNED,
q.PARTITIONS_TOTAL,
CASE
WHEN q.PARTITIONS_TOTAL > 0 THEN
ROUND((q.PARTITIONS_SCANNED::FLOAT / q.PARTITIONS_TOTAL::FLOAT) * 100, 2)
ELSE 0
END as PARTITION_SCAN_PERCENTAGE,
q.BYTES_SPILLED_TO_LOCAL_STORAGE,
q.BYTES_SPILLED_TO_REMOTE_STORAGE,
q.BYTES_SENT_OVER_THE_NETWORK,
q.CREDITS_USED_CLOUD_SERVICES,
q.OUTBOUND_DATA_TRANSFER_CLOUD,
q.OUTBOUND_DATA_TRANSFER_REGION,
q.OUTBOUND_DATA_TRANSFER_BYTES,
q.INBOUND_DATA_TRANSFER_CLOUD,
q.INBOUND_DATA_TRANSFER_REGION,
q.INBOUND_DATA_TRANSFER_BYTES,
q.EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,
q.EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,
q.EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,
q.EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,
q.EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,
q.QUERY_ACCELERATION_BYTES_SCANNED,
q.QUERY_ACCELERATION_PARTITIONS_SCANNED,
q.QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR,
q.TRANSACTION_ID,
q.PARENT_QUERY_ID,
q.ROOT_QUERY_ID,
q.RELEASE_VERSION,
CASE
WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
ROUND((q.COMPILATION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
ELSE 0
END as COMPILATION_TIME_PERCENTAGE,
CASE
WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
ROUND((q.EXECUTION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
ELSE 0
END as EXECUTION_TIME_PERCENTAGE,
CASE
WHEN q.BYTES_SCANNED > 0 THEN
ROUND(q.ROWS_PRODUCED::FLOAT / (q.BYTES_SCANNED::FLOAT / 1024 / 1024), 2)
ELSE 0
END as ROWS_PER_MB_SCANNED,
CASE
WHEN q.TOTAL_ELAPSED_TIME <= 1000 THEN 'VERY_FAST'
WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN 'FAST'
WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN 'MODERATE'
WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN 'SLOW'
ELSE 'VERY_SLOW'
END as PERFORMANCE_CATEGORY,
CASE
WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 90 THEN 'HIGH_CACHE_HIT'
WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 50 THEN 'MEDIUM_CACHE_HIT'
WHEN q.PERCENTAGE_SCANNED_FROM_CACHE > 0 THEN 'LOW_CACHE_HIT'
ELSE 'NO_CACHE_HIT'
END as CACHE_EFFICIENCY,
CASE
WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 AND q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'BOTH_SPILL'
WHEN q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'REMOTE_SPILL'
WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 'LOCAL_SPILL'
ELSE 'NO_SPILL'
END as SPILL_CLASSIFICATION,
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.QUERY_ID IS NOT NULL
ORDER BY q.START_TIME DESC;
RETURN 'QUERY_DETAILS_COMPLETE table refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$$;
-- =====================================================
-- EXAMPLE USAGE QUERIES
-- =====================================================
-- To refresh both tables:
-- CALL REFRESH_QUERY_HISTORY_SUMMARY();
-- CALL REFRESH_QUERY_DETAILS_COMPLETE();
-- Example drill-down queries for QUERY_HISTORY_SUMMARY:
-- 1. Find all queries by a specific user
-- SELECT * FROM QUERY_HISTORY_SUMMARY WHERE USER_NAME = 'YOUR_USER' ORDER BY START_TIME DESC;
-- 2. Find all failed queries
-- SELECT * FROM QUERY_HISTORY_SUMMARY WHERE EXECUTION_STATUS = 'FAIL' ORDER BY START_TIME DESC;
-- 3. Find slow queries (5+ minutes)
-- SELECT * FROM QUERY_HISTORY_SUMMARY WHERE DURATION_BUCKET = '5+ minutes' ORDER BY TOTAL_ELAPSED_TIME DESC;
-- 4. Find spilled queries
-- SELECT * FROM QUERY_HISTORY_SUMMARY WHERE SPILL_STATUS = 'SPILLED' ORDER BY START_TIME DESC;
-- 5. Find queries by warehouse
-- SELECT * FROM QUERY_HISTORY_SUMMARY WHERE WAREHOUSE_NAME = 'YOUR_WAREHOUSE' ORDER BY START_TIME DESC;
-- Example detail lookup for QUERY_DETAILS_COMPLETE:
-- 1. Get complete details for a specific query
-- SELECT * FROM QUERY_DETAILS_COMPLETE WHERE QUERY_ID = 'YOUR_QUERY_ID';
-- 2. Get performance analysis for slow queries
-- SELECT QUERY_ID, QUERY_TEXT_PREVIEW, TOTAL_ELAPSED_TIME, COMPILATION_TIME_PERCENTAGE,
-- EXECUTION_TIME_PERCENTAGE, CACHE_EFFICIENCY, SPILL_CLASSIFICATION
-- FROM QUERY_DETAILS_COMPLETE
-- WHERE PERFORMANCE_CATEGORY IN ('SLOW', 'VERY_SLOW')
-- ORDER BY TOTAL_ELAPSED_TIME DESC;
-- View both tables
SELECT 'QUERY_HISTORY_SUMMARY' as TABLE_NAME, COUNT(*) as ROW_COUNT FROM QUERY_HISTORY_SUMMARY
UNION ALL
SELECT 'QUERY_DETAILS_COMPLETE' as TABLE_NAME, COUNT(*) as ROW_COUNT FROM QUERY_DETAILS_COMPLETE;
Armaan Khan
Posted on
query histoy
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)