The Query Insight feature uses slow query logs and the system table hg_table_info that logs table statistics to quickly provide execution information about the current query. The execution information includes the resource consumption of the query process, the metadata of tables in the query, and the execution plan of the query. You can use the Query Insight feature to check whether a data definition language (DDL) conflict or a table lock occurs in the query. This helps you perform troubleshooting.
Prerequisites
You have logged on to a Hologres instance. For more information, see Log on to an instance.
Go to the Query Insight page
You can go to the Query Insight page from the navigation pane or by redirections.
Log on to the Hologres console.
In the top navigation bar, select a region from the drop-down list.
In the left-side navigation pane of the Hologres console, click Go to HoloWeb to go to the HoloWeb console.
Go to the Query Insight page.
Navigation pane
In the top navigation bar, click Diagnostics and Optimization. In the left-side navigation pane, choose Metadata Warehouse Analysis > Query Insight.
Redirections
In HoloWeb, you can be redirected to the Query Insight page from the SQL Editor or Historical Slow Queries page.
SQL Editor: If you execute an SQL statement that contains the Query ID parameter in the SQL editor of HoloWeb and the execution duration is greater than 1s, you are directly redirected to the Query Insight page.
Historical Slow Queries: In the query list, find the desired SQL statement and click the
icon in the Actions column to be redirected to the Query Insight page.
The Query Insight page provides four tabs: Query Metadata, Table Metadata, Plan Visualization, and Table Lock Analysis.
Query Metadata
Data on the Query Metadata tab is collected from slow query logs of Hologres. By default, only data manipulation language (DML) statements that consume more than 100 ms and all DDL statements are collected in slow query logs. The Query Metadata tab displays DDL statements that consume more than 1s. Specific permissions are required to query slow query logs. For more information, see Query and analyze slow query logs. On the Query Metadata tab, configure the Instance Name and Query ID parameters, and click Search to query the metadata.
Basic Information
Displays the instance ID, database, and instance version of the query.
Query Information
Displays the basic information about the query. The information is collected from slow query logs and includes information about the user that performs the query, the execution status, and the execution engine.
Resource Usage
Displays the resource consumption during the query. The information is collected from slow query logs and includes the scan functions and CPU consumption.
Other Information
Displays additional information about the query. The information is collected from slow query logs and includes the client IP address.
SQL
Displays the content of the query. You can format the query to display it in an intuitive and user-friendly manner. You can also copy the query content.
Query Detail
Displays parameter values in the query when you configure parameters to perform the query. You can also configure parameters in SQL statements.
Execution Plan
The execution plan of the query, which is specified by the Plan field in slow query logs. The system collects the execution plans of queries that consume more than 10s. You can also view execution plans in a visualized manner for further analysis and optimization of queries. For more information about execution plans, see Explain and Explain Analyze.
Statistics
Displays runtime information about the query. The information is specified by the Statistics field in slow query logs. The detailed consumption information of each operator is displayed, including the number of scanned rows and the duration consumed by each operator. You can further optimize the query based on the statistics.
Information of Table for Data Read and Write
Displays the tables from which data is read and to which data is written in the query. You can click the following items in the Actions column of a table based on your business requirements:
Table Metadata Information: You can click it to be redirected to the Table Metadata tab to query the metadata of the table. The metadata of the table is collected from the hg_table_info table. By default, the data is updated on the next day. Therefore, you cannot query the metadata on the day when the table is created.
Table Lock Analysis: You can click it to be redirected to the Table Lock Analysis page to check whether the current query acquires a lock. Only locks acquired by DML statements can be detected. For example, if an SQL query that uses a fixed plan slows down, you can go to the Table Lock Analysis tab to check whether DML statements that are executed by using Hologres Query Engine (HQE) are executed on the table. This slows down the query process.
DDL Conflict Analysis
Displays DDL statements that are executed on the tables involved in the query within 1 minute before and after the query is performed. If a DDL conflict occurs and the query fails, the following error message is reported:
Query is canceled.Error Message
Displays the error message, including the error causes, if the query fails. The Query Insight page in HoloWeb provides the SQL intelligent diagnosis feature, which returns new error causes and solutions to the error message. This helps you fix errors in SQL statements in an efficient manner. For more information about common SQL failures and solutions, see FAQ about Hologres SQL statements.
Table Metadata
Table metadata is collected from the system table hg_table_info of Hologres. The system collects and reports table statistics every morning and updates the system table on the next day. Therefore, you can query the table metadata of the current day only on the next day.
You can click Table Metadata Information in the Actions column of a table on the Query Metadata tab to query metadata of the table. You can also configure the Instance Name, Database, Schema, Table, and Collection Time parameters and click Search to query the metadata of the table.
Basic Information
Displays the table name, partition status, and partition information.
Table Metadata Information
Displays the table creation time, collection time, and storage size.
Table property information
Displays the indexes of the table, including the primary key and the distribution key.
Table Schema Information
Displays the schema of the table, including Field Name, Field Type, Nullable, and Default value.
Plan Visualization
On this subtab, you can view the execution plan that is specified by the Plan field in slow query logs in a visualized manner. The system collects only the execution plans of queries that consume more than 10s. For more information about execution plans, see Explain and Explain Analyze.
Table Lock Analysis
On this subtab, you can check whether the SQL statements in the query involve DML statements (INSERT, UPDATE, or DELETE) that are executed by using HQE. This way, you can check whether the execution of SDK SQL statements slows down due to locks. If an SDK or HQE query and an HQE DML query are performed on the same table at the same time, locks exist. We recommend that you do not perform the queries at the same time.
Use Query Insight to troubleshoot common issues
Error message: relation with OID xxx does not exist.
Troubleshooting method: In most cases, this error message is reported because operations such as TRUNCATE or DROP are performed on tables involved in the query and the table object identifiers (OIDs) change. In this case, you can use the Query Insight feature to locate the ID of the query for which this error message is reported and check whether a DDL statement acquires a lock when the query is performed.
Error message: query is canceled.
Troubleshooting method: This error message is reported because DDL operations such as TRUNCATE or DROP are performed on the tables when the query is performed on the tables. In this case, you can go to the Query Insight page and specify the Query ID parameter to search for information about the query. In the DDL Conflict Analysis section of the Query Metadata tab, check DDL operations on the tables.
Error: SDK SQL statements have high latency.
Troubleshooting method: If an SDK SQL statement has high latency, such as 18s, you can use the Query Insight feature to check whether an SQL statement that is executed by using HQE acquires a table lock when the query is performed. In the Information of Table for Data Read and Write section, find the desired table and click Table Lock Analysis in the Actions column.
DML statements that are executed by using HQE when the query is performed are displayed. DML statements that are executed by using HQE acquire table locks. As a result, SDK SQL statements in the query need to wait for the locks to be released, and the execution duration increases. If no HQE-based DML statements exist, the table is not locked. In this case, perform troubleshooting by following the instructions in Optimize data write or update performance.