Description
Query the trend of performance statistics for an SQL statement within a specified time interval. The performance trend consists of multiple sampling points, each containing the performance statistics at a specified timestamp. This API can be used to observe how SQL performance changes over time.
Note
When specifying a large time interval, the number of returned sampling points approaches 120.
The caller must have read permission for the specified tenant. Request path
GET /api/v2/ob/clusters/{clusterId}/tenants/{tenantId}/sqls/{sqlId}/trends
Request parameters
Response parameters
- Basic data structures
| Parameter | Type | Required | Example Value | Description |
| clusterId | Integer | Yes | 1 | Cluster ID |
| tenantId | Integer | Yes | 1001 | Tenant ID |
| sqlId | String | Yes | C9B3A63AFF8F2A408CCF3D5B0F36682D | SQL ID |
| startTime | Datetime | Yes | 2020-02-16T05:32:16+08:00 | Start Time |
| endTime | Datetime | Yes | 2020-02-16T07:32:16+08:00 | End Time |
| serverId | Integer | No | 300 | Query the performance metrics of SQL statements executed on a specified OceanBase server. If not specified, query the performance metrics of SQL statements executed on all OceanBase servers. |
| dbName | String | No | oceanbase | Query the performance metrics of SQL statements executed in a specified database. If not specified, query the performance metrics of SQL statements executed in all databases. |
- Data structures for SQL performance
| Parameter | Type | Description |
| data | Object | The requested data. | | ├─ contents | Array | Array of SQL performance sampling data. For the structure of SQL performance sampling data, see the table below. | | successful | Boolean | 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 encoding that complies with the HTTP Status specification. | | traceId | String | The trace ID of the request, used for troubleshooting. | | server | String | The address of the application service that responds to the request. | Request example | Parameter | Type | Description |
| timestamp | Datetime | Timestamp of sampling | | executions | Integer | Total Number of SQL Executions During the Period | | execPs | Float | The average number of SQL queries executed per second during the period, rounded to two decimal places. | | avgAffectedRows | Float | The average number of rows updated per SQL statement during the period, rounded to two decimal places. | | avgReturnRows | Float | Average number of rows returned by SQL queries during the period, rounded to two decimal places. | | avgPartitionCount | Float | Average Number of Accessed Partitions per SQL Statement During the Period | | failCount | Integer | Total Number of SQL Errors During the Period | | failPercentage | Float | The error percentage of SQL queries within the period. The return value is in the range [0, 1] and is rounded to four decimal places. | | retCode4012Count | Integer | Total Number of SQL Timeout Errors During the Period | | avgWaitTime | Float | Average Wait Time of SQL Queries Within the Period | | avgWaitCount | Float | Average Number of SQL Waitings During the Period | | avgRpcCount | Float | Average Number of RPCs Sent per SQL Statement During the Period | | localPlanPercentage | Float | Percentage of Local Plans for SQL Queries During the Period | | remotePlanPercentage | Float | Remote SQL Plan Percentage During the Period | | disPlanPercentage | Float | Percentage of Distributed SQL Plans During the Period | | avgElapsedTime | Float | Average SQL Response Time During the Period (ms) | | maxElapsedTime | Float | Maximum SQL Response Time Within the Period (ms) | | avgCpuTime | Float | Average CPU Time of SQL Queries in the Period (Millisecond) | | maxCpuTime | Float | Maximum CPU Time of SQL Queries within the Period (Millisecond) | | avgNetTime | Float | Average Network Transfer Time of SQL Queries within the Period (Millisecond) | | avgNetWaitTime | Float | Average Network Queue Time of SQL Queries Within the Period (ms) | | avgQueueTime | Float | Average Queue Time of SQL Queries within the Period (ms) | | avgDecodeTime | Float | Average Syntax Parsing Time of SQL Statements within the Period (Millisecond) | | avgGetPlanTime | Float | Average Plan Generation Time of SQL Queries within the Period (ms) | | avgExecuteTime | Float | Average Planned Execution Time of SQL Queries within the Period (Millisecond) | | avgExecutorRpcCount | Float | Average RPC Requests Executed per SQL Statement During the Period | | missPlanPercentage | Float | The planned miss rate of SQL queries during the period. The return value is in the range of [0, 1] and is rounded to four decimal places. | | avgApplicationTime | Float | Average Application Event Waiting Time of SQL Queries within the Period (Millisecond) | | avgConcurrencyWaitTime | Float | Average Concurrency Event Waiting Time of SQL Queries within the Period (Millisecond) | | avgUserIoWaitTime | Float | Average UserIO Event Waiting Time of SQL Queries in the Period (Millisecond) | | avgScheduleTime | Float | Average Wait Time of Schedule Events for SQL Queries within the Period (Millisecond) | | avgRowCacheHit | Float | Average RowCache Hits per SQL Statement During the Period | | avgBloomFilterCacheHit | Float | Average BloomFilterCache Hits of SQL Queries within the Period | | avgBlockCacheHit | Float | Average BlockCache Hits per SQL Statement During the Period | | avgBlockIndexCacheHit | Float | Average BlockIndexCache Hits per SQL Statement During the Period | | avgDiskReads | Float | Average Number of Disk Reads per Second for SQL Queries During the Period | | retryCount | Integer | Total Number of SQL Retry Attempts During the Period | | tableScanPercentage | Float | Percentage of Table Scans by SQL During the Period | | strongConsistencyPercentage | Float | The percentage of strong-consistency transactions for SQL queries within the specified period. The return value is in the range [0, 1] and is rounded to four decimal places. | | weakConsistencyPercentage | Float | The percentage of weak-consistency transactions for SQL queries within the period. The return value is in the range [0, 1] and is rounded to four decimal places. | | avgMemstoreReadRows | Float | Average Number of MemStore Reads per Row for SQL Queries During the Period | | avgSsstoreReadRows | Float | Average Number of SsStore Read Rows per Second for SQL Queries within the Period | | avgLogicalReads | Double | Average Logical Reads per SQL Statement | | dbName | String | Database Name | | missPlans | Integer | Number of Missed Plans During the Period | | remotePlans | Integer | Number of Remote Plans During the Period | | server | String | Server where SQL is Executed | | serverIp | String | IP address of the server where the SQL statement is executed | | sumCpuTimeMs | Double | Total CPU Consumption During the Period | | sumElapsedTime | Double | Total Response Time During Period (ms) | | sumLogicalReads | Integer | Total Logical Reads of SQL | | sumWaitTime | Double | Total Waiting Time During the Period (ms) | | userName | String | SQL User | | waitEvent | String | Maximum Waiting Events During the Period | Response example Query the performance trend of the SQL statement with the cluster ID 1, tenant ID 1001, and SQL ID C9B3A63AFF8F2A408CCF3D5B0F36682D. The time range is from 2020-11-03T19:00:00+08:00 to 2020-11-03T20:00. Query only the performance of this SQL statement on the server with ID 2 and in 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
Response example
{
"duration": 2134,
"server": "a83ad33525",
"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
}
]
}
}
```
]
}
}