Redshift で実行時間が長いクエリの disk spill/skewness を確認するクエリサンプル

• Redshift で実行時間が長いクエリの disk spill を確認するクエリサンプル

\pset format unaligned
\pset fieldsep '|'
\t off

select any_value(a.username) username,
    any_value(a.query_type) query_type, 
    any_value(a.generic_query_hash) generic_query_hash,
    a.query_id,
    any_value(a.transaction_id) transaction_id,
    any_value(a.query_text) query_text, 
    any_value(a.execution_time) execution_time,
    sum(spilled_block_local_disk) sum_spilled_block_local_disk, 
    sum(spilled_block_remote_disk) sum_spilled_block_remote_disk,
    any_value(a.query_priority) query_priority
from sys_query_history a, sys_query_detail b
where a.query_id = b.query_id
and b.metrics_level = 'segment'
and a.execution_time > 1000*1000*1
group by a.query_id
order by execution_time;

• Redshift で実行時間が長いクエリの time_skewness/data_skewness を確認するクエリサンプル。

select any_value(a.username) username,
    any_value(a.query_type) query_type, 
    any_value(a.generic_query_hash) generic_query_hash,
    a.query_id,
    any_value(a.transaction_id) transaction_id,
    any_value(a.query_text) query_text, 
    any_value(a.execution_time) execution_time,
    max(time_skewness) max_time_skewness,
    max(data_skewness) max_data_skewness,
    any_value(a.query_priority) query_priority
from sys_query_history a, sys_query_detail b
where a.query_id = b.query_id
and b.metrics_level = 'step'
and a.execution_time > 1000*1000*1
group by a.query_id
order by execution_time;