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
NoteEscape 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
NoteEscape 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 ak:sk>' \
-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