Description
Query the SQL performance statistics of a tenant, which are aggregated over a specified time interval. The performance data includes information such as cluster, tenant, server, database, user, SQL_ID, response time, CPU time, execution count, and error count. You can filter specific SQL performance metrics by conditions such as Internal SQL or Not, Keywords in the SQL Text, and Advanced Search.
Note
This API is time-consuming for queries. If the response time is long, appropriately reduce the query frequency, the time range of the query, or the number of concurrent requests. You can also monitor the resource usage of OCP Server and MonitorDB, and adjust their 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 to be 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. This parameter takes effect only when both searchOp and searchVal are specified. |
| searchOp | String | No | GT | Operators for advanced search. Both searchAttr and searchVal must be specified for the operation 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 business data of the request. |
| ├─ contents | Array | The list of SQL performance metric data, as shown in 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
}
]
}
}
```
]
}
}