検証結果
- queue_time は elapsed_time に含まれる
検証手順
- 手動WLMで Default queue の同時実行数を 1 に設定する。
$ nohup ./exec_all.sh &
- 別セッションで以下のクエリを実行する(同時実行数が1なのでキュー待ちになる)
dev awsuser 20230606_21:00:16 =# UPDATE testtab1 SET name='fff' WHERE id=1;
UPDATE 2
Time: 392967.335 ms (06:32.967)
- SYS_QUERY_HISTORY を確認する。
- elapsed_time と queue_time + execution_time がほぼ近い値になり、elapsed_time は queue_time を含む。
dev awsuser 20230606_21:02:08 =# select start_time, end_time, end_time-start_time end_minus_start, elapsed_time, queue_time+execution_time+planning_time+lock_wait_time time_sum, queue_time, execution_time, compile_time, planning_time, lock_wait_time, substring(query_text,1,50) from sys_query_history where query_text like '%testtab1%' order by start_time;
start_time | end_time | end_minus_start | elapsed_time | time_sum | queue_time | execution_time | compile_time | planning_time | lock_wait_time | substring
2023-06-06 12:00:45.373202 | 2023-06-06 12:07:17.154041 | 391780839 | 391780839 | 391769694 | 391737485 | 29576 | 0 | 2619 | 14 | UPDATE testtab1 SET name='fff' WHERE id=1;
export LC_ALL=C
SCRIPT_BASE_NAME=$(basename $0 .sh)
CURRENT_DATE=`date '+%Y-%m-%d-%H%M%S'`
BASE_DIR=$(cd $(dirname $0);pwd)
cd $BASE_DIR
PG_HOST=${PG_HOST:-rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com}
PG_USER=${PG_USER:-awsuser}
PG_DB=${PG_DB:-tpch_3tb}
PG_PORT=${PG_PORT:-5439}
LOG_DIR=${LOG_DIR:-log}
if [ ! -d "${LOG_DIR}" ]
then
mkdir ${LOG_DIR}
fi
psql "host=$PG_HOST user=$PG_USER dbname=$PG_DB port=$PG_PORT" -a -f exec_all.sql \
>> "${LOG_DIR}/${SCRIPT_BASE_NAME}_${CURRENT_DATE}.log" 2>&1
\i query_0.sql
\i query_1.sql
\i query_10.sql
\i query_2.sql
\i query_3.sql
\i query_4.sql
\i query_5.sql
\i query_6.sql
\i query_7.sql
\i query_8.sql
\i query_9.sql