Description
You can call this operation to query the list of slow SQL statements.
Call description
Prerequisites
You have the Read-only permission on the specified tenant.
Request path
GET /api/v2/ob/clusters/{clusterId}/tenants/{tenantId}/slowSql
Request parameters
| Parameter | Type | Required | Example value | Description |
|---|---|---|---|---|
| clusterId | Integer | Yes | 1 | The ID of the cluster. |
| tenantId | Integer | Yes | 10001 | The ID of the tenant. |
| startTime | Datetime | Yes | 2023-04-12T04:38:38Z | The start time of the time range for querying the execution history of the slow SQL statement. The value must be UTC time in the YYYY-MM-DDThh:mm:ssZ format. |
| endTime | Datetime | Yes | 2023-04-12T05:38:38Z | The end time of the time range for querying the execution history of the slow SQL statement. The value must be UTC time in the YYYY-MM-DDThh:mm:ssZ format. |
| serverId | Integer | No | 300 | The ID of the OBServer node on which you want to query the performance metrics. If it is not specified, the system queries the performance metrics of the SQL execution plan on all OBServer nodes. |
| inner | Boolean | No | false | Indicates whether the SQL statement is an internal SQL statement. |
| sqlText | String | No | hello | The keyword in the SQL statement, which is case-insensitive. |
| filterExpression | String | No | @avgCpuTime > 20 and @executions > 100 | 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. |
| limit | Integer | No | 3 | The maximum number of slow SQL statements returned. |
| sqlTextLength | Integer | No | 65535 | The maximum length of the returned SQL text. |
Response parameters
Basic data structure
Parameter Type Description data Object The requested data. ├─ contents Array The array of information about slow SQL statements. For more information, see Data structure of a slow SQL statement. successful Boolean Indicates whether the request was successful. timestamp Datetime The timestamp when the server completed the request. status Integer The HTTP status code. traceId String The trace ID of the request. This trace ID is used for troubleshooting. server String The address of the application server that responded to the request. Data structure of a slow SQL statement
Parameter Type Description data Object The returned data. avgAffectedRows Double The average number of rows affected. avgApplicationWaitTime Double The average amount of time spent on an application wait event, in milliseconds. avgBlockCacheHit Double The average number of block cache hits. avgBlockIndexCacheHit Double The average number of block index cache hits. avgBloomFilterCacheHit Double The average number of Bloom filter cache hits. avgConcurrencyWaitTime Double The average amount of time spent on a concurrency wait event, in milliseconds. avgCpuTime Double The average CPU time, in milliseconds. avgDecodeTime Double The average amount of time spent in syntax parsing, in milliseconds. avgDiskReads Double The average number of disk reads. avgElapsedTime Double The average response time, in milliseconds. avgExecuteTime Double The average execution time of the SQL statement, in milliseconds. avgExecutorRpcCount Double The number of remote executions of the SQL statement, which corresponds to v$sl_audit.executor_rpc.avgGetPlanTime Double The average amount of time spent in generating an execution plan, in milliseconds. avgLogicalReads Double The average number of logical reads. avgMemstoreReadRows Double The average number of rows read from the MemStore. avgNetTime Double The average network I/O time of the SQL statement, in milliseconds. avgNetWaitTime Double The average network wait time of the SQL statement, in milliseconds. avgPartitionCount Double The average number of partitions accessed. avgQueueTime Double The average queuing time, in milliseconds. avgReturnRows Double The average number of rows returned. avgRowCacheHit Double The average number of row cache hits. avgRpcCount Double The average number of remote procedure calls (RPCs) executed by the SQL statement. avgScheduleTime Double The average scheduling time, in milliseconds. avgSsstoreReadRows Double The average number of rows read from the SSTable. avgUserIoWaitTime Double The average user I/O time, in milliseconds. avgWaitCount Double The average number of waits of the SQL statement. avgWaitTime Double The average wait time, in milliseconds. dbName String The name of the database. distPlanPercentage Double The percentage of distributed execution plans used for the SQL statement. execPs Double The average number of executions per second within the specified period. executions Double The total number of executions within the specified period. failCount Double The number of execution errors. failPercentage Double The execution error rate of the SQL statement. inner Boolean Indicates whether the SQL statement is an internal SQL statement. lastFailCode Long The error code of the last execution error. lastFailTimestamp Datetime The timestamp of the last execution error. localPlanPercentage Double The percentage of local execution plans used for the SQL statement. maxAffectedRows Double The maximum number of rows affected by the SQL statement. maxApplicationWaitTime Double The maximum amount of time spent on an application wait event, in milliseconds. maxConcurrencyWaitTime Double The maximum amount of time spent on a concurrency wait event, in milliseconds. maxCpuTime Double The maximum CPU time. maxDiskReads Double The maximum number of disk reads. maxElapsedTime Double The maximum response time of the SQL statement. maxReturnRows Double The maximum number of rows returned by the SQL statement. maxUserIoWaitTime Double The maximum amount of time spent on a user I/O wait event, in milliseconds. missPlanPercentage Double The percentage of execution plan misses of the SQL statement. obDbId Long The ID of the OceanBase cluster. obTenantId Long ID of the OceanBase Database tenant. remotePlanPercentage Double The percentage of remote execution plans used for the SQL statement. retCode4012Count Long The number of occurrences of the error code 4012. retCode4013Count Long The number of occurrences of the error code 4013. retCode5001Count Long The number of occurrences of the error code 5001. retCode5024Count Long The number of occurrences of the error code 5024. retCode5167Count Long The number of occurrences of the error code 5167. retCode5217Count Long The number of occurrences of the error code 5217. retCode6002Count Long The number of occurrences of the error code 6002. retryCount Double The number of retries. server String The address of the application server that responded to the request. serverIp String The IP address of the OBServer node. sqlId String The ID of the SQL statement. sqlTextShort String The foremost 100 characters of the SQL text. sqlType String The type of the SQL statement. strongConsistencyPercentage Double The percentage of strong-consistency reads of the SQL statement. sumCpuTimeMs Double The total CPU time, in milliseconds. sumElapsedTime Double The total response time, in milliseconds. sumLogicalReads Double The total number of logical reads. sumWaitTime Double The wait time of the longest wait event, in milliseconds. tableScanPercentage Double The percentage of full-table scans. tenantId Integer The ID of the tenant. tenantName String The name of the tenant. userName String The username. waitEvent String The longest wait event. weakConsistencyPercentage Double The percentage of weak-consistency reads of the SQL statement.
Examples
Sample request
Query the slow SQL statements for the tenant with the ID 1196 in the cluster with the ID 250 during the period from 2024-07-28T00:00:00+08:00 to 2024-07-29T00:00:00+08:00.
GET /api/v2/ob/clusters/250/tenants/1196/slowSql?startTime=2024-07-28T00%3A00%3A00%2B08%3A00&endTime=2024-07-29T00%3A00%3A00%2B08%3A00
Sample response
{
"data": {
"contents": [
{
"avgAffectedRows": 0,
"avgApplicationWaitTime": 0,
"avgBlockCacheHit": 0,
"avgBlockIndexCacheHit": 0,
"avgBloomFilterCacheHit": 0,
"avgConcurrencyWaitTime": 0,
"avgCpuTime": 108.49,
"avgDecodeTime": 0,
"avgDiskReads": 0,
"avgElapsedTime": 108.49,
"avgExecuteTime": 108.27,
"avgExecutorRpcCount": 0,
"avgGetPlanTime": 0.22,
"avgLogicalReads": 0,
"avgMemstoreReadRows": 0,
"avgNetTime": 0,
"avgNetWaitTime": 0,
"avgPartitionCount": 0,
"avgQueueTime": 0,
"avgReturnRows": 0,
"avgRowCacheHit": 0,
"avgRpcCount": 0,
"avgScheduleTime": 0,
"avgSsstoreReadRows": 0,
"avgUserIoWaitTime": 0,
"avgWaitCount": 0,
"avgWaitTime": 0,
"dbName": "oceanbase",
"distPlanPercentage": 0,
"execPs": 0.02,
"executions": 1,
"failCount": 1,
"failPercentage": 100,
"inner": false,
"lastFailCode": 0,
"lastFailTimestamp": 0,
"localPlanPercentage": 0,
"maxAffectedRows": 0,
"maxApplicationWaitTime": 0,
"maxConcurrencyWaitTime": 0,
"maxCpuTime": 108.49,
"maxDiskReads": 0,
"maxElapsedTime": 108.49,
"maxReturnRows": 0,
"maxUserIoWaitTime": 0,
"missPlanPercentage": 100,
"obDbId": 201001,
"obTenantId": 1010,
"remotePlanPercentage": 0,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"server": "xxx.xxx.xxx.xxx:2882",
"serverIp": "xxx.xxx.xxx.xxx",
"sqlId": "7FA0ED8C8C94A4F26548FCDDA2368C21",
"sqlTextShort": "CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();",
"sqlType": "OTHER",
"strongConsistencyPercentage": 0,
"sumCpuTimeMs": 108.49,
"sumElapsedTime": 108.49,
"sumLogicalReads": 0,
"sumWaitTime": 0,
"tableScanPercentage": 0,
"tenantId": 1196,
"tenantName": "ocp_admin_pr",
"userName": "root",
"waitEvent": "none",
"weakConsistencyPercentage": 0
},
{
"avgAffectedRows": 0,
"avgApplicationWaitTime": 0,
"avgBlockCacheHit": 0,
"avgBlockIndexCacheHit": 0,
"avgBloomFilterCacheHit": 0,
"avgConcurrencyWaitTime": 0,
"avgCpuTime": 105.02,
"avgDecodeTime": 0,
"avgDiskReads": 0,
"avgElapsedTime": 105.02,
"avgExecuteTime": 99.28,
"avgExecutorRpcCount": 0,
"avgGetPlanTime": 5.74,
"avgLogicalReads": 0,
"avgMemstoreReadRows": 0,
"avgNetTime": 0,
"avgNetWaitTime": 0,
"avgPartitionCount": 2,
"avgQueueTime": 0,
"avgReturnRows": 1,
"avgRowCacheHit": 0,
"avgRpcCount": 0,
"avgScheduleTime": 0,
"avgSsstoreReadRows": 0,
"avgUserIoWaitTime": 0,
"avgWaitCount": 0,
"avgWaitTime": 0,
"dbName": "oceanbase",
"distPlanPercentage": 0,
"execPs": 0.02,
"executions": 1,
"failCount": 0,
"failPercentage": 0,
"inner": false,
"lastFailCode": 0,
"lastFailTimestamp": 0,
"localPlanPercentage": 100,
"maxAffectedRows": 0,
"maxApplicationWaitTime": 0,
"maxConcurrencyWaitTime": 0,
"maxCpuTime": 105.02,
"maxDiskReads": 0,
"maxElapsedTime": 105.02,
"maxReturnRows": 1,
"maxUserIoWaitTime": 0,
"missPlanPercentage": 100,
"obDbId": 201001,
"obTenantId": 1010,
"remotePlanPercentage": 0,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"server": "xxx.xxx.xxx.xxx:2882",
"serverIp": "xxx.xxx.xxx.xxx",
"sqlId": "8B5B4B4CC550A9251AE501E633E90D95",
"sqlTextShort": "SELECT /*+ workload_repository_snapshot query_time",
"sqlType": "SELECT",
"strongConsistencyPercentage": 100,
"sumCpuTimeMs": 105.02,
"sumElapsedTime": 105.02,
"sumLogicalReads": 0,
"sumWaitTime": 0,
"tableScanPercentage": 100,
"tenantId": 1196,
"tenantName": "ocp_admin_pr",
"userName": "root",
"waitEvent": "none",
"weakConsistencyPercentage": 0
}
]
},
"duration": 14,
"server": "f00e301d58",
"status": 200,
"successful": true,
"timestamp": "2024-07-29T10:50:22.176+08:00",
"traceId": "1ab38f7744b4a012"
}