Query SQL performance statistics|V4.3.5|OceanBase Cloud Platform|docs|OceanBase Documentations

Query SQL performance statistics

Last Updated：2026-09-04 02:20:48 Updated

Function Description

Queries the SQL performance statistics of a tenant within a specified time interval. The performance statistics include the following information: cluster, tenant, server, database, user, SQL_ID, response time, CPU time, execution count, and error count. You can specify the performance metrics of a specific SQL statement based on the following conditions: whether it is an internal SQL statement, keywords in the SQL text, and advanced search.

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 Not, keywords in the SQL text, or using advanced search.

Note

Interface constraints

This API query is time-consuming. 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 the OCP server and MonitorDB, and adjust the specifications as needed.

## **API details** **Constraints**

You must have read permission on the specified tenant. Request path

GET /api/v2/ob/clusters/{clusterId}/tenants/{tenantId}/topSql

Request parameters

Response parameters

Basic data structure

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: EQ: equal to NE: not equal to GT: greater than GE: greater than or equal to LT: less than LE: less than or equal to
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. |

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. |

Parameter	Type	Description
sqlId	String	The ID of the SQL statement.
server	String	The address of the OceanBase server.
dbName	String	The database in which the SQL statement is executed.
userName	String	The user that executes the SQL statement.
sqlType	String	The type of the SQL statement. The value range is as follows: SELECT: a query statement INSERT: an insert statement UPDATE: an update statement DELETE: a delete statement REPLACE: a replace statement EXPLAIN: a plan parsing statement UNKNOWN: other statements
sqlTextShort	String	The first 100 characters of the SQL text.
inner	Boolean	Specifies whether the SQL statement is an internal SQL statement.
waitEvent	String	The most time-consuming internal event for the SQL statement in the specified time interval. The value range is as follows: system internal wait mysql response wait client sync rpc db file data read
executions	Integer	The total number of executions of the SQL statement in the period.
execPs	Float	The average number of executions of the SQL statement per second in the period, with two decimal places retained.
avgAffectedRows	Float	The average number of updated rows of the SQL statement in the period, with two decimal places retained.
avgReturnRows	Float	The average number of returned rows of the SQL statement in the period, with two decimal places retained.
avgPartitionCount	Float	The average number of partitions accessed by the SQL statement in the period.
failCount	Integer	The total number of errors of the SQL statement in the period.
failPercentage	Float	The error percentage of the SQL statement in the period. The return value is in the range [0, 1]. Four decimal places are retained.
retCode4012Count	Integer	The total number of times the SQL statement in the period exceeded the time limit (error code 4012).
retCode4013Count	Integer	The total number of times the SQL statement in the period experienced memory shortage (error code 4013).
retCode5001Count	Integer	The total number of times the SQL statement in the period experienced syntax parsing errors (error code 5001).
retCode5024Count	Integer	The total number of times the SQL statement in the period experienced key-value conflict errors (error code 5024).
retCode5167Count	Integer	The total number of times the SQL statement in the period experienced data length exceeding errors (error code 5167).
retCode5217Count	Integer	The total number of times the SQL statement in the period experienced unknown column errors (error code 5217).
retCode6002Count	Integer	The total number of times the SQL statement in the period experienced transaction rollback errors (error code 6002).
avgWaitTime	Float	The average waiting time of the SQL statement in the period.
avgWaitCount	Float	The average number of times the SQL statement in the period waits.
avgRpcCount	Float	The average number of RPC requests sent by the SQL statement in the period.
localPlanPercentage	Float	The percentage of local plans of the SQL statement in the period.
remotePlanPercentage	Float	The percentage of remote plans of the SQL statement in the period.
disPlanPercentage	Float	The percentage of distributed plans of the SQL statement in the period.
avgElapsedTime	Float	The average response time of the SQL statement in the period in milliseconds.
maxElapsedTime	Float	The maximum response time of the SQL statement in the period in milliseconds.
avgCpuTime	Float	The average CPU time of the SQL statement in the period in milliseconds.
maxCpuTime	Float	The maximum CPU time of the SQL statement in the period in milliseconds.
avgNetTime	Float	The average network transfer time of the SQL statement in the period in milliseconds.
avgNetWaitTime	Float	The average network waiting time of the SQL statement in the period in milliseconds.
avgQueueTime	Float	The average queue time of the SQL statement in the period in milliseconds.
avgDecodeTime	Float	The average syntax parsing time of the SQL statement in the period in milliseconds.
avgGetPlanTime	Float	The average plan generation time of the SQL statement in the period in milliseconds.
avgExecuteTime	Float	The average plan execution time of the SQL statement in the period in milliseconds.
avgExecutorRpcCount	Float	The average number of execution RPC requests of the SQL statement in the period.
missPlanPercentage	Float	The plan miss rate of the SQL statement in the period. The return value is in the range [0, 1]. Four decimal places are retained.
avgApplicationWaitTime	Float	The average waiting time of the SQL statement in the Application event in the period in milliseconds.
avgConcurrencyWaitTime	Float	The average waiting time of the SQL statement in the Concurrency event in the period in milliseconds.
avgUserIoWaitTime	Float	The average waiting time of the SQL statement in the UserIO event in the period in milliseconds.
avgScheduleTime	Float	The average waiting time of the SQL statement in the Schedule event in the period in milliseconds.
avgRowCacheHit	Float	The average number of RowCache hits of the SQL statement in the period.
avgBloomFilterCacheHit	Float	The average number of BloomFilterCache hits of the SQL statement in the period.
avgBlockCacheHit	Float	The average number of BlockCache hits of the SQL statement in the period.
avgBlockIndexCacheHit	Float	The average number of BlockIndexCache hits of the SQL statement in the period.
avgDiskReads	Float	The average number of disk reads of the SQL statement in the period.
retryCount	Integer	The total number of retries of the SQL statement in the period.
tableScanPercentage	Float	The percentage of table scans of the SQL statement in the period.
strongConsistencyPercentage	Float	The percentage of strong-consistency transactions of the SQL statement in the period. The return value is in the range [0, 1]. Four decimal places are retained.
weakConsistencyPercentage	Float	The percentage of weak-consistency transactions of the SQL statement in the period. The return value is in the range [0, 1]. Four decimal places are retained.
avgMemstoreReadRows	Float	The average number of MemStore reads of the SQL statement in the period.
avgSsstoreReadRows	Float	The average number of SsStore reads of the SQL statement in the period.
avgLogicalReads	Double	The average number of logical reads of the SQL statement in the period.
cpuPercentage	Double	The percentage of CPU time of the SQL statement in the TOP SQL statement.
dynamicSql	Boolean	Specifies whether the SQL statement is a dynamic SQL statement.
lastFailCode	Integer	The error code of the last error of the SQL statement in the period.
lastFailTimestamp	Integer	The timestamp of the last error of the SQL statement in the period.
obDbId	Integer	The ID of the OceanBase database.
obTenantId	Integer	The ID of the OceanBase tenant.
serverIp	String	The IP address of the OBServer.
sumCpuTimeMs	Double	The total CPU time of the SQL statement in the period.
sumElapsedTime	Double	The total response time of the SQL statement in the period in milliseconds.
sumLogicalReads	Integer	The total number of logical reads of the SQL statement in the period.
sumWaitTime	Double	The total waiting time of the SQL statement in the period in milliseconds.
tenantId	Integer	The ID of the tenant.
tenantName	String	The name of the tenant.

SELECT: query statement.

INSERT: insert statement.

UPDATE: update statement.

DELETE: delete statement.

REPLACE: replace statement.

EXPLAIN: plan parsing.

UNKNOWN: other statements.

system internal wait

mysql response wait client

sync rpc

db file data read

Note

The returned SQL text contains only the first 100 characters. To view the full SQL text, use the API for querying SQL text.

## **Example**

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
                }
                ]
                }
                }
            ```
        ]
    }
}

OceanBase

Customer Stories

Documentation

Query SQL performance statistics

Description

Function Description

Note

Note