Query the list of slow SQL statements

Description

You can call this operation to query the list of slow SQL statements.

Call description

Prerequisites

You have the Read-only permission on the specified tenant.

Request path

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

Request parameters

Response parameters

• Basic data structure

  Parameter	Type	Description
  data	Object	The requested data.
  ├─ contents	Array	The array of information about slow SQL statements. For more information, see Data structure of a slow SQL statement.
  successful	Boolean	Indicates whether the request was successful.
  timestamp	Datetime	The timestamp when the server completed the request.
  status	Integer	The 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 a slow SQL statement

  Parameter	Type	Description
  data	Object	The returned data.
  avgAffectedRows	Double	The average number of rows affected.
  avgApplicationWaitTime	Double	The average amount of time spent on an application wait event, in milliseconds.
  avgBlockCacheHit	Double	The average number of block cache hits.
  avgBlockIndexCacheHit	Double	The average number of block index cache hits.
  avgBloomFilterCacheHit	Double	The average number of Bloom filter cache hits.
  avgConcurrencyWaitTime	Double	The average amount of time spent on a concurrency wait event, in milliseconds.
  avgCpuTime	Double	The average CPU time, in milliseconds.
  avgDecodeTime	Double	The average amount of time spent in syntax parsing, in milliseconds.
  avgDiskReads	Double	The average number of disk reads.
  avgElapsedTime	Double	The average response time, in milliseconds.
  avgExecuteTime	Double	The average execution time of the SQL statement, in milliseconds.
  avgExecutorRpcCount	Double	The number of remote executions of the SQL statement, which corresponds to v$sl_audit.executor_rpc.
  avgGetPlanTime	Double	The average amount of time spent in generating an execution plan, in milliseconds.
  avgLogicalReads	Double	The average number of logical reads.
  avgMemstoreReadRows	Double	The average number of rows read from the MemStore.
  avgNetTime	Double	The average network I/O time of the SQL statement, in milliseconds.
  avgNetWaitTime	Double	The average network wait time of the SQL statement, in milliseconds.
  avgPartitionCount	Double	The average number of partitions accessed.
  avgQueueTime	Double	The average queuing time, in milliseconds.
  avgReturnRows	Double	The average number of rows returned.
  avgRowCacheHit	Double	The average number of row cache hits.
  avgRpcCount	Double	The average number of remote procedure calls (RPCs) executed by the SQL statement.
  avgScheduleTime	Double	The average scheduling time, in milliseconds.
  avgSsstoreReadRows	Double	The average number of rows read from the SSTable.
  avgUserIoWaitTime	Double	The average user I/O time, in milliseconds.
  avgWaitCount	Double	The average number of waits of the SQL statement.
  avgWaitTime	Double	The average wait time, in milliseconds.
  dbName	String	The name of the database.
  distPlanPercentage	Double	The percentage of distributed execution plans used for the SQL statement.
  execPs	Double	The average number of executions per second within the specified period.
  executions	Double	The total number of executions within the specified period.
  failCount	Double	The number of execution errors.
  failPercentage	Double	The execution error rate of the SQL statement.
  inner	Boolean	Indicates whether the SQL statement is an internal SQL statement.
  lastFailCode	Long	The error code of the last execution error.
  lastFailTimestamp	Datetime	The timestamp of the last execution error.
  localPlanPercentage	Double	The percentage of local execution plans used for the SQL statement.
  maxAffectedRows	Double	The maximum number of rows affected by the SQL statement.
  maxApplicationWaitTime	Double	The maximum amount of time spent on an application wait event, in milliseconds.
  maxConcurrencyWaitTime	Double	The maximum amount of time spent on a concurrency wait event, in milliseconds.
  maxCpuTime	Double	The maximum CPU time.
  maxDiskReads	Double	The maximum number of disk reads.
  maxElapsedTime	Double	The maximum response time of the SQL statement.
  maxReturnRows	Double	The maximum number of rows returned by the SQL statement.
  maxUserIoWaitTime	Double	The maximum amount of time spent on a user I/O wait event, in milliseconds.
  missPlanPercentage	Double	The percentage of execution plan misses of the SQL statement.
  obDbId	Long	The ID of the OceanBase cluster.
  obTenantId	Long	ID of the OceanBase Database tenant.
  remotePlanPercentage	Double	The percentage of remote execution plans used for the SQL statement.
  retCode4012Count	Long	The number of occurrences of the error code 4012.
  retCode4013Count	Long	The number of occurrences of the error code 4013.
  retCode5001Count	Long	The number of occurrences of the error code 5001.
  retCode5024Count	Long	The number of occurrences of the error code 5024.
  retCode5167Count	Long	The number of occurrences of the error code 5167.
  retCode5217Count	Long	The number of occurrences of the error code 5217.
  retCode6002Count	Long	The number of occurrences of the error code 6002.
  retryCount	Double	The number of retries.
  server	String	The address of the application server that responded to the request.
  serverIp	String	The IP address of the OBServer node.
  sqlId	String	The ID of the SQL statement.
  sqlTextShort	String	The foremost 100 characters of the SQL text.
  sqlType	String	The type of the SQL statement.
  strongConsistencyPercentage	Double	The percentage of strong-consistency reads of the SQL statement.
  sumCpuTimeMs	Double	The total CPU time, in milliseconds.
  sumElapsedTime	Double	The total response time, in milliseconds.
  sumLogicalReads	Double	The total number of logical reads.
  sumWaitTime	Double	The wait time of the longest wait event, in milliseconds.
  tableScanPercentage	Double	The percentage of full-table scans.
  tenantId	Integer	The ID of the tenant.
  tenantName	String	The name of the tenant.
  userName	String	The username.
  waitEvent	String	The longest wait event.
  weakConsistencyPercentage	Double	The percentage of weak-consistency reads of the SQL statement.

