Help Center/ GaussDB/ API Reference/ APIs (Recommended)/ Top SQL/ Querying Top SQL Statements

Updated on 2025-10-20 GMT+08:00

View PDF

Querying Top SQL Statements

Function

This API is used to query top SQL statements by instance ID. Before calling this API:

Learn how to authenticate this API.
Obtain the required region and endpoint.

Constraints

This API is suitable only for nodes with CNs or DNs (primary or standby DNs).

Debugging

You can debug this API in API Explorer.

URI

POST /v3/{project_id}/instances/{instance_id}/top-sql-list

**Table 1** Parameter description
Parameter	Mandatory	Description
project_id	Yes	Definition Project ID of a tenant in a region. To obtain the value, see Obtaining a Project ID. Constraints N/A Range The value can contain 32 characters. Only letters and digits are allowed. Default Value N/A
instance_id	Yes	Definition Instance ID, which uniquely identifies an instance. Constraints N/A Range The value can contain 36 characters. Only letters and digits are allowed. Default Value N/A

Request Parameters

**Table 2** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	Definition User token. You can obtain the token by calling the IAM API used to obtain a user token. After the request is processed, the value of X-Subject-Token in the message header is the token value. Constraints N/A Range N/A Default Value N/A
X-Language	No	String	Definition Language. Constraints N/A Range zh-cn en-us Default Value en-us

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
instance_id	Yes	String	Definition Instance ID. Constraints N/A Range N/A Default Value N/A
node_ids	Yes	Array of strings	Definition IDs of selected instance nodes. Constraints N/A
start_time	Yes	Long	Definition Start time. Constraints The value is a 13-digit UNIX timestamp, in milliseconds. The time zone is UTC. Range N/A Default Value N/A
end_time	Yes	Long	Definition End time. Constraints The value is a 13-digit UNIX timestamp, in milliseconds. The time zone is UTC. Range N/A Default Value N/A
start_time_utc	No	String	Definition Start time. Constraints UTC time. Range N/A Default Value N/A
end_time_utc	No	String	Definition End time. Constraints UTC time. Range N/A Default Value N/A
support_system	No	Boolean	Definition Whether to display system users. Constraints None Range true: System users are displayed. false: System users are not displayed. Default Value N/A
sql_id	No	String	Definition ID of a normalized top SQL statement. Constraints N/A Range N/A Default Value N/A
db_name	No	String	Definition Name of the database where a top SQL statement is executed. Constraints This parameter is displayed only when the DB engine version is 8.200 or later. Range N/A Default Value N/A
user_name	No	String	Definition Name of the user who executed a top SQL statement. Constraints N/A Range N/A Default Value N/A
sql_text	No	String	Definition Text of top SQL statements. Constraints N/A Range N/A Default Value N/A
multi_queries	No	Array of MultiMergeCondition	Definition Query conditions for field aggregation. For details, see Table 4. Constraints Only full AND or full OR queries can be performed on the query field.

**Table 4** MultiMergeCondition
Parameter	Type	Description
name	String	Definition Name of the field to be queried. Constraints Only the character string query is supported. Range 1 to 128 characters, including letters (case-sensitive), digits, and underscores (_) Default Value N/A
condition	String	Definition Combined condition. Constraints N/A Range The value can only be and, or, AND or OR. Default Value N/A
values	Array of strings	Definition Set of multiple search criteria. The value is a list of 1 to 5 characters. Constraints N/A
is_fuzzy	Boolean	Definition Set of multiple search criteria. Constraints The value can only be true for fuzzy query. Range true: fuzzy search false: exact match Default Value true

Response Parameters

**Table 5** Response body parameters
Parameter	Type	Description
top_sql_infos	Array of objects	Definition Top SQL information list. For details, see Table 6.
total	Integer	Definition Total number of top SQL statements. Range N/A

**Table 6** TopSqlInfo
Parameter	Type	Description
sql_id	String	Definition ID of a normalized top SQL statement. Range N/A
user_name	String	Definition Name of the user who executed a top SQL statement. Range N/A
sql_text	String	Definition Text of top SQL statements. Range N/A
calls_percent	String	Definition Call frequency ratio of top SQL statements. Range 0–100
cpu_percent	String	Definition CPU overhead ratio of top SQL statements. Range 0–100
io_percent	String	Definition I/O overhead ratio of top SQL statements. Range 0–100
calls	String	Definition Number of calls of top SQL statements. Range ≥ 0
returned_rows	String	Definition Number of tuples returned by top SQL statements. Range ≥ 0
tuple_read	String	Definition Number of tuples read by top SQL statements. Range ≥ 0
avg_elapse_time	String	Definition Average execution time of top SQL statements. Unit: ms Range ≥ 0
total_elapse_time	String	Definition Total execution time of top SQL statements. Unit: ms Range ≥ 0
cpu_time	String	Definition CPU overhead of top SQL statements. Unit: ms Range N/A
io_time	String	Definition I/O overhead of top SQL statements. Unit: ms Range N/A
min_elapse_time	String	Definition Minimum execution time of top SQL statements. Unit: ms Range N/A
max_elapse_time	String	Definition Maximum execution time of top SQL statements. Unit: ms Range N/A
sql_hit_ratio	String	Definition Hit ratio of top SQL statements. Range ≥ 0
node_id	String	Definition ID of the node where top SQL statements are executed. Range N/A
node_name	String	Definition Name of the node where top SQL statements are executed. Range N/A
db_name	String	Definition Name of the database where top SQL statements are executed. (This parameter is displayed only when the DB engine version is 8.200 or later.) Range N/A

Example Request

Querying top SQL statements

POST https://gaussdb-opengauss.ap-southeast-1.myhuaweicloud.com/v3/0611f1bd8b00d5d32f17c017f15b599f/instances/3d39c18788b54a919bab633874c159dfin14/top-sql-liste
{
  "instance_id": "3d39c18788b54a919bab633874c159dfin14",
  "node_ids": [
    "ce86fdea77304bfb9469d0b75ea3943ano14"
  ],
  "start_time": 1745477837000,
  "end_time": 1745564237000,
  "start_time_utc": "2025-04-24T06:57:17+0000",
  "end_time_utc": "2025-04-25T06:57:17+0000",
  "user_name": "dbmind_server",
  "sql_text": "BEGIN",
  "sql_id": "1872435845",
  "db_name": "postgres",
  "support_system": false,
  "multi_queries": [
    {
      "name": "query",
      "condition": "AND",
      "is_fuzzy": true,
      "values": [
        "BEGIN"
      ]
    }
  ]
}

Example Response

Node information queried.

{
    "top_sql_infos": [
        {
            "sql_id": "1872435845",
            "user_name": "dbmind_server",
            "sql_text": "BEGIN",
            "calls_percent": "48.15",
            "cpu_percent": "20.16",
            "io_percent": "0.00",
            "calls": "117",
            "returned_rows": "0",
            "tuple_read": "0",
            "avg_elapse_time": "0.0520",
            "total_elapse_time": "6.0830",
            "cpu_time": "9.2660",
            "io_time": "0.0000",
            "min_elapse_time": "0.0210",
            "max_elapse_time": "0.1820",
            "sql_hit_ratio": "0",
            "node_id": "ce86fdea77304bfb9469d0b75ea3943ano14",
            "node_name": "gauss-6e34-gaussdbv5cn_0",
            "db_name": "postgres"
        }
],
    "total": 1
}