DescribeOasAnomalySQLList|OceanBase Cloud| docs|Distributed Database

DescribeOasAnomalySQLList

Last Updated：2025-11-26 03:33:16 Updated

Overview

You can call this API to query the list of suspicious SQL statements.

API details

Constraints

The caller must have an AccessKey for accessing APIs of OceanBase Cloud. For information about how to obtain the AccessKey ID and AccessKey secret, see Manage AccessKeys.

Request path

/api/v2/instances/{instanceId}/tenants/{tenantId}/anomalySql

Request parameters

Parameter	Type	Required	Description	Example value
instanceId	String	Yes	The ID of the OceanBase cluster.	ob317v4uif****
tenantId	String	Yes	The ID of the tenant.	t4louaeei****
startTime	String	Yes	The start time of the time range for querying suspicious SQL statements. The value must be UTC time in the format of YYYY-MM-DDThh:mm:ssZ.	2023-04-12T04:38:38Z Note Special characters such as colon (:) must be escaped in the request parameters of time. For example, if 2023-04-12T05:38:38Z is placed in the URL request parameters, it needs to be escaped as 2023-04-12T04%3A38%3A38Z.
endTime	String	Yes	The end time of the time range for querying suspicious SQL statements. The value must be UTC time in the format of YYYY-MM-DDThh:mm:ssZ.	2023-04-12T05:38:38Z Note Special characters such as colon (:) must be escaped in the request parameters of time. For example, if 2023-04-12T05:38:38Z is placed in the URL request parameters, it needs to be escaped as 2023-04-12T04%3A38%3A38Z.
dbName	String	No	The name of the database.	test_db
searchKeyWord	String	No	The search keyword.	update
nodeIp	String	No	The IP address of the node.	i-bp18l4****str4uk03
filterCondition	String	No	All fields are referenced by using an at sign (@). For information about optional fields, see the Response parameters section in the Query performance indicators of an SQL statement topic.	@avgCpuTime > 20 and @executions > 100
sqlTextLength	Long	No	The maximum length of the returned SQL text.	65535

Response parameters

Parameter	Type	Description	Example value
	Object	The return result of the request.
Data	Object []	The list of information about suspicious SQL statements.
avgCpuTime	Double	The average CPU time of a suspicious SQL statement.	100.24
avgElapsedTime	Double	The average response time of a suspicious SQL statement.	100.28
avgGetPlanTime	Double	The average amount of time required for obtaining an execution plan for a suspicious SQL statement.	0
cpuTime	Double	The CPU time of the suspicious SQL statement.	100.23
dbName	String	The name of the database.	db_***
diagTypes	Array	The diagnostic type.
	String	The diagnostic type.	[ "TABLE_SCAN_INDEX_NOT_USED" ]
diagnosis	String	The diagnostic details.	An existing index is not used for the full-table scan
executions	Double	The total number of executions of the suspicious statement.	1
lastExecutedTime	Double	The last time when the suspicious SQL statement was executed.	2023-04-12T04:38:38Z
riskLevel	String	The risk level.	high
sqlId	String	The ID of the SQL statement.	8D6E84**0B8FB1823D199E2CA1**
sqlTextShort	String	The prefix of the SQL text.	delete /+ XXX PARALLEL(4) / from
suggestion	String	The suggestion on the suspicious statement.	review
sumElapsedTime	String	The total response time of the suspicious SQL statement.	11452126.36
userName	String	The username.	test_user
requestId	String	The ID of the request.	473469-AA6F-4D-B3DB-A***********
totalCount	Long	The total number of suspicious SQL statements.	9

Examples

Request example

curl  --request GET \
  --digest   -u 'AccessKeyID:AccessKeySecret' \
  -G -d 'startTime=2024-08-27T00%3A35%3A32Z' -d 'endTime=2024-08-27T06%3A05%3A32Z' -d 'pageSize=20'  -d'dbName=t_database' \
  --url https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/anomalySql
  -H 'X-Ob-Project-Id: <Project ID>'

Notice

Special characters contained in parameters must be escaped. For example, -d'@avgCpuTime>0' must be rewritten as -d'%40avgCpuTime%3E0'. Specifically, special characters in time request parameters, such as colon (:), must be escaped.
A project ID uniquely identifies a project. For information about how to obtain the project ID of an instance, see Manage projects.
Make sure that the project ID corresponding to the specified instanceId is consistent with the specified project ID.

Response example

JSON format

{
  "Data": [
    {
      "avgCpuTime": 100.24,
      "avgElapsedTime": 100.28,
      "avgGetPlanTime": 0,
      "cpuTime": 100.23,
      "dbName": "db_***",
      "diagTypes": [
        "[\n        \"TABLE_SCAN_INDEX_NOT_USED\"\n      ]"
      ],
      "diagnosis": "An existing index is not used for the full-table scan",
      "executions": 1,
      "lastExecutedTime": 0,
      "riskLevel": "high",
      "sqlId": "8D6E84****0B8FB1823D199E2CA1****",
      "sqlTextShort": "delete /*+ XXX PARALLEL(4) */ from",
      "suggestion": "review",
      "sumElapsedTime": "11452126.36",
      "userName": "test_user"
    }
  ],
  "RequestId": "473469**-AA6F-4D**-B3DB-A***********",
  "TotalCount": 9
}