SQL Execution Monitoring

SQL Historical Monitoring Data

Historical SQL monitoring data records the resource usage (including memory, disk spilling, CN and DN duration, OBS access time, and LakeFormation access time) and SQL execution plan information (EXPLAIN/EXPLAIN PERFORMANCE). Historical monitoring data is generated only after the SQL statement is executed.

The fields displayed in the SQL historical monitoring data are as follows:

**Table 1** Fields displayed in SQL historical monitoring data
Field Name	Subfield Name	Type	Description
session_id	-	text	Session ID of a statement.
statement_id	-	text	Statement ID.
start_time	-	timestampt with zone	Start time of a statement.
finish_time	-	timestampt with zone	Reported/End time of a statement.
duration	-	bigint	Statement running duration. Unit: ms.
cn_actor_info	-	text	Serverless information of a CN.
	actor_name	text	Name of the pod where a CN is.
	actor_ip	text	IP address of the pod where a CN is.
	actor_id	text	Name of a CN within a pod.
dn_actor_info	-	text	Serverless information of a DN.
	actor_name	text	Name of the pod where a DN is.
	actor_ip	text	ID of the pod where a DN is.
	actor_id	text	Name of a DN within a pod.
startup_coordinator_duration	-	bigint	Duration to start a CN. Unit: ms.
create_session_duration	-	bigint	Duration to create a session. Unit: ms.
enqueue_statement_duration	-	bigint	Duration to add a statement to a task queue. Unit: ms.
queue_duration	-	bigint	Queuing duration of a statement in a task queue. Unit: ms.
write_resultset_duration	-	bigint	Duration to write a statement to a result set. Unit: ms.
queryid	-	bigint	ID of a debug query.
status	-	text	Statement status.
abort_info	-	text	Error message.
schemaname	-	text	Schema name when a statement is executed.
query	-	text	Statement.
query_plan	-	text	Explain information when GUC parameter resource_track_level is set to query. Explain performance information when set to perf.
tid	-	bigint	Thread ID.
parse_time	-	bigint	Time from parsing to execution start. Unit: ms.
estimate_memory	-	int	Estimated memory of a statement. Unit: MB.
dn_time	-	text	DN duration information.
	min_dn_time	bigint	Minimum DN duration. Unit: ms.
	max_dn_time	bigint	Maximum DN duration. Unit: ms.
	average_dn_time	bigint	Average DN duration. Unit: ms.
	dntime_skew_percent	int	Skew percentage of DN duration. Unit: %.
dn_cpu_time	-	text	DN CPU duration.
	min_cpu_time	bigint	Minimum DN CPU duration. Unit: ms.
	max_cpu_time	bigint	Maximum DN CPU duration. Unit: ms.
	average_cpu_time	bigint	Average DN CPU duration. Unit: ms.
	total_cpu_time	bigint	Total DN CPU duration. Unit: ms.
	cpu_skew_percent	int	Skew percentage of DN CPU duration. Unit: %.
dn_peak_memory	-	text	DN peak memory information.
	min_peak_memory	int	Minimum peak memory of all DNs. Unit: MB.
	max_peak_memory	int	Maximum peak memory of all DNs. Unit: MB.
	average_peak_memory	int	Average peak memory of all DNs. Unit: MB.
	memory_skew_percent	int	Memory skew rate. Unit: %.
cn_spill_info	-	text	CN disk spill information.
	spill_count	bigint	Number of spills from a CN.
	spill_size	bigint	CN disk spill size. Unit: MB.
	spill_obs_size	bigint	Size of data spilled to OBS by a CN. Unit: MB.
	write_obs_size	bigint	Size of data directly written to OBS by a CN. Unit: MB.
	write_disk_size	bigint	Size of data written to local disks by a CN. Unit: MB.
dn_spill_info	-	text	DN disk spill information.
	spill_dn_num	int	Number of DNs with disk spills.
	spill_count	int	Total number of DN disk spills.
	min_spill_size	bigint	Maximum spill size. Unit: MB.
	max_spill_size	bigint	Minimum spill size. Unit: MB.
	average_spill_size	bigint	Average spill size. Unit: MB.
	spill_skew_percent	int	Spill skew rate. Unit: %.
	write_disk_total_size	bigint	Total amount of data spilled to local disks by a DN. Unit: MB.
	write_disk_max_size	bigint	Maximum amount of data written to local disks by a DN. Unit: MB.
	write_disk_min_size	bigint	Minimum amount of data written to local disks by a DN. Unit: MB.
	spill_obs_total_size	bigint	Total amount of data spilled to OBS by a DN. Unit: MB.
	spill_obs_max_size	bigint	Maximum amount of data spilled to OBS by a DN. Unit: MB.
	spill_obs_min_size	bigint	Minimum amount of data spilled to OBS by a DN. Unit: MB.
	write_obs_total_size	bigint	Total amount of data directly written to OBS by a DN. Unit: MB.
	write_obs_max_size	bigint	Maximum amount of data directly written to OBS by a DN. Unit: MB.
	write_obs_min_size	bigint	Minimum amount of data directly written to OBS by a DN. Unit: MB.
