Query the performance indicator trend of an SQL statement|V3.1.1|OceanBase Cloud Platform|docs|OceanBase Documentations

Description

You can call this operation to query the performance indicator trend of an SQL statement within a specified time range. The performance trend comprises multiple sampling points, and each sampling point is included in the performance indicators of a specified timestamp. You can call this operation to query SQL performance changes with time. Note

If you specify a large time range, the returned sampling points may be approximate to 120.

Call description

Prerequisite

You have the Read-only permission on 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	Specifies the ID of the OBServer on which you want to query the performance indicators of an SQL statement. If it is not specified, the SQL performance indicators of all OBServers are queried.
dbName	String	No	oceanbase	Specifies the name of the database where you want to query the performance indicators of an SQL statement. If it is not specified, the SQL performance indicators of all databases are queried.

Response parameters

Basic data structure

Parameter	Type	Description
data	Object	The requested data.
├─ contents	Array	The array of SQL performance sampling data. For more information about its data structure, see the following table.
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	An HTTP status code.
traceId	String	The trace ID of the request. This trace ID is used for troubleshooting.
server	String	The address of the application server that responded to the request.

Data structure of SQL performance indicators

Parameter	Type	Description
timestamp	Datetime	The timestamp of the sampling.
executions	Integer	The total number of times the SQL statement was executed during its execution period.
execPs	Float	The average number of times the SQL statement was executed every second during its execution period. The value is rounded to two decimal places.
avgAffectedRows	Float	The average number of updated rows during the execution period of the SQL statement. The value is rounded to two decimal places.
avgReturnRows	Float	The average number of returned rows during the execution period of the SQL statement. The value is rounded to two decimal places.
avgPartitionCount	Float	The average number of accessed partitions during the execution period of the SQL statement.
failCount	Integer	The total number of errors occurred during the execution period of the SQL statement.
failPercentage	Float	The percentage of errors occurred during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places.
retCode4012Count	Integer	The total number of timeout errors occurred during the execution period of the SQL statement.
avgWaitTime	Float	The average wait time during the execution period of the SQL statement.
avgWaitCount	Float	The average wait times during the execution period of the SQL statement.
avgRpcCount	Float	The average RPC sending times during the execution period of the SQL statement.
localPlanPercentage	Float	The percentage of the local plans during the execution period of the SQL statement.
remotePlanPercentage	Float	The percentage of the remote plans during the execution period of the SQL statement.
disPlanPercentage	Float	The percentage of the distributed plans during the execution period of the SQL statement.
avgElapsedTime	Float	The average response time during the execution period of the SQL statement, in milliseconds.
maxElapsedTime	Float	The maximum response time during the execution period of the SQL statement, in milliseconds.
avgCpuTime	Float	The average CPU time during the execution period of the SQL statement, in milliseconds.
maxCpuTime	Float	The maximum CPU time during the execution period of the SQL statement, in milliseconds.
avgNetTime	Float	The average network transmission time during the execution period of the SQL statement, in milliseconds.
avgNetWaitTime	Float	The average time spent for the SQL statement to enter into a queue during its execution period, in milliseconds.
avgQueueTime	Float	The average queue time of the SQL statement during its execution period, in milliseconds.
avgDecodeTime	Float	The average syntax parsing time during the execution period of the SQL statement, in milliseconds.
avgGetPlanTime	Float	The average plan generation time during the execution period of the SQL statement, in milliseconds.
avgExecuteTime	Float	The average plan execution time during the execution period of the SQL statement, in milliseconds.
avgExecutorRpcCount	Float	The average number of RPCs during the execution period of the SQL statement.
missPlanPercentage	Float	The retrieval failure rate of the plans during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places.
avgApplicationTime	Float	The average Application wait time during the execution period of the SQL statement, in milliseconds.
avgConcurrencyWaitTime	Float	The average Concurrency wait time during the execution period of the SQL statement, in milliseconds.
avgUserIoWaitTime	Float	The average UserIO wait time during the execution period of the SQL statement, in milliseconds.
avgScheduleTime	Float	The average Schedule wait time during the execution period of the SQL statement, in milliseconds.
avgRowCacheHit	Float	The average RowCache hit times during the execution period of the SQL statement.
avgBloomFilterCacheHit	Float	The average BloomFilterCache hit times during the execution period of the SQL statement.
avgBlockCacheHit	Float	The average BlockCache hit times during the execution period of the SQL statement.
avgBlockIndexCacheHit	Float	The average BlockIndexCache hit times during the execution period of the SQL statement.
avgDiskReads	Float	The average disk read times during the execution period of the SQL statement.
retryCount	Integer	The total retry times during the execution period of the SQL statement.
tableScanPercentage	Float	The percentage of table scans during the execution period of the SQL statement.
strongConsistencyPercentage	Float	The percentage of strong consistency transactions during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places.
weakConsistencyPercentage	Float	The percentage of weak consistency transactions during the execution period of the SQL statement. Value range: 0‒1. The value is rounded to four decimal places.
avgMemstoreReadRows	Float	The average MemStore read rows during the execution period of the SQL statement.
avgSsstoreReadRows	Float	The average SsStore read rows during the execution period of the SQL statement.

Examples

Sample request

Query the performance indicator trend of the SQL statement whose ID is C9B3A63AFF8F2A408CCF3D5B0F36682D based on the cluster ID 1 and tenant ID 1001, and set the time range to 2020-11-03T19:00:00+08:00 to 2020-11-03T20:00. Query only the SQL performance on the server with an ID of 2 and 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

Sample response

{
    "duration": 2134,
    "server": "xxx.xxx.xxx.xxx",
    "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
            }
        ]
    }
}