Query performance indicators of an SQL statement

Description

You can call this operation to query SQL performance indicators of a tenant, and specify a time range to collect SQL performance indicators of the period. Performance data includes the cluster, tenant, server, database, user, SQL_ID, response time, CPU time, number of executions, and number of errors. You can query specified SQL performance indicators based on conditions such as whether it is an internal SQL statement, keywords in the SQL text, and advanced search conditions.

Note

This query takes a relatively long time. If the response is slow, you can reduce the query frequency, time range, or number of concurrent requests, check the resource usage of the OCP-Server and Monitor DB, and adjust the configuration accordingly

Call description

Prerequisite

You have the Read-only permission on the tenant.

Request path

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

Request parameters

Response parameters

• Basic data structure

  Parameter	Type	Description
  data	Object	The requested service data.
  ├─ contents	Array	The list of SQL performance indicators. 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 server that responded to the request.

• Data structure of SQL performance indicators

  Parameter	Type	Description
  sqlId	String	The ID of the SQL statement.
  server	String	The URL of the OBServer.
  dbName	String	The database where the SQL statement was executed.
  userName	String	The user who executed the SQL statement.
  sqlType	String	The type of the SQL statement. Valid values: * SELECT * INSERT * UPDATE * DELETE * REPLACE * EXPLAIN * UNKNOWN
  sqlTextShort	String	The first 100 characters of the SQL text.
  inner	Boolean	Indicates whether it was an internal SQL statement.
  waitEvent	String	The longest internal event of this SQL statement in the specified time range. Valid values: * system internal wait * mysql response wait client * sync rpc * db file data read
  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. Error code: 4012.
  retCode4013Count	Integer	The total number of insufficient memory errors occurred during the execution period of the SQL statement. Error code: 4013.
  retCode5001Count	Integer	The total number of syntax parsing errors occurred during the execution period of the SQL statement. Error code: 5001.
  retCode5024Count	Integer	The total number of key-value conflict errors occurred during the execution period of the SQL statement. Error code: 5024.
  retCode5167Count	Integer	The total number of data length excess errors occurred during the execution period of the SQL statement. Error code: 5167.
  retCode5217Count	Integer	The total number of unknown column errors occurred during the execution period of the SQL statement. Error code: 5217.
  retCode6002Count	Integer	The total number of transaction rollback errors occurred during the execution period of the SQL statement. Error code: 6002.
  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.

  Note

  Only the foremost 100 characters of the SQL text are returned. To query the complete SQL text, call the SQL text query operation.

Examples

Sample request

Query the SQL performance 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, and set the number of executions to greater than or equal to 500.

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

Sample response

The size of the returned performance data is affected by the database load and time range of the query. In the following code snippet, only two performance data records are returned.

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