Overview
You can call this API to query the list of slow SQL statements in a specified OceanBase cluster tenant. You can filter the SQL statements by time range, database, node, and SQL keyword. The API also returns the execution details and performance statistics of the 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
GET /api/v2/instances/{instanceId}/tenants/{tenantId}/slowSql
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 slow 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 colon (:). For example, 2023-04-12T05:38:38Z must be escaped to 2023-04-12T04%3A38%3A38Z when it is used as a URL request parameter. |
| endTime | string | Yes | The end time of the slow 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 colon (:). For example, 2023-04-12T05:38:38Z must be escaped to 2023-04-12T04%3A38%3A38Z when it is used as a URL request parameter. |
| 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 using the @ symbol. For optional fields, see the columns returned by the Query SQL performance statistics interface. | @avgCpuTime > 20 and @executions > 100 |
| mergeDynamicSql | boolean | No | Specifies whether to merge dynamic SQL statements. | true |
| sqlId | string | No | The unique identifier of the SQL statement. | sql_123456789 |
| dynamicSql | boolean | No | Specifies whether the SQL statement is dynamic. | false |
| httpServletRequest | unknown | Yes | The HTTP servlet request parameter. | N/A |
Response parameters
| Parameter | Type | Description | Example value |
|---|---|---|---|
| object | The return result of the request. | ||
| Data | object [] | The list of slow SQL information. | |
| executions | double | The total number of executions. | 1 |
| rpcCount | double | The number of RPC requests. | 0.0 |
| remotePlans | double | The number of remote executions. | 0 |
| missPlans | double | The number of failed execution plans. | 0 |
| maxElapsedTime | double | The maximum response time. | 13643.3 |
| totalWaitTime | double | The total waiting time, in milliseconds. | 10.966 |
| execPs | double | The average number of executions per second. | 0.31 |
| maxCpuTime | double | The maximum CPU time, in milliseconds. | 13641.9 |
| cpuPercentage | double | The CPU utilization. | 100.0 |
| userName | string | The username. | test_user |
| dbName | string | The name of the database. | test_db |
| retCode4012Count | long | The number of times result code 4012 is returned. | 0 |
| retCode4013Count | long | The number of times result code 4013 is returned. | 0 |
| retCode5001Count | long | The number of times result code 5001 is returned. | 0 |
| retCode5024Count | long | The number of times result code 5024 is returned. | 0 |
| retCode5167Count | long | The number of times result code 5167 is returned. | 0 |
| retCode5217Count | long | The number of times result code 5217 is returned. | 0 |
| retCode6002Count | long | The number of times result code 6002 is returned. | 0 |
| failPercentage | double | The error rate. | 0.0 |
| sumWaitTime | double | The total waiting time, in milliseconds. | 9421.73 |
| avgWaitCount | double | The average number of waits. | 0.0 |
| avgRpcCount | double | The average number of RPC requests. | 8.0 |
| localPlanPercentage | double | The percentage of local plans. | 0.0 |
| remotePlanPercentage | double | The percentage of remote plans. | 0.0 |
| distPlanPercentage | double | The percentage of distributed plans. | 100.0 |
| sumElapsedTime | double | The total response time, in milliseconds. | 11452126.36 |
| avgNetTime | double | The average network transmission time, in milliseconds. | 0.0 |
| avgExecutorRpcCount | double | The average number of executed RPC requests. | 0.0 |
| missPlanPercentage | double | The plan hit rate. | 0.0 |
| tableScanPercentage | double | The percentage of table scans. | 0.0 |
| strongConsistencyPercentage | double | The percentage of strong-consistency transactions. | 100.0 |
| weakConsistencyPercentage | double | The percentage of weak-consistency transactions. | 0.0 |
| maxAffectedRows | double | The maximum number of rows affected. | 10000.0 |
| maxReturnRows | double | The maximum number of rows returned. | 0.0 |
| maxWaitTime | double | The maximum waiting time, in milliseconds. | 3.4 |
| maxApplicationWaitTime | double | The maximum Application event waiting time, in milliseconds. | 0.0 |
| maxConcurrencyWaitTime | double | The maximum Concurrency event waiting time, in milliseconds. | 0.0 |
| maxUserIoWaitTime | double | The maximum UserIO event waiting time, in milliseconds. | 0.0 |
| maxDiskReads | double | The maximum number of physical reads. | 0.0 |
| avgExpectedWorkerCount | double | The average parallelism. | 3 |
| avgUsedWorkerCount | double | The average number of threads used by SQL. | 3 |
| sumLogicalReads | double | The total number of logical reads. | 0.0 |
| server | string | The server where the SQL is executed. | xxx.xxx.x.xxx:xxxx |
| serverIp | string | The IP address of the server where the SQL is executed. | i-bp1db1****8uemejio |
| serverPort | long | The port of the server where the SQL is executed. | 389 |
| sqlTextShort | string | The text of the SQL statement (the first 100 characters). | delete /*+ XXX PARALLEL(4) */ fro |
| sqlType | string | The type of the SQL statement. | select |
| sqlId | string | The SQL ID. | 8D6E84****0B8FB1823D199E2CA1**** |
| inner | boolean | Indicates whether the SQL statement is an internal SQL statement. | false |
| waitEvent | string | The longest waiting event. | none |
| avgAffectedRows | double | The average number of rows affected. | 9978.75 |
| avgReturnRows | double | The average number of rows returned. | 0.0 |
| avgPartitionCount | double | The average number of partitions accessed. | 1.0 |
| failCount | double | The number of errors. | 0 |
| avgWaitTime | double | The average waiting time, in milliseconds. | 1442.49 |
| avgElapsedTime | double | The average response time, in milliseconds. | 903.29 |
| avgCpuTime | double | The average CPU time, in milliseconds. | 1875.34 |
| avgNetWaitTime | double | The average network waiting time, in milliseconds. | 0.0 |
| avgQueueTime | double | The average queueing time, in milliseconds. | 0.01 |
| avgDecodeTime | double | The average syntax parsing time, in milliseconds. | 0.0 |
| avgGetPlanTime | double | The average plan generation time, in milliseconds. | 0.0 |
| avgExecuteTime | double | The average plan execution time, in milliseconds. | 1895.7 |
| avgApplicationWaitTime | double | The average Application event waiting time, in milliseconds. | 0.0 |
| avgConcurrencyWaitTime | double | The average concurrency event waiting time (in milliseconds) during the period. | 0.0 |
| avgUserIoWaitTime | double | The average user I/O event waiting time (in milliseconds) during the period. | 0.0 |
| avgScheduleTime | double | The average schedule event waiting time (in milliseconds) during the period. | 0.0 |
| avgRowCacheHit | double | The average number of row cache hits during the period. | 0.0 |
| avgBloomFilterCacheHit | double | The average number of bloom filter cache hits during the period. | 0.0 |
| avgBlockCacheHit | double | The average number of block cache hits during the period. | 0.0 |
| avgBlockIndexCacheHit | double | The average number of block index cache hits during the period. | 0.0 |
| avgDiskReads | double | The average number of physical reads during the period. | 0.0 |
| retryCount | double | The total number of retries during the period. | 0 |
| avgMemstoreReadRows | double | The number of rows read from the memory. | 0.0 |
| avgSsstoreReadRows | double | The number of rows read from the disk. | 0.0 |
| avgLogicalReads | double | The average number of logical reads for SQL during the period. | 0.0 |
Examples
Request example
curl --request GET \
--digest -u '<Your ak:sk>' \
-G -d 'requestId={requestId}' \
-d 'startTime={startTime}' \
-d 'endTime={endTime}' \
-d 'dbName={dbName}' \
--url 'https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/slowSql' \
-H 'X-Ob-Project-Id: <Project ID>'
Notice
- Special characters in parameters must be escaped. For example, -d'@avgCpuTime>0' must be rewritten as -d'%40avgCpuTime%3E0'. The colon (:) in time parameters must be escaped in a timely manner.
- Special characters in parameters must be escaped. For example, -d'@avgCpuTime>0' must be rewritten as -d'%40avgCpuTime%3E0'. The colon (:) in time parameters must be escaped in a timely manner.
- 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
{
"RequestId": "EE205C00-30E4-****-****-87E3A8A2AA0C",
"Data": [
{
"executions": 1,
"rpcCount": 0,
"remotePlans": 0,
"missPlans": 0,
"maxElapsedTime": 13643.3,
"totalWaitTime": 10.966,
"execPs": 0.31,
"maxCpuTime": 13641.9,
"cpuPercentage": 100,
"clientIp": "i-bp1db****38uemejio",
"userName": "test_user",
"dbName": "test_db",
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"failPercentage": 0,
"sumWaitTime": 9421.73,
"avgWaitCount": 0,
"avgRpcCount": 8,
"localPlanPercentage": 0,
"remotePlanPercentage": 0,
"distPlanPercentage": 100,
"sumElapsedTime": 11452126.36,
"avgNetTime": 0,
"avgExecutorRpcCount": 0,
"missPlanPercentage": 0,
"tableScanPercentage": 0,
"strongConsistencyPercentage": 100,
"weakConsistencyPercentage": 0,
"maxAffectedRows": 10000,
"maxReturnRows": 0,
"maxWaitTime": 3.4,
"maxApplicationWaitTime": 0,
"maxConcurrencyWaitTime": 0,
"maxUserIoWaitTime": 0,
"maxDiskReads": 0,
"avgExpectedWorkerCount": 3,
"avgUsedWorkerCount": 3,
"sumLogicalReads": 0,
"server": "xxx.xxx.x.xxx:xxxx",
"serverIp": "i-bp1db1****8uemejio",
"serverPort": 389,
"sqlTextShort": "delete /*+ XXX PARALLEL(4) */ fro",
"sqlType": "select ",
"sqlId": "8D6E84****0B8FB1823D199E2CA1****",
"inner": false,
"waitEvent": "none",
"avgAffectedRows": 9978.75,
"avgReturnRows": 0,
"avgPartitionCount": 1,
"failCount": 0,
"avgWaitTime": 1442.49,
"avgElapsedTime": 903.29,
"avgCpuTime": 1875.34,
"avgNetWaitTime": 0,
"avgQueueTime": 0.01,
"avgDecodeTime": 0,
"avgGetPlanTime": 0,
"avgExecuteTime": 1895.7,
"avgApplicationWaitTime": 0,
"avgConcurrencyWaitTime": 0,
"avgUserIoWaitTime": 0,
"avgScheduleTime": 0,
"avgRowCacheHit": 0,
"avgBloomFilterCacheHit": 0,
"avgBlockCacheHit": 0,
"avgBlockIndexCacheHit": 0,
"avgDiskReads": 0,
"retryCount": 0,
"avgMemstoreReadRows": 0,
"avgSsstoreReadRows": 0,
"avgLogicalReads": 0
}
]
}