Examples

Sample request

Query the slow SQL statements for the tenant with the ID 1196 in the cluster with the ID 250 during the period from 2024-07-28T00:00:00+08:00 to 2024-07-29T00:00:00+08:00.

GET /api/v2/ob/clusters/250/tenants/1196/slowSql?startTime=2024-07-28T00%3A00%3A00%2B08%3A00&endTime=2024-07-29T00%3A00%3A00%2B08%3A00

Sample response

{
  "data": {
    "contents": [
      {
        "avgAffectedRows": 0,
        "avgApplicationWaitTime": 0,
        "avgBlockCacheHit": 0,
        "avgBlockIndexCacheHit": 0,
        "avgBloomFilterCacheHit": 0,
        "avgConcurrencyWaitTime": 0,
        "avgCpuTime": 108.49,
        "avgDecodeTime": 0,
        "avgDiskReads": 0,
        "avgElapsedTime": 108.49,
        "avgExecuteTime": 108.27,
        "avgExecutorRpcCount": 0,
        "avgGetPlanTime": 0.22,
        "avgLogicalReads": 0,
        "avgMemstoreReadRows": 0,
        "avgNetTime": 0,
        "avgNetWaitTime": 0,
        "avgPartitionCount": 0,
        "avgQueueTime": 0,
        "avgReturnRows": 0,
        "avgRowCacheHit": 0,
        "avgRpcCount": 0,
        "avgScheduleTime": 0,
        "avgSsstoreReadRows": 0,
        "avgUserIoWaitTime": 0,
        "avgWaitCount": 0,
        "avgWaitTime": 0,
        "dbName": "oceanbase",
        "distPlanPercentage": 0,
        "execPs": 0.02,
        "executions": 1,
        "failCount": 1,
        "failPercentage": 100,
        "inner": false,
        "lastFailCode": 0,
        "lastFailTimestamp": 0,
        "localPlanPercentage": 0,
        "maxAffectedRows": 0,
        "maxApplicationWaitTime": 0,
        "maxConcurrencyWaitTime": 0,
        "maxCpuTime": 108.49,
        "maxDiskReads": 0,
        "maxElapsedTime": 108.49,
        "maxReturnRows": 0,
        "maxUserIoWaitTime": 0,
        "missPlanPercentage": 100,
        "obDbId": 201001,
        "obTenantId": 1010,
        "remotePlanPercentage": 0,
        "retCode4012Count": 0,
        "retCode4013Count": 0,
        "retCode5001Count": 0,
        "retCode5024Count": 0,
        "retCode5167Count": 0,
        "retCode5217Count": 0,
        "retCode6002Count": 0,
        "retryCount": 0,
        "server": "xxx.xxx.xxx.xxx:2882",
        "serverIp": "xxx.xxx.xxx.xxx",
        "sqlId": "7FA0ED8C8C94A4F26548FCDDA2368C21",
        "sqlTextShort": "CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();",
        "sqlType": "OTHER",
        "strongConsistencyPercentage": 0,
        "sumCpuTimeMs": 108.49,
        "sumElapsedTime": 108.49,
        "sumLogicalReads": 0,
        "sumWaitTime": 0,
        "tableScanPercentage": 0,
        "tenantId": 1196,
        "tenantName": "ocp_admin_pr",
        "userName": "root",
        "waitEvent": "none",
        "weakConsistencyPercentage": 0
      },
      {
        "avgAffectedRows": 0,
        "avgApplicationWaitTime": 0,
        "avgBlockCacheHit": 0,
        "avgBlockIndexCacheHit": 0,
        "avgBloomFilterCacheHit": 0,
        "avgConcurrencyWaitTime": 0,
        "avgCpuTime": 105.02,
        "avgDecodeTime": 0,
        "avgDiskReads": 0,
        "avgElapsedTime": 105.02,
        "avgExecuteTime": 99.28,
        "avgExecutorRpcCount": 0,
        "avgGetPlanTime": 5.74,
        "avgLogicalReads": 0,
        "avgMemstoreReadRows": 0,
        "avgNetTime": 0,
        "avgNetWaitTime": 0,
        "avgPartitionCount": 2,
        "avgQueueTime": 0,
        "avgReturnRows": 1,
        "avgRowCacheHit": 0,
        "avgRpcCount": 0,
        "avgScheduleTime": 0,
        "avgSsstoreReadRows": 0,
        "avgUserIoWaitTime": 0,
        "avgWaitCount": 0,
        "avgWaitTime": 0,
        "dbName": "oceanbase",
        "distPlanPercentage": 0,
        "execPs": 0.02,
        "executions": 1,
        "failCount": 0,
        "failPercentage": 0,
        "inner": false,
        "lastFailCode": 0,
        "lastFailTimestamp": 0,
        "localPlanPercentage": 100,
        "maxAffectedRows": 0,
        "maxApplicationWaitTime": 0,
        "maxConcurrencyWaitTime": 0,
        "maxCpuTime": 105.02,
        "maxDiskReads": 0,
        "maxElapsedTime": 105.02,
        "maxReturnRows": 1,
        "maxUserIoWaitTime": 0,
        "missPlanPercentage": 100,
        "obDbId": 201001,
        "obTenantId": 1010,
        "remotePlanPercentage": 0,
        "retCode4012Count": 0,
        "retCode4013Count": 0,
        "retCode5001Count": 0,
        "retCode5024Count": 0,
        "retCode5167Count": 0,
        "retCode5217Count": 0,
        "retCode6002Count": 0,
        "retryCount": 0,
        "server": "xxx.xxx.xxx.xxx:2882",
        "serverIp": "xxx.xxx.xxx.xxx",
        "sqlId": "8B5B4B4CC550A9251AE501E633E90D95",
        "sqlTextShort": "SELECT /*+ workload_repository_snapshot query_time",
        "sqlType": "SELECT",
        "strongConsistencyPercentage": 100,
        "sumCpuTimeMs": 105.02,
        "sumElapsedTime": 105.02,
        "sumLogicalReads": 0,
        "sumWaitTime": 0,
        "tableScanPercentage": 100,
        "tenantId": 1196,
        "tenantName": "ocp_admin_pr",
        "userName": "root",
        "waitEvent": "none",
        "weakConsistencyPercentage": 0
      }
    ]
  },
  "duration": 14,
  "server": "f00e301d58",
  "status": 200,
  "successful": true,
  "timestamp": "2024-07-29T10:50:22.176+08:00",
  "traceId": "1ab38f7744b4a012"
}