Description
Query the SQL performance statistics of a tenant, which are aggregated over a specified time interval. The performance data includes metrics such as cluster, tenant, server, database, user, SQL_ID, response time, CPU time, execution count, and error count. You can filter the performance metrics of specific SQL statements by conditions such as Internal SQL or External SQL, keywords in the SQL text, or using advanced search.
Note
This API is time-consuming for queries. If the response time is long, appropriately reduce the query frequency, query time range, or number of concurrent requests. You can also monitor the resource usage of OCP Server and MonitorDB, and adjust the specifications as needed.
You must have read permission for the specified tenant. Request path
GET /api/v2/ob/clusters/{clusterId}/tenants/{tenantId}/topSql
Request parameters
Response parameters
- Basic data structure
| Parameter | Type | Required | Example Value | Description |
| clusterId | Integer | Yes | 1 | Cluster ID |
| tenantId | Integer | Yes | 1001 | Tenant 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 | SQL statements to be executed on the specified OceanBase server. If not specified, SQL statements are executed on all OceanBase servers. |
| inner | Boolean | No | false | Whether to include internal SQL statements. Internal SQL statements are those initiated by OceanBase Database. The default value is false, which means no internal SQL statements are included. |
| sqlText | String | No | hello | Keywords contained in the SQL statement. Keywords are case-insensitive. |
| searchAttr | String | No | executions | Metric name for advanced search. Both searchOp and seachVal must be provided for this parameter to take effect. |
| searchOp | String | No | GT | Operators for advanced search. Both searchAttr and searchVal must be specified for the operator to take effect. Valid values: |
| searchVal | String | No | 1543 | Value for advanced search. Both searchAttr and searchOp must be specified for this parameter to take effect. |
| ------------- | ---------- | ---------------------- |
| data | Object | The requested business data. |
| ├─ contents | Array | The list of SQL performance metric data, see the table below. |
| 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 | The HTTP status code. | | traceId | String | The trace ID of the request, used for troubleshooting. | | server | String | The address of the server that responded to the request. |
- Data structure of SQL performance metrics | Parameter | Type | Description | | sqlId | String | The ID of the SQL statement. |
| server | String | The address of the OceanBase server. |
| userName | String | The user who executes the SQL statement. | | sqlType | String | The type of the SQL statement. Valid values:
Note
The returned SQL text contains only the first 100 characters. To view the full SQL text, use the API for querying SQL text.
Request example Query the SQL performance for cluster ID 1 and tenant ID 1001. The time range is from 2020-11-03T19:00:00+08:00 to 2020-11-03T20:00. Specify the advanced search condition as: Number of executions greater than or equal to 500. Response example The actual number of performance data points returned is related to the database load and the query time interval. Only two performance data points are displayed here.
GET /api/v2/ob/clusters/1/tenants/1001/topSql?searchAttr=executions&searchOp=GE&searchVal=500&startTime=2020-11-03T19%3A00%3A00%2B08%3A00&endTime=2020-11-03T20%3A00%3A00%2B08%3A00
Response example
The actual number of performance data points returned is related to the database load and the query time interval. Only two performance data points are displayed here.
{
"duration": 1435,
"server": "a83ad33525",
"status": 200,
"successful": true,
"timestamp": "2020-11-04T11:49:03.802+08:00",
"traceId": "ead2abb032cc464b",
"data": {
"contents": [
{
"avgAffectedRows": 0.00,
"avgApplicationWaitTime": 0.00,
"avgBlockCacheHit": 0.00,
"avgBlockIndexCacheHit": 0.00,
"avgBloomFilterCacheHit": 0.00,
"avgConcurrencyWaitTime": 0.00,
"avgCpuTime": 0.09,
"avgDecodeTime": 0.00,
"avgDiskReads": 0.00,
"avgElapsedTime": 0.09,
"avgExecuteTime": 0.04,
"avgExecutorRpcCount": 0.00,
"avgGetPlanTime": 0.05,
"avgMemstoreReadRows": 0.00,
"avgNetTime": 0.00,
"avgNetWaitTime": 0.00,
"avgPartitionCount": 1.00,
"avgQueueTime": 0.00,
"avgReturnRows": 0.00,
"avgRowCacheHit": 0.00,
"avgRpcCount": 0.00,
"avgScheduleTime": 0.00,
"avgSsstoreReadRows": 0.00,
"avgUserIoWaitTime": 0.00,
"avgWaitCount": 0.00,
"avgWaitTime": 0.00,
"dbName": "bar",
"distPlanPercentage": 0.00,
"execPs": 2.11,
"executions": 7522,
"failCount": 0,
"failPercentage": 0.00,
"inner": true,
"localPlanPercentage": 1.00,
"maxCpuTime": 0.00,
"maxElapsedTime": 0.00,
"missPlanPercentage": 0.00,
"remotePlanPercentage": 0.00,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"server": "xxx.xxx.xxx.1:2882",
"sqlId": "89FF9AC900FA242F1DEF8B2211377F60",
"sqlTextShort": "DELETE FROM X WHERE ID = 3",
"sqlType": "DELETE",
"strongConsistencyPercentage": 1.00,
"tableScanPercentage": 0.00,
"userName": "test",
"waitEvent": "none",
"weakConsistencyPercentage": 0.00
},
{
"avgAffectedRows": 0.00,
"avgApplicationWaitTime": 0.00,
"avgBlockCacheHit": 1.00,
"avgBlockIndexCacheHit": 1.00,
"avgBloomFilterCacheHit": 0.00,
"avgConcurrencyWaitTime": 0.00,
"avgCpuTime": 0.39,
"avgDecodeTime": 0.00,
"avgDiskReads": 0.00,
"avgElapsedTime": 0.42,
"avgExecuteTime": 0.28,
"avgExecutorRpcCount": 0.00,
"avgGetPlanTime": 0.11,
"avgMemstoreReadRows": 1.00,
"avgNetTime": 0.00,
"avgNetWaitTime": 0.00,
"avgPartitionCount": 2.00,
"avgQueueTime": 0.02,
"avgReturnRows": 2.00,
"avgRowCacheHit": 0.00,
"avgRpcCount": 0.00,
"avgScheduleTime": 0.00,
"avgSsstoreReadRows": 1.00,
"avgUserIoWaitTime": 0.00,
"avgWaitCount": 0.00,
"avgWaitTime": 0.00,
"dbName": "oceanbase",
"distPlanPercentage": 0.00,
"execPs": 1.00,
"executions": 3568,
"failCount": 0,
"failPercentage": 0.00,
"inner": false,
"localPlanPercentage": 1.00,
"maxCpuTime": 0.00,
"maxElapsedTime": 0.00,
"missPlanPercentage": 0.00,
"remotePlanPercentage": 0.00,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"server": "xxx.xxx.xxx.2:2882",
"sqlId": "9CFB17EA7B3F88B92C6BD433D845963A",
"sqlTextShort": "select 1",
"sqlType": "SELECT",
"strongConsistencyPercentage": 0.00,
"tableScanPercentage": 0.00,
"userName": "foo",
"waitEvent": "none",
"weakConsistencyPercentage": 1.00
}
]
}
}
```
]
}
}