disk_cache_info	-	text	Cache information.
	disk_cache_hit_ratio	numeric	Disk cache hit rate.
	disk_cache_disk_read_size	bigint	Read size of disk cache. Unit: byte.
	disk_cache_disk_write_size	bigint	Write size of disk cache. Unit: byte.
	disk_cache_remote_read_size	bigint	Remote read size of disk cache. Unit: byte.
	disk_cache_remote_read_time	bigint	Count of remote reads from disk cache. Unit: times.
obs_info	-	text	OBS information.
	vfs_scan_bytes	bigint	Bytes scanned by the OBS v file system. Unit: byte.
	vfs_remote_read_bytes	bigint	Bytes remotely read by the OBS v file system. Unit: byte.
	disk_cache_load_time	bigint	Local load time of disk cache. Unit: μs.
	disk_cache_conflict_count	bigint	Hash collision count of disk cache blocks. Unit: times.
	disk_cache_error_count	bigint	Error count of disk cache. Unit: times.
	disk_cache_error_code	bigint	Error code of disk cache.
	obs_io_req_avg_rtt	bigint	Average round-trip time of OBS I/O requests. Unit: μs.
	obs_io_req_avg_latency	bigint	Average latency of OBS I/O requests. Unit: μs.
	obs_io_req_latency_gt_1s	bigint	Count of OBS I/O request delays exceeding 1s. Unit: times.
	obs_io_req_latency_gt_10s	bigint	Count of OBS I/O request delays exceeding 10s. Unit: times.
	obs_io_req_count	bigint	Number of OBS I/O requests. Unit: times.
	obs_io_req_retry_count	bigint	Number of OBS I/O request retries. Unit: times.
	obs_io_req_rate_limit_count	bigint	Rate limit count of OBS I/O requests. Unit: times.
yr_info	-	text	YuanRong API invocation information.
	total_call	bigint	Total number of calls.
	failed_call	bigint	Number of failed calls.
	avg_call_during	bigint	Average API invocation duration. Unit: millisecond.
	min_call_during	bigint	Minimum calling duration. Unit: millisecond.
	max_call_during	bigint	Maximum calling duration. Unit: millisecond.
	total_data	bigint	Total data volume. Unit: byte.
	avg_data_rate	bigint	Average data processing rate. Unit: byte/ms.
	min_data_rate	bigint	Minimum data processing rate. Unit: byte/ms.
	max_data_rate	bigint	Maximum data processing rate. Unit: byte/ms.

Current SQL monitoring data is stored in the OBS bucket of the result set, in the same directory as the result set folder. It can be accessed through the OBS service on the Huawei Cloud website. Based on the path of the current query's result set, locate the corresponding SQL monitoring data file in the same directory and download it for viewing. Upon opening the SQL monitoring data file, the following information is displayed:

Figure 1 Content of a historical SQL monitoring information file
Click to enlarge

SQL Real-Time Monitoring Data

Real-time SQL monitoring data also records the resource usage of query jobs (including memory, disk spillage, CN and DN durations, OBS access time, and LakeFormation access time) as well as SQL execution plan information (EXPLAIN/EXPLAIN PERFORMANCE). However, the real-time SQL monitoring data can only retrieve information for statements currently being executed. It cannot obtain information for statements that have already completed execution.

Usage: select * from gs_wlm_session_statistics();

The fields displayed in the SQL real-time monitoring data are as follows:

