Overview
You can call this API to query the list of slow 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}/slowSql
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 slow SQL statements. The value must be UTC time in the format of YYYY-MM-DDThh:mm:ssZ. | 2023-04-12T04:38:38Z
NoteSpecial 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 slow SQL statements. The value must be UTC time in the format of YYYY-MM-DDThh:mm:ssZ. | 2023-04-12T05:38:38Z
NoteSpecial 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 slow SQL statements. | |
| executions | Double | The total number of executions within the specified period. | 1 |
| rpcCount | Double | The total number of RPCs within the specified period. | 0.0 |
| remotePlans | Double | The number of remote executions within the specified period. | 0 |
| missPlans | Double | The number of missed execution plans within the specified period. | 0 |
| maxElapsedTime | Double | The maximum response time within the specified period. | 13643.3 |
| totalWaitTime | Double | The total wait time within the specified period, in milliseconds. | 10.966 |
| execPs | Double | The average number of executions per second within the specified period. | 0.31 |
| maxCpuTime | Double | The maximum CPU time, in milliseconds. | 13641.9 |
| cpuPercentage | Double | The CPU utilization. | 100.0 |
| clientIp | String | The IP address of the client. | i-bp1db****38uemejio |
| userName | String | The username. | test_user |
| dbName | String | The name of the database. | test_db |
| retCode4012Count | Long | The number of times that the result code 4012 is returned within the specified period. | 0 |
| retCode4013Count | Long | The number of times that the result code 4013 is returned within the specified period. | 0 |
| retCode5001Count | Long | The number of times that the result code 5001 is returned within the specified period. | 0 |
| retCode5024Count | Long | The number of times that the result code 5024 is returned within the specified period. | 0 |
| retCode5167Count | Long | The number of times that the result code 5167 is returned within the specified period. | 0 |
| retCode5217Count | Long | The number of times that the result code 5217 is returned within the specified period. | 0 |
| retCode6002Count | Long | The number of times that the result code 6002 is returned within the specified period. | 0 |
| failPercentage | Double | The percentage of errors occurred within the specified period. | 0.0 |
| sumWaitTime | Double | The total wait time within the specified period, in milliseconds. | 9421.73 |
| avgWaitCount | Double | The average number of waits within the specified period. | 0.0 |
| avgRpcCount | Double | The average number of RPCs sent within the specified period. | 8.0 |
| localPlanPercentage | Double | The percentage of local plans executed within the specified period. | 0.0 |
| remotePlanPercentage | Double | The percentage of remote plans executed within the specified period. | 0.0 |
| distPlanPercentage | Double | The percentage of distributed plans executed within the specified period. | 100.0 |
| sumElapsedTime | Double | The total response time within the specified period, in milliseconds. | 11452126.36 |
| avgNetTime | Double | The average network transmission time within the specified period, in milliseconds. | 0.0 |
| avgExecutorRpcCount | Double | The average number of RPCs executed within the specified period. | 0.0 |
| missPlanPercentage | Double | The plan hit rate within the specified period. | 0.0 |
| tableScanPercentage | Double | The percentage of table scans within the specified period. | 0.0 |
| strongConsistencyPercentage | Double | The percentage of strong-consistency transactions within the specified period. | 100.0 |
| weakConsistencyPercentage | Double | The percentage of weak-consistency transactions within the specified period. | 0.0 |
| maxAffectedRows | Double | The maximum number of rows affected within the specified period. | 10000.0 |
| maxReturnRows | Double | The maximum number of rows returned within the specified period. | 0.0 |
| maxWaitTime | Double | The maximum wait time within the specified period, in milliseconds. | 3.4 |
| maxApplicationWaitTime | Double | The maximum amount of time spent on waiting for events of the Application class within the specified period, in milliseconds. | 0.0 |
| maxConcurrencyWaitTime | Double | The maximum amount of time spent on waiting for events of the Concurrency class within the specified period, in milliseconds. | 0.0 |
| maxUserIoWaitTime | Double | The maximum amount of time spent on waiting for events of the UserIO class within the specified period, in milliseconds. | 0.0 |
| maxDiskReads | Double | The maximum number of physical reads within the specified period. | 0.0 |
| avgExpectedWorkerCount | Double | The average degree of parallelism (DOP) within the specified period. | 3 |
| avgUsedWorkerCount | Double | The average number of threads used for an SQL statement within the specified period. | 3 |
| sumLogicalReads | Double | The total number of logical reads by the SQL statement within the specified period. | 0.0 |
| server | String | The server where the SQL statement was executed. | i-bp1db1****8uemejio |
| serverIp | String | The IP address of the server where the SQL statement was executed. | i-bp1db1****8uemejio |
| serverPort | Long | The port number of the server where the SQL statement was executed. | 389 |
| sqlTextShort | String | The foremost 100 characters of the SQL text. | delete /*+ XXX PARALLEL(4) */ fro |
| sqlType | String | The SQL type. | Select |
| sqlId | String | The ID of the SQL statement. | 8D6E84****0B8FB1823D199E2CA1**** |
| inner | Boolean | Indicates whether the SQL statement is an internal SQL statement. | false |
| waitEvent | String | The event with the longest wait time within the specified period. | none |
| avgAffectedRows | Double | The average number of rows updated within the specified period. | 9978.75 |
| avgReturnRows | Double | The average number of rows returned within the specified period. | 0.0 |
| avgPartitionCount | Double | The average number of partitions accessed within the specified period. | 1.0 |
| failCount | Double | The number of errors. | 0 |
| avgWaitTime | Double | The average amount of wait time within the specified period, in milliseconds. | 1442.49 |
| avgElapsedTime | Double | The average response time within the specified period, in milliseconds. | 903.29 |
| avgCpuTime | Double | The average CPU time within the specified period, in milliseconds. | 1875.34 |
| avgNetWaitTime | Double | The average network wait time within the specified period, in milliseconds. | 0.0 |
| avgQueueTime | Double | The average queuing time within the specified period, in milliseconds. | 0.01 |
| avgDecodeTime | Double | The average syntax parsing time within the specified period, in milliseconds. | 0.0 |
| avgGetPlanTime | Double | The average plan generation time within the specified period, in milliseconds. | 0.0 |
| avgExecuteTime | Double | The average plan execution time within the specified period, in milliseconds. | 1895.7 |
| avgApplicationWaitTime | Double | The average amount of time spent on an Application wait event within the specified period, in milliseconds. | 0.0 |
| avgConcurrencyWaitTime | Double | The average amount of time spent on a Concurrency wait event within the specified period, in milliseconds. | 0.0 |
| avgUserIoWaitTime | Double | The average amount of time spent on a UserIO wait event within the specified period, in milliseconds. | 0.0 |
| avgScheduleTime | Double | The average amount of time spent on a Schedule wait event within the specified period, in milliseconds. | 0.0 |
| avgRowCacheHit | Double | The average number of row cache hits within the specified period. | 0.0 |
| avgBloomFilterCacheHit | Double | The average number of Bloom filter cache hits within the specified period. | 0.0 |
| avgBlockCacheHit | Double | The average number of block cache hits within the specified period. | 0.0 |
| avgBlockIndexCacheHit | Double | The average number of block index cache hits within the specified period. | 0.0 |
| avgDiskReads | Double | The average number of physical reads within the specified period. | 0.0 |
| retryCount | Double | The total number of retries within the specified 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 by an SQL statement within the specified period. | 0.0 |
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}/slowSql
-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
{
"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": "i-bp1db1****8uemejio",
"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
}
]
}
