Query the performance indicator trend of an SQL statement|V4.3.6| docs|Distributed Database

Query the performance indicator trend of an SQL statement

Last Updated：2025-09-08 08:15:43 Updated

Function Description

Queries the performance statistics of a specific SQL statement within a specified time interval. The performance statistics are represented by multiple sampling points, with each sampling point containing the performance statistics at a specific timestamp. This API can be used to observe how the SQL performance changes over time.

Note

If a large time interval is specified, the number of sampling points returned will approach 120.

Call instructions

Interface constraints

The caller must have the read permission for 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	The performance metrics of the SQL statement executed on the specified OceanBase server. If this parameter is not specified, the performance metrics of the SQL statement executed on all OceanBase servers are queried.
dbName	String	No	oceanbase	The performance metrics of the SQL statement executed on the specified database. If this parameter is not specified, the performance metrics of the SQL statement executed on all databases are queried.

Return result

Basic data structure

Parameter	Type	Description
data	Object	The request data.
├─ contents	Array	An array of SQL performance sampling data. The structure of an SQL performance sampling data item is described in the following table.
successful	Boolean	Specifies whether the request is successful.
timestamp	Datetime	The timestamp when the server completes the request.
duration	Integer	The time taken by the server to process the request (in milliseconds).
status	Integer	The HTTP status code that conforms to the HTTP status specification.
traceId	String	The trace ID of the request, which is used to troubleshoot problems.
server	String	The address of the application service that responds to the request.

Structure of SQL performance data

Parameter	Type	Description
timestamp	Datetime	The timestamp when the sample is taken.
executions	Integer	The total number of SQL executions during the period.
execPs	Float	The average number of SQL executions per second during the period, with two decimal places.
avgAffectedRows	Float	The average number of rows updated by the SQL statement during the period, with two decimal places.
avgReturnRows	Float	The average number of rows returned by the SQL statement during the period, with two decimal places.
avgPartitionCount	Float	The average number of partitions accessed by the SQL statement during the period.
failCount	Integer	The total number of SQL errors during the period.
failPercentage	Float	The error percentage of the SQL statement during the period. The return value is in the [0, 1] range, with four decimal places.
retCode4012Count	Integer	The total number of SQL statement timeouts during the period.
avgWaitTime	Float	The average wait time of the SQL statement during the period.
avgWaitCount	Float	The average number of waits of the SQL statement during the period.
avgRpcCount	Float	The average number of RPCs sent by the SQL statement during the period.
localPlanPercentage	Float	The percentage of local plans of the SQL statement during the period.
remotePlanPercentage	Float	The percentage of remote plans of the SQL statement during the period.
disPlanPercentage	Float	The percentage of distributed plans of the SQL statement during the period.
avgElapsedTime	Float	The average response time of the SQL statement during the period (in milliseconds).
maxElapsedTime	Float	The maximum response time of the SQL statement during the period (in milliseconds).
avgCpuTime	Float	The average CPU time of the SQL statement during the period (in milliseconds).
maxCpuTime	Float	The maximum CPU time of the SQL statement during the period (in milliseconds).
avgNetTime	Float	The average network transfer time of the SQL statement during the period (in milliseconds).
avgNetWaitTime	Float	The average network enqueue time of the SQL statement during the period (in milliseconds).
avgQueueTime	Float	The average queue time of the SQL statement during the period (in milliseconds).
avgDecodeTime	Float	The average syntax parsing time of the SQL statement during the period (in milliseconds).
avgGetPlanTime	Float	The average plan generation time of the SQL statement during the period (in milliseconds).
avgExecuteTime	Float	The average plan execution time of the SQL statement during the period (in milliseconds).
avgExecutorRpcCount	Float	The average number of execution RPC requests of the SQL statement during the period.
missPlanPercentage	Float	The plan miss rate of the SQL statement during the period. The return value is in the [0, 1] range, with four decimal places.
avgApplicationTime	Float	The average Application event wait time of the SQL statement during the period (in milliseconds).
avgConcurrencyWaitTime	Float	The average Concurrency event wait time of the SQL statement during the period (in milliseconds).
avgUserIoWaitTime	Float	The average UserIO event wait time of the SQL statement during the period (in milliseconds).
avgScheduleTime	Float	The average Schedule event wait time of the SQL statement during the period (in milliseconds).
avgRowCacheHit	Float	The average RowCache hit count of the SQL statement during the period.
avgBloomFilterCacheHit	Float	The average BloomFilterCache hit count of the SQL statement during the period.
avgBlockCacheHit	Float	The average BlockCache hit count of the SQL statement during the period.
avgBlockIndexCacheHit	Float	The average BlockIndexCache hit count of the SQL statement during the period.
avgDiskReads	Float	The average number of disk reads of the SQL statement during the period.
retryCount	Integer	The total number of SQL statement retries during the period.
tableScanPercentage	Float	The percentage of table scans of the SQL statement during the period.
strongConsistencyPercentage	Float	The percentage of strong-consistency transactions of the SQL statement during the period. The return value is in the [0, 1] range, with four decimal places.
weakConsistencyPercentage	Float	The percentage of weak-consistency transactions of the SQL statement during the period. The return value is in the [0, 1] range, with four decimal places.
avgMemstoreReadRows	Float	The average number of MemStore reads of the SQL statement during the period.
avgSsstoreReadRows	Float	The average number of SsStore reads of the SQL statement during the period.
avgLogicalReads	Double	The average number of logical reads of the SQL statement.
dbName	String	The name of the database.
missPlans	Integer	The number of plan misses during the period.
remotePlans	Integer	The number of remote plans during the period.
server	String	The server on which the SQL statement is executed.
serverIp	String	The IP address of the server on which the SQL statement is executed.
sumCpuTimeMs	Double	The total CPU consumption during the period.
sumElapsedTime	Double	The total response time of the SQL statement during the period (in milliseconds).
sumLogicalReads	Integer	The total number of logical reads of the SQL statement.
sumWaitTime	Double	The total wait time of the SQL statement during the period (in milliseconds).
userName	String	The user of the SQL statement.
waitEvent	String	The longest wait event during the period.

Example

Request example

Query the performance trend of 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. Only query the performance of SQL on server ID 2 and database name 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
            }
        ]
    }
}