DescribeOasTopSQLList|OceanBase Cloud |docs|OceanBase Documentations

Overview

You can call this API to query the list of SQL execution performance data collected by the diagnostic system.

API details

Constraints

The caller must have an AccessKey for accessing APIs of OceanBase Cloud. For information about how to obtain the AccessKey ID and AccessKey secret, see Manage AccessKeys.

Request path

GET /api/v2/instances/{instanceId}/tenants/{tenantId}/topSql

Request parameters

Name	Type	Required	Description	Example value
instanceId	string	Yes	The ID of the OceanBase Cloud instance.	ob317v4uif****
tenantId	string	Yes	The ID of the tenant.	t4louaeei****
requestId	string	Yes	The request ID for tracking.	dc302d76-66b5-48d8-ab53-a035********
startTime	string	Yes	The start time for querying the top SQL statements. The time must be in UTC and in the format of: YYYY-MM-DDThh:mm:ssZ.	2023-04-12T04:38:38Z Note Escape special characters in the time parameter, such as colons ( : ). For example, 2023-04-12T05:38:38Z must be escaped as 2023-04-12T04%3A38%3A38Z when it is used as a URL request parameter.
endTime	string	Yes	The end time for querying the top SQL statements. The time must be in UTC and in the format of: YYYY-MM-DDThh:mm:ssZ.	2023-04-12T05:38:38Z Note Escape special characters in the time parameter, such as colons ( : ). For example, 2023-04-12T05:38:38Z must be escaped as 2023-04-12T04%3A38%3A38Z when it is used as a URL request parameter.
dbName	string	No	The name of the database.	test_db
searchKeyWord	string	No	The keyword for the query.	update
nodeIp	string	No	The IP address of the node.	i-bp18l4****str4uk03
filterCondition	string	No	All fields are referenced by using the @ symbol. For information about the optional fields, see the columns returned by the Query SQL Performance Statistics API.	@avgCpuTime > 20 and @executions > 100
sqlTextLength	long	No	The maximum length of the returned SQL text.	65535
limit	integer	No	The maximum number of results to return.	100
mergeDynamicSql	boolean	No	Whether to merge dynamic SQL statements.	true
sqlId	string	No	The unique identifier of the SQL statement.	sql_123456789
dynamicSql	boolean	No	Whether the SQL statement is dynamic.	false
customColumns	array[string]	No	The custom columns.	["executions", "avgCpuTime"]
httpServletRequest	unknown	Yes	The HTTP servlet request.	N/A

Response parameters

Name	Type	Description	Example value
	object	The return result of the request.
