Overview
You can call this API to query the slow SQL list.
API details
Constraints
The caller must have an AccessKey for accessing the APIs of OceanBase Database. 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 cluster. | ob317v4uif**** |
| tenantId | string | Yes | The ID of the tenant. | t4louaeei**** |
| startTime | string | Yes | The start time for querying the slow SQL parameters. This parameter supports only UTC time, and the format is: 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 for querying the slow SQL parameters. This parameter supports only UTC time, and the format is: 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 |
| searchKeyWord | string | No | The keyword for the query. | update |
| nodeIp | string | No | The IP address of the node. | i-bp18l4****str4uk03 |
| filterCondition | string | No | All fields are referenced by using @. For information about the optional fields, see the columns returned by the Query SQL performance statistics interface. | @avgCpuTime > 20 and @executions > 100 |
| sqlTextLength | long | No | The maximum length of the SQL text. | 65535 |
Response parameters
| Parameter | Type | Description | Example value |
|---|---|---|---|
| object | The return result. | ||
| Data | object [] | The list of slow SQL statements. | |
| executions | double | The total number of executions during the period. | 1 |
| rpcCount | double | The number of RPC calls during the period. | 0.0 |
| remotePlans | double | The number of remote executions during the period. | 0 |
| missPlans | double | The number of times that execution plans are not hit during the period. | 0 |
| maxElapsedTime | double | The maximum response time during the period. | 13643.3 |
| totalWaitTime | double | The total wait time during the period, in milliseconds. | 10.966 |
| execPs | double | The average number of executions per second during the period. | 0.31 |
| maxCpuTime | double | The maximum CPU time, in milliseconds. | 13641.9 |
| cpuPercentage | double | The CPU usage. | 100.0 |
| userName | string | The username. | test_user |
| dbName | string | The database name. | test_db |
| retCode4012Count | long | The number of times that result code 4012 is returned during the period. | 0 |
| retCode4013Count | long | The number of times that result code 4013 is returned during the period. | 0 |
| retCode5001Count | long | The number of times that result code 5001 is returned during the period. | 0 |
| retCode5024Count | long | The number of times that result code 5024 is returned during the period. | 0 |
| retCode5167Count | long | The number of times that result code 5167 is returned during the period. | 0 |
| retCode5217Count | long | The number of times that result code 5217 is returned during the period. | 0 |
| retCode6002Count | long | The number of times that result code 6002 is returned during the period. | 0 |
| failPercentage | double | The error rate during the period. | 0.0 |
| sumWaitTime | double | The total wait time during the period, in milliseconds. | 9421.73 |
| avgWaitCount | double | The average number of waits during the period. | 0.0 |
| avgRpcCount | double | The average number of RPC calls during the period. | 8.0 |
| localPlanPercentage | double | The percentage of local plans during the period. | 0.0 |
| remotePlanPercentage | double | The percentage of remote plans during the period. | 0.0 |
| distPlanPercentage | double | The percentage of distributed plans during the period. | 100.0 |
| sumElapsedTime | double | The total response time during the period, in milliseconds. | 11452126.36 |
| avgNetTime | double | The average network transmission time during the period, in milliseconds. | 0.0 |
| avgExecutorRpcCount | double | The average number of RPC requests executed during the period. | 0.0 |
| missPlanPercentage | double | The plan hit rate during the period. | 0.0 |
| tableScanPercentage | double | The percentage of table scans during the period. | 0.0 |
| strongConsistencyPercentage | double | The percentage of strong consistency transactions during the period. | 100.0 |
| weakConsistencyPercentage | double | The percentage of weak consistency transactions during the period. | 0.0 |
| maxAffectedRows | double | The maximum number of rows affected during the period. | 10000.0 |
| maxReturnRows | double | The maximum number of rows returned during the period. | 0.0 |
| maxWaitTime | double | The maximum wait time during the period, in milliseconds. | 3.4 |
| maxApplicationWaitTime | double | The maximum Application event wait time during the period, in milliseconds. | 0.0 |
| maxConcurrencyWaitTime | double | The maximum Concurrency event wait time during the period, in milliseconds. | 0.0 |
| maxUserIoWaitTime | double | The maximum UserIO event wait time during the period, in milliseconds. | 0.0 |
| maxDiskReads | double | The maximum number of physical reads during the period. | 0.0 |
| avgExpectedWorkerCount | double | The average parallelism during the period. | 3 |
| avgUsedWorkerCount | double | The average number of SQL threads used during the period. | 3 |
| sumLogicalReads | double | The total number of logical reads for SQL during the period. | 0.0 |
| server | string | The server where the SQL statement is executed. | i-bp1db1****8uemejio |
| serverIp | string | The IP address of the server where the SQL statement is executed. | i-bp1db1****8uemejio |
| serverPort | long | The port of the server where the SQL statement is executed. | 389 |
| sqlTextShort | string | The text of the SQL statement (first 100 characters). | delete /*+ XXX PARALLEL(4) */ fro |
| sqlType | string | The type of the SQL statement. | select |
| sqlId | string | The ID of the SQL statement. | 8D6E84****0B8FB1823D199E2CA1**** |
| inner | boolean | Indicates whether the SQL statement is an internal one. | false |
| waitEvent | string | The longest wait event during the period. | none |
| avgAffectedRows | double | The average number of rows updated during the period. | 9978.75 |
| avgReturnRows | double | The average number of rows returned during the period. | 0.0 |
| avgPartitionCount | double | The average number of partitions accessed during the period. | 1.0 |
| failCount | double | The number of errors. | 0 |
| avgWaitTime | double | The average wait time during the period (ms). | 1442.49 |
| avgElapsedTime | double | The average response time during the period (ms). | 903.29 |
| avgCpuTime | double | The average CPU time during the period (ms). | 1875.34 |
| avgNetWaitTime | double | The average network enqueue time during the period (ms). | 0.0 |
| avgQueueTime | double | The average queue time during the period (ms). | 0.01 |
| avgDecodeTime | double | The average syntax parsing time during the period (ms). | 0.0 |
| avgGetPlanTime | double | The average plan generation time during the period (ms). | 0.0 |
| avgExecuteTime | double | The average plan execution time during the period (ms). | 1895.7 |
| avgApplicationWaitTime | double | The average Application event wait time during the period (ms). | 0.0 |
| avgConcurrencyWaitTime | double | The average Concurrency event wait time during the period (ms). | 0.0 |
| avgUserIoWaitTime | double | The average UserIO event wait time during the period (ms). | 0.0 |
| avgScheduleTime | double | The average Schedule event wait time during the period (ms). | 0.0 |
| avgRowCacheHit | double | The average number of RowCache hits during the period. | 0.0 |
| avgBloomFilterCacheHit | double | The average number of BloomFilterCache hits during the period. | 0.0 |
| avgBlockCacheHit | double | The average number of BlockCache hits during the period. | 0.0 |
| avgBlockIndexCacheHit | double | The average number of BlockIndexCache 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 memory. | 0.0 |
| avgSsstoreReadRows | double | The number of rows read from disk. | 0.0 |
| avgLogicalReads | double | The average number of logical reads during the 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'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 in parameters must be escaped. For example, -d'@avgCpuTime>0' must be rewritten as -d'%40avgCpuTime%3E0'. In addition, special characters in time parameters, such as colon (:), must be escaped.
- Special characters in parameters must be escaped. For example, -d'@avgCpuTime>0' must be rewritten as -d'%40avgCpuTime%3E0'. In addition, special characters in time 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
}
]
}