Description
You can call this operation to query the performance indicator trend of an SQL statement within a specified time range. The performance trend comprises multiple sampling points, and each sampling point is included in the performance indicators of a specified timestamp. You can call this operation to query SQL performance changes with time. Note
If you specify a large time range, the returned sampling points may be approximate to 120.
Call description
Prerequisite
You have the Read-only permission on the specified tenant.
Request path
GET /api/v2/ob/clusters/{clusterId}/tenants/{tenantId}/sqls/{sqlId}/trends
Request parameters
| Parameter | Type | Required | Example value | Description |
|---|---|---|---|---|
| clusterId | Integer | Yes | 1 | The ID of the cluster. |
| tenantId | Integer | Yes | 1001 | The ID of the tenant. |
| sqlId | String | Yes | C9B3A63AFF8F2A408CCF3D5B0F36682D | The ID of the SQL statement. |
| startTime | Datetime | Yes | 2020-02-16T05:32:16+08:00 | The start time. |
| endTime | Datetime | Yes | 2020-02-16T07:32:16+08:00 | The end time. |
| serverId | Integer | No | 300 | Specifies the ID of the OBServer on which you want to query the performance indicators of an SQL statement. If it is not specified, the SQL performance indicators of all OBServers are queried. |
| dbName | String | No | oceanbase | Specifies the name of the database where you want to query the performance indicators of an SQL statement. If it is not specified, the SQL performance indicators of all databases are queried. |
Response parameters
- Basic data structure
| Parameter | Type | Description |
|---|---|---|
| data | Object | The requested data. |
| ├─ contents | Array | The array of SQL performance sampling data. For more information about its data structure, see the following table. |
| successful | Boolean | Indicates whether the request was successful. |
| timestamp | Datetime | The timestamp when the server completed the request. |
| duration | Integer | The time taken by the server to process the request, in milliseconds. |
| status | Integer | An 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 SQL performance indicators
| Parameter | Type | Description |
|---|---|---|
| timestamp | Datetime | The timestamp of the sampling. |
| executions | Integer | The total number of times the SQL statement was executed during its execution period. |
| execPs | Float | The average number of times the SQL statement was executed every second during its execution period. The value is rounded to two decimal places. |
| avgAffectedRows | Float | The average number of updated rows during the execution period of the SQL statement. The value is rounded to two decimal places. |
| avgReturnRows | Float | The average number of returned rows during the execution period of the SQL statement. The value is rounded to two decimal places. |
| avgPartitionCount | Float | The average number of accessed partitions during the execution period of the SQL statement. |
| failCount | Integer | The total number of errors occurred during the execution period of the SQL statement. |
| failPercentage | Float | The percentage of errors occurred during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places. |
| retCode4012Count | Integer | The total number of timeout errors occurred during the execution period of the SQL statement. |
| avgWaitTime | Float | The average wait time during the execution period of the SQL statement. |
| avgWaitCount | Float | The average wait times during the execution period of the SQL statement. |
| avgRpcCount | Float | The average RPC sending times during the execution period of the SQL statement. |
| localPlanPercentage | Float | The percentage of the local plans during the execution period of the SQL statement. |
| remotePlanPercentage | Float | The percentage of the remote plans during the execution period of the SQL statement. |
| disPlanPercentage | Float | The percentage of the distributed plans during the execution period of the SQL statement. |
| avgElapsedTime | Float | The average response time during the execution period of the SQL statement, in milliseconds. |
| maxElapsedTime | Float | The maximum response time during the execution period of the SQL statement, in milliseconds. |
| avgCpuTime | Float | The average CPU time during the execution period of the SQL statement, in milliseconds. |
| maxCpuTime | Float | The maximum CPU time during the execution period of the SQL statement, in milliseconds. |
| avgNetTime | Float | The average network transmission time during the execution period of the SQL statement, in milliseconds. |
| avgNetWaitTime | Float | The average time spent for the SQL statement to enter into a queue during its execution period, in milliseconds. |
| avgQueueTime | Float | The average queue time of the SQL statement during its execution period, in milliseconds. |
| avgDecodeTime | Float | The average syntax parsing time during the execution period of the SQL statement, in milliseconds. |
| avgGetPlanTime | Float | The average plan generation time during the execution period of the SQL statement, in milliseconds. |
| avgExecuteTime | Float | The average plan execution time during the execution period of the SQL statement, in milliseconds. |
| avgExecutorRpcCount | Float | The average number of RPCs during the execution period of the SQL statement. |
| missPlanPercentage | Float | The retrieval failure rate of the plans during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places. |
| avgApplicationTime | Float | The average Application wait time during the execution period of the SQL statement, in milliseconds. |
| avgConcurrencyWaitTime | Float | The average Concurrency wait time during the execution period of the SQL statement, in milliseconds. |
| avgUserIoWaitTime | Float | The average UserIO wait time during the execution period of the SQL statement, in milliseconds. |
| avgScheduleTime | Float | The average Schedule wait time during the execution period of the SQL statement, in milliseconds. |
| avgRowCacheHit | Float | The average RowCache hit times during the execution period of the SQL statement. |
| avgBloomFilterCacheHit | Float | The average BloomFilterCache hit times during the execution period of the SQL statement. |
| avgBlockCacheHit | Float | The average BlockCache hit times during the execution period of the SQL statement. |
| avgBlockIndexCacheHit | Float | The average BlockIndexCache hit times during the execution period of the SQL statement. |
| avgDiskReads | Float | The average disk read times during the execution period of the SQL statement. |
| retryCount | Integer | The total retry times during the execution period of the SQL statement. |
| tableScanPercentage | Float | The percentage of table scans during the execution period of the SQL statement. |
| strongConsistencyPercentage | Float | The percentage of strong consistency transactions during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places. |
| weakConsistencyPercentage | Float | The percentage of weak consistency transactions during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places. |
| avgMemstoreReadRows | Float | The average MemStore read rows during the execution period of the SQL statement. |
| avgSsstoreReadRows | Float | The average SsStore read rows during the execution period of the SQL statement. |
Examples
Sample request
Query the performance indicator trend of the SQL statement whose ID is C9B3A63AFF8F2A408CCF3D5B0F36682D based on the cluster ID 1 and tenant ID 1001, and set the time range to 2020-11-03T19:00:00+08:00 to 2020-11-03T20:00. Query only the SQL performance on the server with an ID of 2 and the database named oceanbase.
GET /api/v2/ob/clusters/1/tenants/1001/sqls/C9B3A63AFF8F2A408CCF3D5B0F36682D/trends?startTime=2020-11-03T19%3A00%3A00%2B08%3A00&endTime=2020-11-03T20%3A00%3A00%2B08%3A00&dbName=oceanbase&serverId=2
Sample response
{
"duration": 2134,
"server": "xxx.xxx.xxx.xxx",
"status": 200,
"successful": true,
"timestamp": "2020-11-03T20:03:16.783+08:00",
"traceId": "23d1fed3db5f4bef",
"data": {
"contents": [
{
"avgAffectedRows": 0.00,
"avgApplicationWaitTime": 0.00,
"avgBlockCacheHit": 0.19,
"avgBlockIndexCacheHit": 0.19,
"avgBloomFilterCacheHit": 0.00,
"avgConcurrencyWaitTime": 0.00,
"avgCpuTime": 0.75,
"avgDecodeTime": 0.00,
"avgDiskReads": 0.00,
"avgElapsedTime": 1.36,
"avgExecuteTime": 1.18,
"avgExecutorRpcCount": 0.00,
"avgGetPlanTime": 0.16,
"avgMemstoreReadRows": 0.98,
"avgNetTime": 0.00,
"avgNetWaitTime": 0.00,
"avgPartitionCount": 1.00,
"avgQueueTime": 0.01,
"avgReturnRows": 7.24,
"avgRowCacheHit": 0.00,
"avgRpcCount": 0.36,
"avgScheduleTime": 0.00,
"avgSsstoreReadRows": 2.40,
"avgUserIoWaitTime": 0.00,
"avgWaitCount": 0.36,
"avgWaitTime": 0.58,
"distPlanPercentage": 0.00,
"execPs": 14.60,
"executions": 438,
"failCount": 0,
"failPercentage": 0.00,
"localPlanPercentage": 0.37,
"maxCpuTime": 0.00,
"maxElapsedTime": 1.00,
"missPlanPercentage": 0.00,
"remotePlanPercentage": 0.63,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"strongConsistencyPercentage": 1.00,
"tableScanPercentage": 0.00,
"timestamp": "2020-11-03T19:00:00+08:00",
"weakConsistencyPercentage": 0.00
},
{
"avgAffectedRows": 0.00,
"avgApplicationWaitTime": 0.00,
"avgBlockCacheHit": 0.19,
"avgBlockIndexCacheHit": 0.19,
"avgBloomFilterCacheHit": 0.00,
"avgConcurrencyWaitTime": 0.00,
"avgCpuTime": 0.78,
"avgDecodeTime": 0.00,
"avgDiskReads": 0.00,
"avgElapsedTime": 1.37,
"avgExecuteTime": 1.19,
"avgExecutorRpcCount": 0.00,
"avgGetPlanTime": 0.16,
"avgMemstoreReadRows": 1.01,
"avgNetTime": 0.00,
"avgNetWaitTime": 0.00,
"avgPartitionCount": 1.00,
"avgQueueTime": 0.01,
"avgReturnRows": 7.33,
"avgRowCacheHit": 0.00,
"avgRpcCount": 0.36,
"avgScheduleTime": 0.00,
"avgSsstoreReadRows": 2.44,
"avgUserIoWaitTime": 0.00,
"avgWaitCount": 0.36,
"avgWaitTime": 0.57,
"distPlanPercentage": 0.00,
"execPs": 14.53,
"executions": 436,
"failCount": 0,
"failPercentage": 0.00,
"localPlanPercentage": 0.36,
"maxCpuTime": 0.00,
"maxElapsedTime": 0.00,
"missPlanPercentage": 0.00,
"remotePlanPercentage": 0.64,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"strongConsistencyPercentage": 1.00,
"tableScanPercentage": 0.00,
"timestamp": "2020-11-03T19:00:30+08:00",
"weakConsistencyPercentage": 0.00
}
]
}
}