Data	object []	The list of TopSQL information.
executions	double	The total number of executions during the period.	1
rpcCount	double	The number of RPCs during the period.	0.0
remotePlans	double	The number of remote executions during the period.	0
missPlans	double	The number of times the execution plan is not hit during the period.	0
maxElapsedTime	double	The maximum response time during the period.	13643.3
totalWaitTime	double	The total waiting time during the period, in milliseconds.	10.966
execPs	double	The average number of executions per second during the period.	0.31
maxCpuTime	double	The maximum CPU time, in milliseconds.	13641.9
cpuPercentage	double	The CPU usage.	100.0
clientIp	string	The IP address of the client.	i-bp1db****38uemejio
userName	string	The username.	test_user
dbName	string	The name of the database.	test_db
retCode4012Count	long	The number of times result code 4012 occurs during the period.	0
retCode4013Count	long	The number of times result code 4013 occurs during the period.	0
retCode5001Count	long	The number of times result code 5001 occurs during the period.	0
retCode5024Count	long	The number of times result code 5024 occurs during the period.	0
retCode5167Count	long	The number of times result code 5167 occurs during the period.	0
retCode5217Count	long	The number of times result code 5217 occurs during the period.	0
retCode6002Count	long	The number of times result code 6002 occurs during the period.	0
failPercentage	double	The error percentage during the period.	0.0
sumWaitTime	double	The total waiting time during the period, in milliseconds.	9421.73
avgWaitCount	double	The average number of waits during the period.	0.0
avgRpcCount	double	The average number of RPCs sent during the period.	8.0
localPlanPercentage	double	The percentage of local plans in the period.	0.0
remotePlanPercentage	double	The percentage of remote plans in the period.	0.0
distPlanPercentage	double	The percentage of distributed plans in the period.	100.0
sumElapsedTime	double	The total response time in the period (in milliseconds).	11452126.36
avgNetTime	double	The average network transmission time in the period (in milliseconds).	0.0
avgExecutorRpcCount	double	The average number of RPC requests executed in the period.	0.0
missPlanPercentage	double	The plan hit rate in the period.	0.0
tableScanPercentage	double	The percentage of table scans in the period.	0.0
strongConsistencyPercentage	double	The percentage of strong consistency transactions in the period.	100.0
weakConsistencyPercentage	double	The percentage of weak consistency transactions in the period.	0.0
maxAffectedRows	double	The maximum number of rows affected in the period.	10000.0
maxReturnRows	double	The maximum number of rows returned in the period.	0.0
maxWaitTime	double	The maximum wait time in the period (in milliseconds).	3.4
maxApplicationWaitTime	double	The maximum Application event wait time in the period (in milliseconds).	0.0
maxConcurrencyWaitTime	double	The maximum Concurrency event wait time in the period (in milliseconds).	0.0
maxUserIoWaitTime	double	The maximum UserIO event wait time in the period (in milliseconds).	0.0
maxDiskReads	double	The maximum number of physical reads in the period.	0.0
avgExpectedWorkerCount	double	The average parallelism in the period.	3
avgUsedWorkerCount	double	The average number of SQL threads used in the period.	3
sumLogicalReads	double	The total logical reads of SQL in the period.	0.0
server	string	The server where the SQL statement is executed.	xxx.xxx.x.xxx:xxxx
serverIp	string	The IP address of the server where the SQL statement is executed.	xxx.xxx.x.xxx:xxxx
serverPort	long	The port of the server where the SQL statement is executed.	389
sqlTextShort	string	The text of the SQL statement (first 100 characters).	delete /+ XXX PARALLEL(4) / fro
sqlType	string	The type of the SQL statement.	select
sqlId	string	The SQL ID.	8D6E84**0B8FB1823D199E2CA1**
inner	boolean	Indicates whether the SQL statement is an internal one.	false
waitEvent	string	The longest wait event in the period.	none
avgAffectedRows	double	The average number of rows updated in the period.	9978.75
avgReturnRows	double	The average number of rows returned in the period.	0.0
avgPartitionCount	double	The average number of partitions accessed in the period.	1.0
failCount	double	The number of errors.	0
avgWaitTime	double	The average wait time (ms) in the period.	1442.49
avgElapsedTime	double	The average response time (ms) in the period.	903.29
avgCpuTime	double	The average CPU time (ms) in the period.	1875.34
avgNetWaitTime	double	The average network enqueue time (ms) in the period.	0.0
avgQueueTime	double	The average queue time (ms) in the period.	0.01
avgDecodeTime	double	The average syntax parsing time (ms) in the period.	0.0
avgGetPlanTime	double	The average plan generation time (ms) in the period.	0.0
avgExecuteTime	double	The average plan execution time (ms) in the period.	1895.7
avgApplicationWaitTime	double	The average Application event wait time (ms) in the period.	0.0
avgConcurrencyWaitTime	double	The average Concurrency event wait time (ms) in the period.	0.0
avgUserIoWaitTime	double	The average UserIO event wait time (ms) in the period.	0.0
avgScheduleTime	double	The average Schedule event wait time (ms) in the period.	0.0
avgRowCacheHit	double	The average number of RowCache hits in the period.	0.0
avgBloomFilterCacheHit	double	The average number of BloomFilterCache hits in the period.	0.0
avgBlockCacheHit	double	The average number of BlockCache hits in the period.	0.0
avgBlockIndexCacheHit	double	The average number of BlockIndexCache hits in the period.	0.0
avgDiskReads	double	The average number of physical reads in the period.	0.0
retryCount	double	The total number of retries in the period.	0
avgMemstoreReadRows	double	The number of rows read from memory.	0.0
avgSsstoreReadRows	double	The number of rows read from the disk.	0.0
avgLogicalReads	double	The average number of logical reads in the period.	0.0

