DescribeOasSQLPlans - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

You can call this API to retrieve information about the SQL execution plan stored in the diagnostic system based on the SQL ID.

Debugging

OpenAPI Explorer automatically calculates the signature value. For your convenience, we recommend that you call this operation in OpenAPI Explorer.

Debug

Authorization information

There is currently no authorization information disclosed in the API.

Request parameters

Parameter	Type	Required	Description	Example
InstanceId	string	Yes	The ID of the OceanBase cluster.	ob317v4uif****
TenantId	string	Yes	The ID of the tenant.	t4louaeei****
StartTime	string	Yes	The start time of querying the SQL execution plan. The value must be UTC time in the format of YYYY-MM-DDThh:mm:ssZ.	2023-04-12T04:38:38Z
EndTime	string	Yes	The end time of querying the SQL execution plan. The value must be UTC time in the format of YYYY-MM-DDThh:mm:ssZ.	2023-04-12T05:38:38Z
DbName	string	Yes	The name of the database.	test_db
SqlId	string	Yes	SQL ID.	8D6E84**0B8FB1823D199E2CA1**
AcceptLanguage	string	No	Supported language. Valid values: zh-CN: Chinese en-US: English	zh-CN

Response parameters

Parameter	Type	Description	Example
	object	Returned response.
RequestId	string	The request ID.	EE205C00-30E4-**-**-87E3A8A2AA0C
Data	object []	The list of SQL execution plan.
PlanHash	integer	The internal identifier of the SQL execution plan in the diagnostic system.	1758**24913166**
Plans	object []	The list of the execution plan.
Uid	string	The unique identifier of the plan.	AAAAAAAAAAI**AAFoT2QAF--7W**
PlanId	long	The ID of the plan.	590****
FirstLoadTime	string	First time to load the plan.	2023-04-12T04:46:38Z
PlanType	string	Plan type, including LOCAL, REMOTE, DIST.	LOCAL
PlanHash	integer	The internal identifier of the SQL execution plan in the diagnostic system.	-86290582****886880
PlanSize	long	Plan size.	10****
SchemaVersion	long	Schema version.	16838****3550464
MergedVersion	long	Merge version.	513
ObServerId	long	Server ID.	2
ObDbId	long	Database ID.	11006****828
ServerId	long	The ID of the backend server instance.	2
FirstLoadTimeUs	long	First loading time.	1684****03289441
HitDiagnosis	boolean	Whether to hit the diagnosis.	false
OutlineData	string	The outline_data parameter of the SQL execution plan.	/*+ BEGIN_OUTLINE_DATA INDEX(@\"SEL$1\
OutlineId	long	Outline ID.	-1
CollectTimeUs	long	Collection time.	1684368****00000
Server	string	The server where the SQL is executed.	i-bp1db1a9us038****jio
HitPercentage	double	Hit rate.	100.0
Executions	long	The total number of executions during the execution period.	1
ExecPs	double	The average execution count per second during the execution period.	0.31
AvgDiskReads	double	The average physical read count during the execution period.	0.0
AvgDiskWrites	double	The average physical write count during the execution period.	0.0
AvgBufferGets	double	Average cache read count during the execution period.	30137.03
AvgApplicationWaitTime	double	Average Application event waiting time (in milliseconds) during the execution period.	0.0
AvgConcurrencyWaitTime	double	Average Concurrency event waiting time (in milliseconds) during the execution period.	0.0
AvgUserIoWaitTime	double	Average UserIo event waiting time (in milliseconds) during the execution period.	0.0
AvgRowProcessed	double	Average row processing waiting time (in milliseconds) during the execution period.	1.0
AvgElapsedTime	double	Average interval waiting time (in milliseconds) during the execution period.	1876.78
AvgCpuTime	double	Average CPU time (in milliseconds) during the execution period.	1875.34
LargeQueryPercentage	double	Percentage of completed long-running queries.	0.0
DelayedLargeQueryPercentage	double	Percentage of delayed long-running queries.	0.0
TimeoutPercentage	double	Percentage of timeouts.	0.0
TableScan	boolean	Whether a full table scan is performed.	false
PlanUnionHash	string	The internal unique identifier of the SQL execution plan in the diagnostic system.	"52c7c53**53e61b3f7586b17**ad"
MergedVersion	long	Merge version.	513
FirstLoadTime	string	First loading time.	2023-04-12T04:46:38Z
PlanType	string	Execution plan type.	LOCAL
HitDiagnosis	number	Whether to hit the diagnosis.	false
HitPercentage	boolean	Hit rate.	100.0
AvgCpuTime	double	Average CPU time (in milliseconds) during the execution period.	1875.34
PlanExplain	object	Execution plan.
PlanJsonString	string	JSON string of the SQL execution plan.	{\"RootOperations\":[{\"Children\
Executions	long	Execution count.	1
PlanUnionHash	string	The internal unique identifier of the SQL execution plan in the diagnostic system.	"52c7c53**53e61b3f7586b17**ad"
QuerySql	string	The SQL for the query.	"select 1 from t"
Bounded	boolean	Whether to bind the execution plan.	false

Examples

Sample success responses

JSONformat

{
  "RequestId": "EE205C00-30E4-****-****-87E3A8A2AA0C",
  "Data": [
    {
      "PlanHash": 0,
      "Plans": [
        {
          "Uid": "AAAAAAAAAAI****AAFoT2QAF--7W****",
          "PlanId": 0,
          "FirstLoadTime": "2023-04-12T04:46:38Z",
          "PlanType": "LOCAL",
          "PlanHash": 0,
          "PlanSize": 0,
          "SchemaVersion": 0,
          "MergedVersion": 513,
          "ObServerId": 2,
          "ObDbId": 0,
          "ServerId": 2,
          "FirstLoadTimeUs": 0,
          "HitDiagnosis": false,
          "OutlineData": "/*+ BEGIN_OUTLINE_DATA INDEX(@\\\"SEL$1\\",
          "OutlineId": -1,
          "CollectTimeUs": 0,
          "Server": "i-bp1db1a9us038****jio",
          "HitPercentage": 100,
          "Executions": 1,
          "ExecPs": 0.31,
          "AvgDiskReads": 0,
          "AvgDiskWrites": 0,
          "AvgBufferGets": 30137.03,
          "AvgApplicationWaitTime": 0,
          "AvgConcurrencyWaitTime": 0,
          "AvgUserIoWaitTime": 0,
          "AvgRowProcessed": 1,
          "AvgElapsedTime": 1876.78,
          "AvgCpuTime": 1875.34,
          "LargeQueryPercentage": 0,
          "DelayedLargeQueryPercentage": 0,
          "TimeoutPercentage": 0,
          "TableScan": false,
          "PlanUnionHash": "\"52c7c53****53e61b3f7586b17****ad\""
        }
      ],
      "MergedVersion": 513,
      "FirstLoadTime": "2023-04-12T04:46:38Z",
      "PlanType": "LOCAL",
      "HitDiagnosis": 0,
      "HitPercentage": true,
      "AvgCpuTime": 1875.34,
      "PlanExplain": {
        "PlanJsonString": "{\\\"RootOperations\\\":[{\\\"Children\\"
      },
      "Executions": 1,
      "PlanUnionHash": "\"52c7c53****53e61b3f7586b17****ad\"",
      "QuerySql": "\"select 1 from t\"",
      "Bounded": false
    }
  ]
}

Error codes

For a list of error codes, visit the Service error codes.

Change history

Change time

Summary of changes

Operation

2024-04-15

The request parameters of the API has changed

see changesets

Change item	Change content
Input Parameters	The request parameters of the API has changed.
	Added Input Parameters: PlanUnionHash
	Added Input Parameters: ReturnBriefInfo

2023-08-21

The request parameters of the API has changed. The response structure of the API has changed

see changesets

Change item	Change content
Input Parameters	The request parameters of the API has changed.
	Added Input Parameters: DynamicSql
Output Parameters	The response structure of the API has changed.