GS_WLM_SESSION_INFO
GS_WLM_SESSION_INFO records load management information about a completed job executed on the current CN. The data is dumped from the kernel to a system catalog. If the GUC parameter enable_resource_record is set to on, the system periodically imports records in GS_WLM_SESSION_HISTORY to this system catalog. You are not advised to enable this function because it occupies storage space and affects performance. You are advised to disable it after performance locating and monitoring tasks are complete.
- Clusters with coupled storage and compute:
For the clusters of version 9.1.0 or earlier, use the GS_WLM_SESSION_INFO system table name for query.
For the clusters of version is 9.1.1 or later, use the GS_WLM_SESSION_INFO_V2 system table name for query.
- Clusters with decoupled storage and compute: Use the GS_WLM_SESSION_INFO_V3 system table name for query.
In clusters of version 9.1.1.300 or later, the system aggregates and records short queries that are successfully executed within 100 ms and contain sql_hash. The main columns for aggregation are datid, dbname, resource_pool, node_group, username, application_name, client_addr, sql_hash, plan_hash, query_band, and nodename.
- The schema of the GS_WLM_SESSION_INFO system table is dbms_om.
- The GS_WLM_SESSION_INFO system catalog can be queried only in the postgres database. If it is queried in other databases, an error is reported.
Table 1 lists the columns in the GS_WLM_SESSION_INFO system catalog.
| Name | Type | Meanings of Long Query Records (Longer Than 100 ms) | Meanings of Short Query Aggregation Records (Shorter Than 100 ms) |
|---|---|---|---|
| datid | oid | OID of the database this backend is connected to | OID of the database the backend is connected to |
| dbname | text | Name of the database the backend is connected to | Name of the database the backend is connected to |
| schemaname | text | Schema name | Schema name of the first statement with the same aggregation primary key in the aggregation period |
| nodename | text | Name of the CN where the statement is run | Name of the CN where the statement is executed |
| username | text | User name used for connecting to the backend | Username for connecting to the backend |
| application_name | text | Name of the application that is connected to the backend | Name of the application connected to the backend |
| client_addr | inet | IP address of the client connected to this backend. If this column is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum. | IP address of the client connected to the backend |
| client_hostname | text | Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This column will only be non-null for IP connections, and only when log_hostname is enabled. | client_hostname of the first statement with the same aggregation primary key in the aggregation period |
| client_port | integer | TCP port number that the client uses for communication with this backend, or -1 if a Unix socket is used | client_port of the first statement with the same aggregation primary key in the aggregation period |
| query_band | text | Job type, which can be set using the GUC parameter query_band and is a null string by default. | query_band of the first statement with the same aggregation primary key in the aggregation period |
| block_time | bigint | Duration that a statement is blocked before being executed, including the statement parsing and optimization duration. The unit is ms. | Average value of block_time of all statements with the same aggregation primary key in the aggregation period |
| start_time | timestamp with time zone | Time when the statement starts to be run | start_time of the first statement with the same aggregation primary key in the aggregation period |
| finish_time | timestamp with time zone | Time when the statement execution ends | finish_time of the last statement with the same aggregation primary key in the aggregation period |
| duration | bigint | Execution time of a statement. The unit is ms. | Average duration of all statements with the same aggregation primary key in the aggregation period |
| estimate_total_time | bigint | Estimated execution time of a statement. The unit is ms. | Average value of estimate_total_time of all statements with the same aggregation primary key in the aggregation period |
| status | text | Final statement execution status. Its value can be finished (normal) or aborted (abnormal). The statement status here is the execution status of the database server. If the statement is successfully executed on the database server but an error is reported in the result set, the statement status is finished. | Final statement execution status. Only the statements that are successfully executed are aggregated. The default value is finished. |
| abort_info | text | Exception information displayed if the final statement execution status is aborted. | Left blank |
| resource_pool | text | Resource pool used by the user | Resource pool used by the user |
| control_group | text | Cgroup used by the statement | control_group of the first statement with the same aggregation primary key in the aggregation period |
| estimate_memory | integer | Estimated memory used by a statement on a single instance. The unit is MB. This column takes effect only when the GUC parameter enable_dynamic_workload is set to on. | Average value of estimate_memory of all statements with the same aggregation primary key in the aggregation period |
| min_peak_memory | integer | Minimum memory peak of a statement across all DNs. The unit is MB. | Minimum value of min_peak_memory of all statements with the same aggregation primary key in the aggregation period |
| max_peak_memory | integer | Maximum memory peak of a statement across all DNs. The unit is MB. | Maximum value of max_peak_memory of all statements with the same aggregation primary key in the aggregation period |
| average_peak_memory | integer | Average memory usage during statement execution. The unit is MB. | Average value of average_peak_memory of all statements with the same aggregation primary key in the aggregation period |
| memory_skew_percent | integer | Memory usage skew of a statement among DNs. | Average value of memory_skew_percent of all statements with the same aggregation primary key in the aggregation period |
| spill_info | text | Statement spill information on all DNs. None indicates that the statement has not been spilled to disks on any DNs. All: The statement has been spilled to disks on all DNs. [a:b]: The statement has been spilled to disks on a of b DNs. | Left blank |
| min_spill_size | integer | Minimum spilled data among all DNs when a spill occurs. The unit is MB. The default value is 0. | Minimum value of min_spill_size of all statements with the same aggregation primary key in the aggregation period |
| max_spill_size | integer | Maximum spilled data among all DNs when a spill occurs. The unit is MB. The default value is 0. | Maximum value of max_spill_size of all statements with the same aggregation primary key in the aggregation period |
| average_spill_size | integer | Average spilled data among all DNs when a spill occurs. The unit is MB. The default value is 0. | Average value of average_spill_size of all statements with the same aggregation primary key in the aggregation period |
| spill_skew_percent | integer | DN spill skew when a spill occurs | Average value of spill_skew_percent of all statements with the same aggregation primary key in the aggregation period |
| min_dn_time | bigint | Minimum execution time of a statement across all DNs. The unit is ms. | Minimum value of min_dn_time of all statements with the same aggregation primary key in the aggregation period |
| max_dn_time | bigint | Maximum execution time of a statement across all DNs. The unit is ms. | Maximum value of max_dn_time of all statements with the same aggregation primary key in the aggregation period |
| average_dn_time | bigint | Average execution time of a statement across all DNs. The unit is ms. | Average value of average_dn_time of all statements with the same aggregation primary key in the aggregation period |
| dntime_skew_percent | integer | Execution time skew of a statement among DNs. | Average value of dntime_skew_percent of all statements with the same aggregation primary key in the aggregation period |
| min_cpu_time | bigint | Minimum CPU time of a statement across all DNs. The unit is ms. | Minimum value of min_cpu_time of all statements with the same aggregation primary key in the aggregation period |
| max_cpu_time | bigint | Maximum CPU time of a statement across all DNs. The unit is ms. | Maximum value of max_cpu_time of all statements with the same aggregation primary key in the aggregation period |
| total_cpu_time | bigint | Total CPU time of a statement across all DNs. The unit is ms. | Total value of total_cpu_time of all statements with the same aggregation primary key in the aggregation period |
| cpu_skew_percent | integer | CPU time skew of a statement among DNs. | Average value of cpu_skew_percent of all statements with the same aggregation primary key in the aggregation period |
| min_peak_iops | integer | Minimum IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. | Minimum value of min_peak_iops of all statements with the same aggregation primary key in the aggregation period |
| max_peak_iops | integer | Maximum IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. | Maximum value of max_peak_iops of all statements with the same aggregation primary key in the aggregation period |
| average_peak_iops | integer | Average IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. | Average value of average_peak_iops of all statements with the same aggregation primary key in the aggregation period |
| iops_skew_percent | integer | I/O skew across DNs. | Average value of iops_skew_percent of all statements with the same aggregation primary key in the aggregation period |
| warning | text | Warning. The following warnings and warnings related to SQL self-diagnosis tuning are displayed:
| warning of the first statement with the same aggregation primary key in the aggregation period |
| queryid | bigint | Internal query ID used for statement execution | queryid of the first statement with the same aggregation primary key in the aggregation period |
| query | text | Statement to be executed. A maximum of 64 KB of strings can be retained. | query of the first statement with the same aggregation primary key in the aggregation period |
| query_plan | text | Execution plan of a statement. Specification restrictions:
| query_plan of the first statement with the same aggregation primary key in the aggregation period |
| node_group | text | Logical cluster of the user running the statement | Logical cluster of the user running the statement |
| pid | bigint | PID of the backend thread of the statement | pid of the first statement with the same aggregation primary key in the aggregation period |
| lane | text | Fast/Slow lane where the statement is executed | lane of the first statement with the same aggregation primary key in the aggregation period |
| unique_sql_id | bigint | ID of the normalized unique SQL. | unique_sql_id of the first statement with the same aggregation primary key in the aggregation period |
| session_id | text | Unique identifier of a session in the database system. Its format is session_start_time.tid.node_name. | session_id of the first statement with the same aggregation primary key in the aggregation period |
| min_read_bytes | bigint | Minimum I/O read bytes of a statement across all DNs. The unit is byte. | Minimum value of min_read_bytes of all statements with the same aggregation primary key in the aggregation period |
| max_read_bytes | bigint | Maximum I/O read bytes of a statement across all DNs. The unit is byte. | Maximum value of max_read_bytes of all statements with the same aggregation primary key in the aggregation period |
| average_read_bytes | bigint | Average I/O read bytes of a statement across all DNs. | Average value of average_read_bytes of all statements with the same aggregation primary key in the aggregation period |
| min_write_bytes | bigint | Minimum I/O write bytes of a statement across all DNs. | Minimum value of min_write_bytes of all statements with the same aggregation primary key in the aggregation period |
| max_write_bytes | bigint | Maximum I/O write bytes of a statement across all DNs. | Maximum value of max_write_bytes of all statements with the same aggregation primary key in the aggregation period |
| average_write_bytes | bigint | Average I/O write bytes of a statement across all DNs. | Average value of average_write_bytes of all statements with the same aggregation primary key in the aggregation period |
| recv_pkg | bigint | Total number of communication packages received by a statement across all DNs. | Total value of recv_pkg of all statements with the same aggregation primary key in the aggregation period |
| send_pkg | bigint | Total number of communication packages sent by a statement across all DNs. | Total value of send_pkg of all statements with the same aggregation primary key in the aggregation period |
| recv_bytes | bigint | Total received data of the statement stream, in byte. | Total value of recv_bytes of all statements with the same aggregation primary key in the aggregation period |
| send_bytes | bigint | Total sent data of the statement stream, in byte. | Total value of send_bytes of all statements with the same aggregation primary key in the aggregation period |
| stmt_type | text | Query type corresponding to the statement. | stmt_type of the first statement with the same aggregation primary key in the aggregation period |
| except_info | text | Information about the exception rule triggered by the statement. | Left blank |
| unique_plan_id | bigint | ID of the normalized unique plan. | unique_plan_id of the first statement with the same aggregation primary key in the aggregation period |
| sql_hash | text | Normalized SQL hash. | Normalized SQL hash |
| plan_hash | text | Normalized plan hash. | Normalized plan hash |
| use_plan_baseline | text | Indicates whether the bound plan is used for executing the current statement. If it is used, the name of the plan_baseline column in pg_plan_baseline is displayed. | Use_plan_baseline of the first statement with the same aggregation primary key in the aggregation period |
| outline_name | text | Name of the outline used for the statement plan. | outline_name of the first statement with the same aggregation primary key in the aggregation period |
| loader_status | text | The JSON string for storing import and export service information is as follows.
| The JSON string for storing import and export service information is as follows.
|
| parse_time | bigint | Total parsing time before the statement is queued (including lexical and syntax parsing, optimization rewriting, and plan generation time), in milliseconds. This column is supported only by clusters of version 8.3.0.100 or later. | Average value of parse_time of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_hit_ratio | numeric(5,2) | Disk cache hit rate. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_hit_ratio of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_disk_read_size | bigint | Total size of data read from disk cache, in MB. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_disk_read_size of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_disk_write_size | bigint | Total size of data written to disk cache, in MB. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_disk_write_size of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_remote_read_size | bigint | Total size of data read remotely from OBS due to disk cache read failure, in MB. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_remote_read_size of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_remote_read_time | bigint | Total number of times data is read remotely from OBS due to disk cache read failure. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_remote_read_time of all statements with the same aggregation primary key in the aggregation period |
| vfs_scan_bytes | bigint | Total number of bytes scanned by the OBS virtual file system in response to upper-layer requests, in bytes. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of vfs_scan_bytes of all statements with the same aggregation primary key in the aggregation period |
| vfs_remote_read_bytes | bigint | Total number of bytes actually read from OBS by the OBS virtual file system, in bytes. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of vfs_remote_read_bytes of all statements with the same aggregation primary key in the aggregation period |
| preload_submit_time | bigint | Total time for submitting I/O requests in the prefetching process, in microseconds. This column only applies to OBS 3.0 tables with storage and compute decoupled. | Total value of preload_submit_time of all statements with the same aggregation primary key in the aggregation period |
| preload_wait_time | bigint | Total time for waiting for I/O requests in the prefetching process, in microseconds. This column only applies to OBS 3.0 tables with storage and compute decoupled. | Total value of preload_wait_time of all statements with the same aggregation primary key in the aggregation period |
| preload_wait_count | bigint | Total number of times that the prefetching process waits for I/O requests. This column only applies to OBS 3.0 tables with storage and compute decoupled. | Total value of preload_wait_count of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_load_time | bigint | Total time for reading from disk cache, in microseconds. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_load_time of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_conflict_count | bigint | Number of times a block in the disk cache produces a hash conflict. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_conflict_count of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_error_count | bigint | Number of disk cache read failures. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of disk_cache_error_count of all statements with the same aggregation primary key in the aggregation period |
| disk_cache_error_code | bigint | Error code for disk cache read failures. Multiple error codes may be generated. If the disk cache fails to be read, OBS remote read is initiated and cache blocks are rewritten. The error code types are as follows: This column only applies to OBS 3.0 tables and foreign tables.
| Left blank |
| obs_io_req_avg_rtt | bigint | Average Round Trip Time (RTT) for OBS I/O requests, in microseconds. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_avg_rtt of all statements with the same aggregation primary key in the aggregation period |
| obs_io_req_avg_latency | bigint | Average delay for OBS I/O requests, in microseconds. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_avg_latency of all statements with the same aggregation primary key in the aggregation period |
| obs_io_req_latency_gt_1s | bigint | Number of OBS I/O requests with a latency exceeding 1 second. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_latency_gt_1s of all statements with the same aggregation primary key in the aggregation period |
| obs_io_req_latency_gt_10s | bigint | Number of OBS I/O requests with a latency exceeding 10 seconds. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_latency_gt_10s of all statements with the same aggregation primary key in the aggregation period |
| obs_io_req_count | bigint | Total number of OBS I/O requests. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_counts of all statements with the same aggregation primary key in the aggregation period |
| obs_io_req_retry_count | bigint | Total number of retries for OBS I/O requests. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_retry_count of all statements with the same aggregation primary key in the aggregation period. |
| obs_io_req_rate_limit_count | bigint | Total number of times OBS I/O requests are flow-controlled. This column only applies to OBS 3.0 tables and foreign tables with storage and compute decoupled. | Total value of obs_io_req_rate_limit_count of all statements with the same aggregation primary key in the aggregation period. |
| gxid | xid | Statement transaction ID. This column is available only in clusters of version 9.1.1.100 or later. | GXID of the first statement with the same aggregation primary key in the aggregation period |
| inserted_rows | bigint | Number of rows inserted by the statement. This column is available only in clusters of version 9.1.1.100 or later. | Average value of inserted_rows of all statements with the same aggregate primary key in a rollup period. |
| deleted_rows | bigint | Number of rows deleted by the statement. This column is available only in clusters of version 9.1.1.100 or later. | Average value of deleted_rows of all statements with the same aggregation primary key in the aggregation period |
| updated_rows | bigint | Number of rows updated by the statement. This column is available only in clusters of version 9.1.1.100 or later. | Average value of updated_rows of all statements with the same aggregate primary key in a rollup period. |
| returned_rows | bigint | Number of rows returned by the statement. This column is available only in clusters of version 9.1.1.100 or later. | Average value of returned_rows of all statements with the same aggregation primary key within a rollup period. |
| obs_io_read_bytes | bigint | Average OBS I/O read bytes of a statement across all DNs. This column is available only in clusters of version 9.1.1.200 or later. | Average value of obs_io_read_bytes of all statements with the same aggregation primary key within a rollup period. |
| obs_io_write_bytes | bigint | Average OBS I/O write bytes of a statement across all DNs. This column is available only in clusters of version 9.1.1.200 or later. | Average value of obs_io_write_bytes of all statements with the same aggregation primary key in the aggregation period |
| calls | integer | The default value is 1. | Number of queries with the same aggregation primary key in the aggregation period |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