Examples

Request example

curl --request GET \
  --digest -u '<Your AccessKey ID:AccessKey Secret>' \
  -G \
  -d 'requestId={requestId}' \
  -d 'startTime=2024-08-27T00%3A35%3A32Z' \
  -d 'endTime=2024-08-27T06%3A05%3A32Z' \
  -d 'dbName={dbName}' \
  -d 'searchKeyWord={searchKeyWord}' \
  -d 'nodeIp={nodeIp}' \
  -d 'filterCondition={filterCondition}' \
  -d 'sqlTextLength={sqlTextLength}' \
  -d 'limit={limit}' \
  -d 'mergeDynamicSql={mergeDynamicSql}' \
  -d 'sqlId={sqlId}' \
  -d 'dynamicSql={dynamicSql}' \
  -d 'customColumns={customColumns}' \
  --url 'https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/topSql' \
  -H 'X-Ob-Project-Id: <Project ID>'

Notice

Special characters in parameters must be escaped. For example, -d'@avgCpuTime>0' is rewritten as -d'%40avgCpuTime%3E0'. Colons (:) are special characters in time parameters and must be escaped.
A project ID uniquely identifies a project. For more information about how to obtain the project ID of an instance, see Manage projects.
Make sure that the project ID corresponding to the specified instanceId is consistent with the specified project ID.

Response example

JSON format

    {
      "RequestId": "EE205C00-30E4-****-****-87E3A8A2AA0C",
      "Data": [
        {
          "executions": 1,
          "rpcCount": 0,
          "remotePlans": 0,
          "missPlans": 0,
          "maxElapsedTime": 13643.3,
          "totalWaitTime": 10.966,
          "execPs": 0.31,
          "maxCpuTime": 13641.9,
          "cpuPercentage": 100,
          "clientIp": "i-bp1db****38uemejio",
          "userName": "test_user",
          "dbName": "test_db",
          "retCode4012Count": 0,
          "retCode4013Count": 0,
          "retCode5001Count": 0,
          "retCode5024Count": 0,
          "retCode5167Count": 0,
          "retCode5217Count": 0,
          "retCode6002Count": 0,
          "failPercentage": 0,
          "sumWaitTime": 9421.73,
          "avgWaitCount": 0,
          "avgRpcCount": 8,
          "localPlanPercentage": 0,
          "remotePlanPercentage": 0,
          "distPlanPercentage": 100,
          "sumElapsedTime": 11452126.36,
          "avgNetTime": 0,
          "avgExecutorRpcCount": 0,
          "missPlanPercentage": 0,
          "tableScanPercentage": 0,
          "strongConsistencyPercentage": 100,
          "weakConsistencyPercentage": 0,
          "maxAffectedRows": 10000,
          "maxReturnRows": 0,
          "maxWaitTime": 3.4,
          "maxApplicationWaitTime": 0,
          "maxConcurrencyWaitTime": 0,
          "maxUserIoWaitTime": 0,
          "maxDiskReads": 0,
          "avgExpectedWorkerCount": 3,
          "avgUsedWorkerCount": 3,
          "sumLogicalReads": 0,
          "server": "xxx.xxx.x.xxx:xxxx",
          "serverIp": "xxx.xxx.x.xxx:xxxx",
          "serverPort": 389,
          "sqlTextShort": "delete /*+ XXX PARALLEL(4) */ fro",
          "sqlType": "select ",
          "sqlId": "8D6E84****0B8FB1823D199E2CA1****"}]}