**Table 2** Fields displayed in SQL real-time monitoring data
Field	Type	Description
session_id	text	Session ID of a statement.
statement_id	text	Statement ID.
start_time	timestampt with zone	Start time of a statement.
duration	bigint	Statement running duration. Unit: ms.
estimate_total_time	bigint	Estimated statement running duration. Unit: ms.
estimate_left_time	bigint	Estimated remaining running duration of a statement. Unit: ms.
cn_actor_info	text	Serverless information of a CN.
dn_actor_info	text	Serverless information of a DN.
startup_coordinator_duration	bigint	Duration to start a CN. Unit: ms.
create_session_duration	bigint	Duration to create a session. Unit: ms.
enqueue_statement_duration	bigint	Duration to add a statement to a task queue. Unit: ms.
queue_duration	bigint	Queuing duration of a statement in a task queue. Unit: ms.
write_resultset_duration	bigint	Duration to write a statement to a result set. Unit: ms.
queryid	bigint	ID of a debug query.
schemaname	text	Schema name when a statement is executed.
query	text	Statement.
query_plan	text	Explain information.
tid	bigint	Thread ID.
parse_time	bigint	Time from parsing to execution start. Unit: ms.
estimate_memory	int	Estimated memory of a statement. Unit: MB.
min_dn_time	bigint	Minimum DN duration. Unit: ms.
max_dn_time	bigint	Maximum DN duration. Unit: ms.
average_dn_time	bigint	Average DN duration. Unit: ms.
dn_time_skew_percent	int	Skew percentage of DN duration. Unit: %.
dn_min_cpu_time	bigint	Minimum DN CPU duration. Unit: ms.
dn_max_cpu_time	bigint	Maximum DN CPU duration. Unit: ms.
dn_average_cpu_time	bigint	Average DN CPU duration. Unit: ms.
dn_total_cpu_time	bigint	Total DN CPU duration. Unit: ms.
dn_cpu_skew_percent	int	Skew percentage of DN CPU duration. Unit: %.
dn_min_peak_memory	int	Minimum peak memory of all DNs. Unit: MB.
dn_max_peak_memory	int	Maximum peak memory of all DNs. Unit: MB.
dn_average_peak_memory	int	Average peak memory of all DNs. Unit: MB.
dn_memory_skew_percent	int	Memory skew rate. Unit: %.
dn_spill_)num	int	Number of DNs with disk spills.
dn_spill_count	int	Total number of DN spills.
dn_min_spill_size	bigint	Maximum spill size. Unit: MB.
dn_max_spill_size	bigint	Minimum spill size. Unit: MB.
dn_average_spill_size	bigint	Average spill size. Unit: MB.
dn_spill_skew_percent	int	Spill skew rate. Unit: %.
dn_write_disk_total_size	bigint	Total size of data written to disks by a DN. Unit: MB.
dn_write_disk_max_size	bigint	Maximum amount of data written to disks by a DN. Unit: MB.
dn_write_disk_min_size	bigint	Minimum amount of data written to disks by a DN. Unit: MB.
dn_spill_obs_total_size	bigint	Total amount of data spilled to OBS by a DN. Unit: MB.
dn_spill_obs_max_size	bigint	Maximum amount of data spilled to OBS by a DN. Unit: MB.
dn_spill_obs_min_size	bigint	Minimum amount of data spilled to OBS by a DN. Unit: MB.
dn_write_obs_total_size	bigint	Total amount of data directly written to OBS by a DN. Unit: MB.
dn_write_obs_max_size	bigint	Maximum amount of data directly written to OBS by a DN. Unit: MB.
dn_write_obs_min_size	bigint	Minimum amount of data directly written to OBS by a DN. Unit: MB.
cn_spill_count	int	Number of spills from a CN.
cn_spill_size	bigint	Total size of data spilled from a CN. Unit: MB.
cn_spill_obs_size	bigint	Amount of data spilled to OBS by a CN. Unit: MB.
cn_write_obs_size	bigint	Amount of data directly written to OBS by a CN. Unit: MB.
cn_write_obs_disk_size	bigint	Amount of data spilled to local disks by a CN. Unit: MB.
disk_cache_hit_ratio	numeric	Disk cache hit rate.
disk_cache_disk_read_size	bigint	Read size of disk cache. Unit: byte.
disk_cache_disk_write_size	bigint	Write size of disk cache. Unit: byte.
disk_cache_remote_read_size	bigint	Remote read size of disk cache. Unit: byte.
disk_cache_remote_read_time	bigint	Count of remote reads from disk cache. Unit: times.
vfs_scan_bytes	bigint	Bytes scanned by the OBS v file system. Unit: byte.
vfs_remote_read_bytes	bigint	Bytes remotely read by the OBS v file system. Unit: byte.
disk_cache_load_time	bigint	Local load time of disk cache. Unit: μs.
disk_cache_conflict_count	bigint	Hash collision count of disk cache blocks. Unit: times.
disk_cache_error_count	bigint	Error count of disk cache. Unit: times.
disk_cache_error_code	bigint	Error code of disk cache.
obs_io_req_avg_rtt	bigint	Average round-trip time of OBS I/O requests. Unit: μs.
obs_io_req_avg_latency	bigint	Average latency of OBS I/O requests. Unit: μs.
obs_io_req_latency_gt_1s	bigint	Count of OBS I/O request delays exceeding 1s. Unit: times.
obs_io_req_latency_gt_10s	bigint	Count of OBS I/O request delays exceeding 10s. Unit: times.
obs_io_req_count	bigint	Number of OBS I/O requests. Unit: times.
obs_io_req_retry_count	bigint	Number of OBS I/O request retries. Unit: times.
obs_io_req_rate_limit_count	bigint	Rate limit count of OBS I/O requests. Unit: times.