Viswamitra's blog

select * from table(information_schema.query_history())  where execution_status  in ('RESUMING_WAREHOUSE','RUNNING','QUEUED','BLOCKED');

select * from table(information_schema.QUERY_HISTORY_BY_USER(USER_NAME => '<usr name>'))  where execution_status  in ('RESUMING_WAREHOUSE','RUNNING','QUEUED','BLOCKED');

--- Queries taking more than 10 sec
SET long_running_threshold_seconds = 10;

SELECT * FROM TABLE(information_schema.query_history()) WHERE (execution_status = 'RUNNING' AND datediff('seconds',start_time,current_timestamp()) > $long_running_threshold_seconds);

--- To get the history , list top 10 queries
use schema snowflake.account_usage;

select QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,sum(TOTAL_ELAPSED_TIME)/1000 as Execution_time_sec
from query_history
group by QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME 
order by sum(TOTAL_ELAPSED_TIME) desc
limit 10;

use schema snowflake.account_usage;

select QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,USER_NAME,sum(CREDITS_USED_CLOUD_SERVICES) as credits_used
from query_history
group by QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,USER_NAME
order by sum(CREDITS_USED_CLOUD_SERVICES) desc
limit 10;

use schema snowflake.account_usage;

select QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,USER_NAME,PARTITIONS_SCANNED,PARTITIONS_TOTAL
from query_history
where PARTITIONS_SCANNED=PARTITIONS_TOTAL
and PARTITIONS_TOTAL > 0
order by PARTITIONS_TOTAL desc;

select * from table(information_schema.query_history())  where execution_status  in ('BLOCKED');

select system$abort_transaction(<transaction id>);
select system$abort_transaction(1594638696404);

use schema snowflake_sample_data.tpch_sf1;

--- Check the query execution plan in text format
explain using text select
       l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1-l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1-l_discount) * (1+l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
 from
       lineitem
 where
       l_shipdate <= dateadd(day, -90, to_date('1998-12-01'))
 group by
       l_returnflag,
       l_linestatus
 order by
       l_returnflag,
       l_linestatus;

GlobalStats:
    partitionsTotal=10
    partitionsAssigned=10
    bytesAssigned=165278208
Operations:
1:0     ->Result  LINEITEM.L_RETURNFLAG, LINEITEM.L_LINESTATUS, SUM(LINEITEM.L_QUANTITY), SUM(LINEITEM.L_EXTENDEDPRICE), SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)), SUM((LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) * (1 + LINEITEM.L_TAX)), (SUM(LINEITEM.L_QUANTITY)) / (COUNT(LINEITEM.L_QUANTITY)), (SUM(LINEITEM.L_EXTENDEDPRICE)) / (COUNT(LINEITEM.L_EXTENDEDPRICE)), (SUM(LINEITEM.L_DISCOUNT)) / (COUNT(LINEITEM.L_DISCOUNT)), COUNT(*)  
1:1          ->Sort  LINEITEM.L_RETURNFLAG ASC NULLS LAST, LINEITEM.L_LINESTATUS ASC NULLS LAST  
1:2               ->Aggregate  aggExprs: [SUM(LINEITEM.L_QUANTITY), SUM(LINEITEM.L_EXTENDEDPRICE), SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)), SUM((LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) * (1 + LINEITEM.L_TAX)), COUNT(LINEITEM.L_QUANTITY), COUNT(LINEITEM.L_EXTENDEDPRICE), SUM(LINEITEM.L_DISCOUNT), COUNT(LINEITEM.L_DISCOUNT), COUNT(*)], groupKeys: [LINEITEM.L_RETURNFLAG, LINEITEM.L_LINESTATUS]  
1:3                    ->Filter  LINEITEM.L_SHIPDATE <= '1998-09-02'  
1:4                         ->TableScan  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM  L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE  {partitionsTotal=10, partitionsAssigned=10, bytesAssigned=165278208}

--- To get the plan in text format
select SYSTEM$EXPLAIN_JSON_TO_TEXT(system$explain_plan_json('01958e99-0187-c08e-0000-00214103b041')) as explain_plan;

GlobalStats:
	bytesAssigned=0
	partitionsAssigned=0
	partitionsTotal=0
Operations:
1:0     ->Result  TEST_123.ID  
1:1          ->TableScan  LOCK_TEST.PUBLIC.TEST_123  ID  {partitionsTotal=0, partitionsAssigned=0, bytesAssigned=0}

--- To get the plan in json format
select system$explain_plan_json('01958e99-0187-c08e-0000-00214103b041') as explain_plan;{"GlobalStats":{"partitionsTotal":0,"partitionsAssigned":0,"bytesAssigned":0},"Operations":[[{"id":0,"operation":"Result","expressions":["TEST_123.ID"]},{"id":1,"parent":0,"operation":"TableScan","objects":["LOCK_TEST.PUBLIC.TEST_123"],"expressions":["ID"],"partitionsAssigned":0,"partitionsTotal":0,"bytesAssigned":0}]]}

--- login history for last 24 hours
select * from table(information_schema.login_history(dateadd('hours',-24,current_timestamp()),current_timestamp())) order by event_timestamp;

--- login history of a user in last 24 hours
select * from table(information_schema.login_history_by_user('<USERNAME>',dateadd('hours',-1,current_timestamp()),current_timestamp())) order by event_timestamp;

select * from snowflake.account_usage.login_history order by event_timestamp;