Overview
You can call this API to query the list of SQL statements that may have performance issues, as determined by the diagnostic system.
API details
Constraints
The caller must have an AccessKey for accessing APIs of multiple clouds. For information about how to obtain the AccessKey ID and AccessKey secret, see Manage AccessKeys.
Request path
GET /api/v2/instances/{instanceId}/tenants/{tenantId}/anomalySql
Request parameters
| Name | Type | Required | Description | Example value |
|---|---|---|---|---|
| instanceId | string | Yes | The ID of the OceanBase Cloud instance. | ob317v4uif**** |
| tenantId | string | Yes | The ID of the tenant. | t4louaeei**** |
| requestId | string | Yes | The ID of the request, which is used for tracing. | dc302d76-66b5-48d8-ab53-a035******** |
| startTime | string | Yes | The start time of the suspicious SQL query. The time must be in UTC and in the format of: YYYY-MM-DDThh:mm:ssZ. | 2023-04-12T04:38:38Z
NoteEscape special characters in the time parameter, such as colons ( : ). For example, 2023-04-12T05:38:38Z in a URL request parameter must be escaped to 2023-04-12T04%3A38%3A38Z. |
| endTime | string | Yes | The end time of the suspicious SQL query. The time must be in UTC and in the format of: YYYY-MM-DDThh:mm:ssZ. | 2023-04-12T05:38:38Z
NoteEscape special characters in the time parameter, such as colons ( : ). For example, 2023-04-12T05:38:38Z in a URL request parameter must be escaped to 2023-04-12T04%3A38%3A38Z. |
| dbName | string | No | The name of the database. | test_db |
| nodeIp | string | No | The IP address of the node. | i-bp18l4****str4uk03 |
| searchKeyWord | string | No | The keyword for the query. | update |
| sqlTextLength | integer | No | The maximum length of the SQL text. | 65535 |
| filterCondition | string | No | All fields are referenced by @. For information about the optional fields, see the columns returned by the Query SQL performance statistics API. | @avgCpuTime > 20 and @executions > 100 |
| pageNumber | integer | No | The page number. | 1 |
| pageSize | integer | No | The page size. | 10 |
| sortOrder | string | No | The sorting order. | desc |
| mergeDynamicSql | boolean | No | Whether to merge dynamic SQL statements. | true |
| sqlId | string | No | The unique identifier of the SQL statement. | sql_123456789 |
| dynamicSql | boolean | No | Whether the SQL statement is dynamic. | false |
| httpServletRequest | unknown | Yes | The HTTP servlet request parameter. | - |
Response parameters
| Name | Type | Description | Example value |
|---|---|---|---|
| object | The return result of the request. | ||
| Data | object [] | The list of suspicious SQL statements. | |
| avgCpuTime | double | The average CPU time of the suspicious SQL statement. | 100.24 |
| avgElapsedTime | double | The average response time of the suspicious SQL statement. | 100.28 |
| avgGetPlanTime | double | The average time taken to obtain the execution plan of the 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 types. | |
| string | The diagnostic types. | [ "TABLE_SCAN_INDEX_NOT_USED" ] | |
| diagnosis | string | The diagnostic details. | Full table scan with unused index |
| executions | double | The total number of executions of the suspicious SQL statement. | 1 |
| lastExecutedTime | double | The time when the suspicious SQL statement was last executed. | 2023-04-12T04:38:38Z |
| riskLevel | string | The risk level. | high |
| sqlId | string | The SQL ID. | 8D6E84****0B8FB1823D199E2CA1**** |
| sqlTextShort | string | The prefix of the SQL text. | delete /*+ XXX PARALLEL(4) */ from |
| suggestion | string | The suggestion for the suspicious SQL 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 items. | 9 |
Examples
Request example
curl --request GET \
--digest -u '<Your ak:sk>' \
-G -d 'requestId={requestId}' \
-d 'startTime={startTime}' \
-d 'endTime={endTime}' \
-d 'dbName={dbName}' \
-d 'nodeIp={nodeIp}' \
-d 'searchKeyWord={searchKeyWord}' \
-d 'sqlTextLength={sqlTextLength}' \
-d 'filterCondition={filterCondition}' \
-d 'pageNumber={pageNumber}' \
-d 'pageSize={pageSize}' \
-d 'sortOrder={sortOrder}' \
-d 'mergeDynamicSql={mergeDynamicSql}' \
-d 'sqlId={sqlId}' \
-d 'dynamicSql={dynamicSql}' \
--url 'https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/anomalySql' \
-H 'X-Ob-Project-Id: <Project ID>'
Notice
- If a parameter contains special characters, you must escape the special characters. For example, -d'@avgCpuTime>0' must be rewritten as -d'%40avgCpuTime%3E0'. In addition, you must escape the special characters in the time parameters, such as the colon ( : ).
- 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": "Full table scan without using index",
"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
}