Querying Slow SQL Statements

Function

This API is used to query slow SQL statements based on node information. 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}/slow-sql-list

**Table 1** Parameter description
Parameter	Mandatory	Type	Description
project_id	Yes	String	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	String	Definition Instance ID, which uniquely identifies an instance. For details about how to obtain the instance ID, see Querying DB Instances. 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 a response is returned, the value of X-Subject-Token in the response header is the token. Constraints N/A Range N/A Default Value N/A
X-Language	No	String	Definition Language of the information returned by the API. Constraints N/A Range zh-cn: Chinese en-us: English Default Value en-us

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
start_time	Yes	Long	Definition Start date. 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 date. Constraints The value is a 13-digit UNIX timestamp, in milliseconds. The time zone is UTC. Range N/A Default Value N/A
instance_id	Yes	String	Definition Instance ID, which uniquely identifies an instance. For details about how to obtain the instance ID, see Querying DB Instances. Constraints N/A Range The value can contain 36 characters. Only letters and digits are allowed. Default Value N/A
threshold	Yes	Integer	Definition Slow SQL threshold, in seconds. Constraints N/A Range 1–99 Default Value N/A
node_ids	Yes	Array of strings	Definition Node ID. To obtain the value, see Querying the Components of a DB Instance. Constraints The value cannot be left blank.
sql_id	No	String	Definition ID of a slow SQL statement. You can filter slow SQL statements by SQL ID. This parameter uniquely identifies a slow SQL statement and is used to query information about the slow SQL statement. When you call this API for the first time and only specify the mandatory parameter, you can obtain the value of sql_id from the response parameters. Constraints N/A Range N/A Default Value N/A
multi_queries	No	Array of objects	Definition Query conditions for field aggregation. For details, see Table 4. Constraints Only all-AND or all-OR queries can be performed on the query field.

**Table 4** MultiMergeCondition
Parameter	Mandatory	Type	Description
name	Yes	String	Definition Name of the field to be queried. Constraints Only the character string query is supported. Range query: indicates a query. Default Value N/A
condition	Yes	String	Definition Combination condition. Logical combination mode between multiple filter criteria. When a query involves multiple filter criteria, this parameter specifies whether the relationship between the criteria is AND or OR. Constraints N/A Range and: AND or: OR AND: AND OR: OR Default Value N/A
values	Yes	Array of strings	Definition Set of multiple search criteria. Constraints The value is a list of 1 to 5 characters.
is_fuzzy	No	Boolean	Definition Whether to enable fuzzy query. 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
total	Integer	Definition Total number of slow SQL statements. Range N/A
slow_sql_infos	Array of objects	Definition Slow SQL statements. For details, see Table 6.

**Table 6** SlowSqlListRes
Parameter	Type	Description
db_name	String	Definition Database name. Range N/A
schema_name	String	Definition Schema name. Range N/A
sql_id	String	Definition SQL ID. Range N/A
user_name	String	Definition Username. Range N/A
node_id	String	Definition Node ID. Range N/A
node_name	String	Definition Node name. Range N/A
sql_text	String	Definition SQL template. Range N/A
sql	String	Definition Complete SQL statement after variable replacement. If sql_text does not contain variable values, sql returns an empty string. Range N/A
query_plan	String	Definition Execution plan. Range N/A
calls	Integer	Definition Number of executions. Range N/A
avg_exec_time	String	Definition Average execution duration of slow SQL statements, in seconds. Range N/A
avg_cpu_time	String	Definition CPU time, in seconds. Range N/A
avg_io_time	String	Definition I/O time, in seconds. Range N/A
avg_returned_rows	Integer	Definition Returned rows. Range N/A
avg_fetched_rows	Integer	Definition Fetched rows. Range N/A
buffer_hit_ratio	String	Definition Buffer hit rate. Range N/A
sql_hit_ratio	String	Definition SQL hit ratio. Range N/A

Example Request

Querying slow SQL statements

POST https://gaussdb-opengauss.ap-southeast-1.myhuaweicloud.com/v3/0611f1bd8b00d5d32f17c017f15b599f/instances/3d39c18788b54a919bab633874c159dfin14/slow-sql-list
{ 
  "instance_id" : "d04686c6baae4f65a742771186f47b7ain14", 
  "node_ids" : [ "88391813e1cb4e93899b210e880fdb1bno20" ], 
  "start_time" : 1686903754933, 
  "end_time" : 1686990154933, 
  "threshold" : 1,
  "multi_queries": [ { "name": "query", "condition": "AND", "is_fuzzy": true, "values": [ "select", "from table" ] } ]
}

Example Response

Slow SQL statements queried.

{
   "slow_sql_infos" : [ {
     "db_name": "db_tpcc",
     "schema_name": "db_tpcc",
     "sql_id" : "6789",
     "user_name" : "john_doe",
     "sql_text" : "SELECT * FROM large_table WHERE column1 = 'value';",
     "sql" : "",
     "query_plan" : "Seq Scan on large_table ...",
     "calls" : 5,
     "avg_exec_time" : "17.4015",
     "avg_cpu_time" : "2.6380",
     "avg_io_time" : "0.0000",
     "avg_returned_rows" : 1000,
     "avg_fetched_rows" : 5000,
     "buffer_hit_ratio" : "0.8",
     "sql_hit_ratio" : "0.005",
     "node_id" : "88391813e1cb4e93899b210e880fdb1bno20",
     "node_name" : "Node1"
        } ],
   "total" : 1
}