このエントリは AWS Analytics Advent Calendar 2024 の8日目の記事です。
7日目は AWS Japan の Analytics Specialist SA ヨータ・ハマオカの AWS上でIcebergテーブルを作成する方法についての検討メモ #iceberg - Qiita でした。
SYS_QUERY_HISTORY に user_query_hash 列と generic_query_hash 列が追加されました。このエントリではこの追加された列をどう使うか具体例を紹介します。
クエリハッシュは、データウェアハウスで実行されたクエリに対して生成される一意のクエリ署名です。クエリハッシュを使用すると、一定期間にわたるクエリの傾向分析を実行したり、異なる期間のクエリパフォーマンスを比較したりして、クエリパフォーマンスを調査できます。この機能により、SYS_QUERY_HISTORY ビューに、クエリリテラルを含むハッシュである user_query_hash と、クエリリテラルを含まないハッシュである generic_query_hash という 2 つの新しい列が追加されました。
Amazon Redshift がクエリパフォーマンスのモニタリングを改善するためのクエリ識別子を導入 - AWS
Oracle Database でいう SQL_ID ですね。user_query_hash は where 句などのリテラルが異なれば別物、generic_query_hash はリテラル値は異なってもクエリ文が同じであれば同一値になります、Oracle Database でいうとバインド変数を使った場合の SQL_ID みたいなイメージですね。クエリリテラルを含まない generic_query_hash を生成してくれるのは非常に便利ですね。
例えば、generic_query_hash を使って、平均と合計の elapsed_time が長い上位クエリをピックアップすることができます。
平均 elapsed_time 上位10件
- クエリ
select generic_query_hash, count(generic_query_hash) cnt, min(query_type) query_type, avg(elapsed_time)/1000/1000 "avg_elapsed_time(s)", avg(execution_time)/1000/1000 "avg_execution_time(s)", avg(queue_time)/1000 "avg_queue_time(ms)", avg(compile_time)/1000 "avg_compile_time(ms)", avg(planning_time)/1000 "avg_planning_time(ms)", avg(lock_wait_time)/1000 "avg_lock_wait_time(ms)" from sys_query_history group by generic_query_hash order by avg(elapsed_time) desc limit 10;
- 結果
generic_query_hash | cnt | query_type | avg_elapsed_time(s) | avg_execution_time(s) | avg_queue_time(ms) | avg_compile_time(ms) | avg_planning_time(ms) | avg_lock_wait_time(ms) ------------------------------------------+-----+------------+---------------------+-----------------------+--------------------+----------------------+-----------------------+------------------------ YyRSNHHwZ0Y= | 1 | SELECT | 146 | 127 | 167 | 19725 | 26 | 0 yIMt4+EVnu4= | 1 | SELECT | 142 | 133 | 0 | 9827 | 25 | 0 EfHHVqvIamU= | 1 | SELECT | 141 | 132 | 0 | 8483 | 26 | 0 pDVobZbCr3I= | 1 | SELECT | 139 | 131 | 200 | 9917 | 25 | 0 EgrXtVMUTcg= | 1 | SELECT | 136 | 0 | 135762 | 1 | 170 | 0 sG6Fzj+JOo0= | 1 | SELECT | 132 | 132 | 0 | 0 | 25 | 0 AjKWNCQ0bk4= | 1 | SELECT | 132 | 132 | 0 | 0 | 27 | 0 6K5zz0x21hA= | 1 | SELECT | 132 | 132 | 95 | 0 | 25 | 0 68oFgnOWby0= | 8 | SELECT | 128 | 128 | 201 | 434 | 16 | 0 rxGgdkiTvbQ= | 1 | SELECT | 127 | 127 | 0 | 0 | 24 | 0 (10 rows)
合計 elapsed_time 上位10件
- クエリ
select generic_query_hash, count(generic_query_hash) cnt, min(query_type) query_type, sum(elapsed_time)/1000/1000 "sum_elapsed_time(s)", avg(elapsed_time)/1000/1000 "avg_elapsed_time(s)", avg(execution_time)/1000/1000 "avg_execution_time(s)", avg(queue_time)/1000 "avg_queue_time(ms)", avg(compile_time)/1000 "avg_compile_time(ms)", avg(planning_time)/1000 "avg_planning_time(ms)", avg(lock_wait_time)/1000 "avg_lock_wait_time(ms)" from SYS_QUERY_HISTORY group by generic_query_hash order by sum(elapsed_time) desc limit 10;
- 結果
generic_query_hash | cnt | query_type | sum_elapsed_time(s) | avg_elapsed_time(s) | avg_execution_time(s) | avg_queue_time(ms) | avg_compile_time(ms) | avg_planning_time(ms) | avg_lock_wait_time(ms) ------------------------------------------+-----+------------+---------------------+---------------------+-----------------------+--------------------+----------------------+-----------------------+------------------------ 68oFgnOWby0= | 8 | SELECT | 1030 | 128 | 128 | 201 | 434 | 16 | 0 9uU/vUfAYWQ= | 8 | SELECT | 849 | 106 | 103 | 128 | 2099 | 104451 | 0 FvVb53c411c= | 9 | SELECT | 651 | 72 | 72 | 0 | 0 | 10 | 0 Qp1LFBVz0ag= | 8 | SELECT | 638 | 79 | 78 | 0 | 1669 | 21 | 0 YP3i/XrP+1g= | 9 | SELECT | 533 | 59 | 57 | 0 | 2018 | 22 | 0 k+7kl7fqUtA= | 9 | SELECT | 366 | 40 | 35 | 0 | 5124 | 20 | 0 E7UqcVBHI64= | 8 | SELECT | 353 | 44 | 43 | 67 | 1042 | 18 | 0 mMl0zjIizfo= | 8 | SELECT | 333 | 41 | 39 | 48 | 1994 | 14 | 0 DWUnAfgTCKY= | 4 | SELECT | 330 | 82 | 80 | 1372 | 771 | 13 | 0 A7lpACU8NBc= | 4 | SELECT | 321 | 80 | 80 | 0 | 0 | 13 | 0 (10 rows)
elapsed_time で集計（GROUP BY）していますが、execution_time など他の列で集計すれば別の軸でのランキングを出すことが可能です。
9日目は...
