Overview
You can call this API to query the execution plan of an SQL statement.
API details
Constraints
The caller must have an AccessKey for accessing the multi-cloud API. For information about how to obtain the AccessKey ID and AccessKey secret, see Manage AccessKeys.
Request path
GET /api/v2/instances/{instanceId}/tenants/{tenantId}/sqls/{sqlId}/topPlanGroup
Request parameters
Path
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| instanceId | String | Yes | The ID of the instance. | obxxx |
| tenantId | String | Yes | The ID of the tenant. | txxx |
| sqlId | String | Yes | The ID of the SQL statement. | 5D51DC02208F9A448157FD8A******** |
Query
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| dbName | String | No | The name of the database. | test11 |
| startTime | Long | No | The start time. | 2025-01-01T00:00:00Z |
| endTime | Long | No | The end time. | 2025-01-02T00:00:00Z |
| dynamicSql | Boolean | No | Specifies whether the SQL statement is dynamic. | false |
| planUnionHash | String | No | The unique identifier of the execution plan. | c******** |
| returnBriefInfo | Boolean | No | Specifies whether to return brief execution plan information. | true |
| checkOutlineStatus | Boolean | No | Specifies whether to check the status of the outline. | false |
| formatSqlId | Boolean | No | format SQL Id | - |
Response parameters
| Parameter | Type | Description | Example |
|---|---|---|---|
| data | Object | Data. | - |
| dataList | Array | Data list. | - |
| avgCpuTime | Number | Average CPU time during the period (ms). | 447.66 |
| bounded | Boolean | Indicates whether the execution plan is bound. | false |
| byFormatSqlId | Boolean | Indicates whether the execution plan is bound in a fuzzy manner. | false |
| executions | Number | Number of executions. | 2088 |
| firstLoadTime | String | Time when the plan was first loaded. | 2025-12-23T06:43:07.429Z |
| fullPlan | String | Full execution plan. | TOP-N S |
| hitDiagnosis | Boolean | Indicates whether the plan is hit. | false |
| mergedVersion | Number | Schema version. | 0 |
| outlineData | String | outline_data field of the SQL execution plan. | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test11"."high_disk_read_2"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.4.1.0') END_OUTLINE_DATA*/ |
| planExplain | Object | Details of the execution plan. | - |
| explainType | String | Type of the execution plan details: PHYSICAL (physical execution plan) or LOGICAL (logical execution plan). | LOGICAL |
| notEmpty | Boolean | Indicates whether the data is empty. | true |
| optimizationInfo | Array | Information about the optimizer, such as the number of physical rows, logical rows, and table accesses, and the type and version of the statistics used. | [OPTIMIZER STATISTICS, STORAGE] |
| otherInfo | String | Other information, such as the plan type, parameterized information of the Fast Parser, plan notes, and constraint information that the plan hits. | because of table property\n |
| outlineData | String | outline_data field of the SQL execution plan. | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test11"."high_disk_read_2"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.4.1.0') END_OUTLINE_DATA*/ |
| qbNameTrace | String | Modification tracking information of the qb_name field in REMARKS. | stmt_id:0, SEL$1 |
| rootOperations | Array | Table of operations. | - |
| children | Array | Sub-operations. | - |
| cost | Number | Cost. | 187080 |
| cpuCost | Number | CPU cost. | 154276 |
| depth | Number | Operator depth. | 1 |
| id | Number | Logical operator. | 1 |
| ioCost | Number | I/O cost. This parameter is valid only for logical execution plans. | 0 |
| objectName | String | Name of the operation object. | high_disk_read_2 |
| operator | String | Logical operator. | TABLE FULL SCAN |
| property | String | Output filter. | output([high_disk_read_2.id], |
| realCost | Number | Actual cost. This parameter is valid only for logical execution plans. | 153055 |
| realRows | Number | Actual number of rows. This parameter is valid only for logical execution plans. | 621 |
| rows | Number | Estimated number of rows. | 2000000 |
| cost | Number | Cost. | 192374 |
| cpuCost | Number | CPU cost. | 73 |
| depth | Number | Operator depth. | 0 |
| id | Number | ID of the logical operator. | 0 |
| ioCost | Number | I/O cost. | 0 |
| objectName | String | Name of the object. | |
| operator | String | Operator. | TOP-N SORT |
| property | String | Output filter. | output([high_disk_read_2.id], [high_disk_read_2.name], [high_disk_read_2.class]),rowset=256,sort_keys([high_disk_read_2.id, DESC]), topn(1) |
| realCost | Number | Actual cost. | 153055 |
| realRows | Number | Actual number of rows. | 1 |
| rows | Number | Number of rows. | 1 |
| usedHint | String | Hint used. | " /*+\n \n */" |
| planHash | String | Internal identifier of the SQL execution plan in the diagnostic system. | 16******** |
| planInfo | String | Information about the execution plan. | TOP-N SORT , | \n TABLE FULL SCAN , high_disk_read_2 | \n |
| planType | String | Type of the execution plan: LOCAL (local execution plan) or REMOTE (remote execution plan). | LOCAL |
| planUnionHash | String | Unique internal identifier of the SQL execution plan in the diagnostic system. | 16******** |
| plans | Array | List of execution plans. | - |
| bounded | Boolean | Indicates whether the execution plan is bound. | false |
| collectTimeUs | Number | Time when the plan was collected. | 1766448000000000 |
| firstLoadTime | String | Time when the plan was first loaded. | 2025-12-23T06:43:07.429Z |
| firstLoadTimeUs | Number | Time when the plan was first loaded (in microseconds). | 1766472187429757 |
| hitDiagnosis | Boolean | Indicates whether the plan is hit. | false |
| mergedVersion | Number | Schema version. | 0 |
| obDbId | Number | Database ID. | 500006 |
| obServerId | Number | Server ID. | 1 |
| outlineData | String | outline_data field of the SQL execution plan. | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test11"."high_disk_read_2"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.4.1.0') END_OUTLINE_DATA*/ |
| outlineId | Number | ID of the outline used by the plan. | -1 |
| planHash | String | The identifier of the SQL execution plan in the diagnostic system. | 16******** |
| planId | Number | The ID of the plan. | 1049 |
| planSize | Number | The size of the plan. | 66424 |
| schemaVersion | Number | The schema version. | 1766472172772496 |
| serverSn | String | The server SN. | i-uf****** |
| sqlId | String | The SQL ID. | 5D51DC02208F9A448157FD8A******** |
| tableScan | Boolean | Indicates whether a table scan is performed. | true |
| requestId | String | The ID of the request. | 12a53ac0-5eeb-4aaa-8753-3cacc696101a |
| success | Boolean | Indicates whether the call is successful. | true |
Examples
Request example
curl --digest \
-u 'ak:sk' \
-X GET \
'https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/sqls/{sqlId}/topPlanGroup' \
-G \
-d 'dbName=xxx' \
-d 'startTime=xxx' \
-d 'endTime=xxx' \
-d 'dynamicSql=xxx' \
-d 'planUnionHash=xxx' \
-d 'returnBriefInfo=xxx' \
-d 'checkOutlineStatus=xxx' \
-d 'formatSqlId=xxx'
Response example
JSON format
{
"data": {
"dataList": [
{
"avgCpuTime": 447.66,
"bounded": false,
"byFormatSqlId": false,
"executions": 2088,
"firstLoadTime": "2025-12-23T06:43:07.429Z",
"fullPlan": "TOP-N S",
"hitDiagnosis": false,
"mergedVersion": 0,
"outlineData": "/*+BEGIN_OUTLINE_DATA FULL(@\"SEL$1\" \"test11\".\"high_disk_read_2\"@\"SEL$1\") OPTIMIZER_FEATURES_ENABLE('4.4.1.0') END_OUTLINE_DATA*/",
"planExplain": {
"explainType": "LOGICAL",
"notEmpty": true,
"optimizationInfo": [
"OPTIMIZER STATISTICS, STORAGE]"
],
"otherInfo": " because of table property\n",
"outlineData": "/*+BEGIN_OUTLINE_DATA FULL(@\"SEL$1\" \"test11\".\"high_disk_read_2\"@\"SEL$1\") OPTIMIZER_FEATURES_ENABLE('4.4.1.0') END_OUTLINE_DATA*/",
"qbNameTrace": " stmt_id:0, SEL$1\n",
"rootOperations": [
{
"children": [
{
"cost": 187080,
"cpuCost": 154276,
"depth": 1,
"id": 1,
"ioCost": 0,
"objectName": "high_disk_read_2",
"operator": "TABLE FULL SCAN",
"property": "output([high_disk_read_2.id], ",
"realCost": 153055,
"realRows": 621,
"rows": 2000000
}
],
"cost": 192374,
"cpuCost": 73,
"depth": 0,
"id": 0,
"ioCost": 0,
"objectName": "",
"operator": "TOP-N SORT",
"property": "output([high_disk_read_2.id], [high_disk_read_2.name], [high_disk_read_2.class]),rowset=256,sort_keys([high_disk_read_2.id, DESC]), topn(1)",
"realCost": 153055,
"realRows": 1,
"rows": 1
}
],
"usedHint": " /*+\n \n */"
},
"planHash": "16********",
"planInfo": "TOP-N SORT , | \n TABLE FULL SCAN , high_disk_read_2 | \n",
"planType": "LOCAL",
"planUnionHash": "16********",
"plans": [
{
"bounded": false,
"collectTimeUs": 1766448000000000,
"firstLoadTime": "2025-12-23T06:43:07.429Z",
"firstLoadTimeUs": 1766472187429757,
"hitDiagnosis": false,
"mergedVersion": 0,
"obDbId": 500006,
"obServerId": 1,
"outlineData": "/*+BEGIN_OUTLINE_DATA FULL(@\"SEL$1\" \"test11\".\"high_disk_read_2\"@\"SEL$1\") OPTIMIZER_FEATURES_ENABLE('4.4.1.0') END_OUTLINE_DATA*/",
"outlineId": -1,
"planHash": "16********",
"planId": 1049,
"planSize": 66424,
"schemaVersion": 1766472172772496,
"serverSn": "i-uf********",
"sqlId": "5D51DC02208F9A448157FD8A********",
"tableScan": true
}
],
"tableScan": true
}
]
},
"requestId": "12a53ac0-5eeb-4aaa-8753-3cacc696101a",
"success": true
}
