DescribeOasSQLPlans-阿里云帮助中心

Retrieves SQL execution plans from the diagnosis system by SQLID.

Try it now

Try this API in OpenAPI Explorer, no manual signing needed. Successful calls auto-generate SDK code matching your parameters. Download it with built-in credential security for local usage.

Test

RAM authorization

The table below describes the authorization required to call this API. You can define it in a Resource Access Management (RAM) policy. The table's columns are detailed below:

Action: The actions can be used in the Action element of RAM permission policy statements to grant permissions to perform the operation.
API: The API that you can call to perform the action.
Access level: The predefined level of access granted for each API. Valid values: create, list, get, update, and delete.
Resource type: The type of the resource that supports authorization to perform the action. It indicates if the action supports resource-level permission. The specified resource must be compatible with the action. Otherwise, the policy will be ineffective.
- For APIs with resource-level permissions, required resource types are marked with an asterisk (*). Specify the corresponding Alibaba Cloud Resource Name (ARN) in the Resource element of the policy.
- For APIs without resource-level permissions, it is shown as All Resources. Use an asterisk (*) in the Resource element of the policy.
Condition key: The condition keys defined by the service. The key allows for granular control, applying to either actions alone or actions associated with specific resources. In addition to service-specific condition keys, Alibaba Cloud provides a set of common condition keys applicable across all RAM-supported services.
Dependent action: The dependent actions required to run the action. To complete the action, the RAM user or the RAM role must have the permissions to perform all dependent actions.

Action

Access level

Resource type

Condition key

Dependent action

oceanbase:DescribeOasSQLPlans

get

*All Resource

*

None

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 of the time range to query SQL execution plans. The time must be in UTC and in the `YYYY-MM-DDThh:mm:ssZ` format.	2023-04-12T04:38:38Z
EndTime	string	Yes	The end of the time range to query SQL execution plans. The time must be in UTC and in the `YYYY-MM-DDThh:mm:ssZ` format.	2023-04-12T05:38:38Z
DbName	string	Yes	The name of the database.	test_db
SqlId	string	Yes	The SQL ID.	8D6E84**0B8FB1823D199E2CA1**
AcceptLanguage	string	No	The language of the response. Valid values: `zh-CN`: Chinese `en-US`: English	zh-CN
DynamicSql	boolean	No	Specifies whether the SQL statement is a dynamic SQL.	false
PlanUnionHash	string	No	The unique identifier of the execution plan.	c4e9aaa797428df9a5a41828********
ReturnBriefInfo	boolean	No	Specifies whether to return brief information about the execution plan.	true
FormatSqlId	string	No	The formatted SQL ID.

Response elements

Element	Type	Description	Example
	object	The data returned.
RequestId	string	The request ID.	EE205C00-30E4-**-**-87E3A8A2AA0C
Data	array<object>	A list of SQL execution plans.
	array<object>	An SQL execution plan.
PlanHash	string	The unique identifier for the SQL execution plan in the diagnostic system.	1758**24913166**
Plans	array<object>	A list of execution plans.
	object	The details of an execution plan.
Uid	string	The plan's unique identifier.	AAAAAAAAAAI**AAFoT2QAF--7W**
PlanId	integer	The plan ID.	590****
FirstLoadTime	string	The first load time of the execution plan.	2023-04-12T04:46:38Z
PlanType	string	The plan type. Valid values: `LOCAL`, `REMOTE`, and `DIST`.	LOCAL
PlanHash	string	The unique identifier for the SQL execution plan in the diagnostic system.	-86290582****886880
PlanSize	integer	The plan size.	10****
SchemaVersion	integer	The schema version.	16838****3550464
MergedVersion	integer	The merged version.	513
ObServerId	integer	The server ID.	2
ObDbId	integer	The database ID.	11006****828
ServerId	integer	The backend server instance ID.	2
FirstLoadTimeUs	integer	The first load time, in microseconds.	1684****03289441
HitDiagnosis	boolean	Indicates whether the plan triggered a diagnosis.	false
OutlineData	string	The `outline_data` field of the SQL execution plan.	/*+ BEGIN_OUTLINE_DATA INDEX(@\"SEL$1\
OutlineId	integer	The outline ID.	-1
CollectTimeUs	integer	The collection time, in microseconds.	1684368****00000
Server	string	The server where the SQL statement was executed.	i-bp1db1a9us038****jio
HitPercentage	number	The hit percentage.	100.0
Executions	integer	The total number of executions within the specified time range.	1
ExecPs	number	The average number of executions per second within the specified time range.	0.31
AvgDiskReads	number	The average number of physical reads within the specified time range.	0.0
AvgDiskWrites	number	The average number of physical writes within the specified time range.	0.0
AvgBufferGets	number	The average number of buffer gets within the specified time range.	30137.03
AvgApplicationWaitTime	number	The average application wait time within the specified time range, in milliseconds.	0.0
AvgConcurrencyWaitTime	number	The average concurrency wait time within the specified time range, in milliseconds.	0.0
AvgUserIoWaitTime	number	The average user I/O wait time within the specified time range, in milliseconds.	0.0
AvgRowProcessed	number	The average number of rows processed within the specified time range.	1.0
AvgElapsedTime	number	The average elapsed time within the specified time range, in milliseconds.	1876.78
AvgCpuTime	number	The average CPU time within the specified time range, in milliseconds.	1875.34
LargeQueryPercentage	number	The percentage of queries that are long-running.	0.0
DelayedLargeQueryPercentage	number	The percentage of long-running queries that were delayed.	0.0
TimeoutPercentage	number	The timeout percentage.	0.0
TableScan	boolean	Indicates whether the plan includes a full table scan.	false
PlanUnionHash	string	The identifier for a group of similar SQL execution plans.	"52c7c53**53e61b3f7586b17**ad"
SqlId	string	The SQL ID.	46939C87ECA**95ED0FF64F44B**
SqlText	string	The text of the SQL statement.
MergedVersion	integer	The merged version.	513
FirstLoadTime	string	The first load time.	2023-04-12T04:46:38Z
PlanType	string	The plan type.	LOCAL
HitDiagnosis	boolean	Indicates whether the plan triggered a diagnosis.	false
HitPercentage	number	The hit percentage.	100.0
AvgCpuTime	number	The average CPU time within the specified time range, in milliseconds.	1875.34
PlanExplain	object	The execution plan.
PlanJsonString	string	The JSON string of the SQL execution plan.	"PlanJsonString":"{\"RootOperations\":[{\"Children\":[{\"Children\":[{\"Cost\":2000,\"ObjectName\":\"TR\",\"Operator\":\"PHY_TABLE_SCAN\",\"Property\":\"table_rows:8593, physical_range_rows:85, logical_range_rows:85, index_back_rows:0, output_rows:85, est_method:default_stat, avaiable_index_name[ALL_VIRTUAL_AGENT]\",\"Rows\":86},{\"Children\":[{\"Cost\":2000,\"ObjectName\":\"NA\",\"Operator\":\"PHY_TABLE_SCAN\",\"Property\":\"table_rows:1750, physical_range_rows:17, logical_range_rows:17, index_back_rows:0, output_rows:17, est_method:default_stat, avaiable_index_name[ALL_VIRTUAL_AGENT]\",\"Rows\":18}],\"Cost\":2013,\"ObjectName\":\"NULL\",\"Operator\":\"PHY_SORT\",\"Property\":\"NULL\",\"Rows\":18}],\"Cost\":4024,\"ObjectName\":\"NULL\",\"Operator\":\"PHY_MERGE_JOIN\",\"Property\":\"NULL\",\"Rows\":26}],\"Cost\":4025,\"ObjectName\":\"NULL\",\"Operator\":\"PHY_SCALAR_AGGREGATE\",\"Property\":\"NULL\",\"Rows\":1}]}"
Executions	integer	The total number of executions.	1
PlanUnionHash	string	The identifier for a group of similar SQL execution plans.	"52c7c53**53e61b3f7586b17**ad"
QuerySql	string	The query statement. Note This parameter is not used and is always empty.	"select 1 from t"
Bounded	boolean	Indicates whether execution plan binding is enabled for this execution plan.	false
TableScan	boolean	Indicates whether the plan includes a full table scan.	false
OutlineId	string	The outline ID that the plan uses.	3********
OutlineStatus	string	The status of the outline used by the plan. `Effective` indicates that the `outline_data` of the plan perfectly matches the `outline_content` of the outline. `Unknown` indicates that the status cannot be determined.	已生效
ByFormatSqlId	boolean	Indicates whether fuzzy binding is enabled.

Examples

Success response

JSON format

{
  "RequestId": "EE205C00-30E4-****-****-87E3A8A2AA0C",
  "Data": [
    {
      "PlanHash": "1758****24913166****",
      "Plans": [
        {
          "Uid": "AAAAAAAAAAI****AAFoT2QAF--7W****",
          "PlanId": 0,
          "FirstLoadTime": "2023-04-12T04:46:38Z",
          "PlanType": "LOCAL",
          "PlanHash": "-86290582****886880",
          "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\"",
          "SqlId": "46939C87ECA****95ED0FF64F44B****",
          "SqlText": ""
        }
      ],
      "MergedVersion": 513,
      "FirstLoadTime": "2023-04-12T04:46:38Z",
      "PlanType": "LOCAL",
      "HitDiagnosis": false,
      "HitPercentage": 100,
      "AvgCpuTime": 1875.34,
      "PlanExplain": {
        "PlanJsonString": "\"PlanJsonString\":\"{\\\"RootOperations\\\":[{\\\"Children\\\":[{\\\"Children\\\":[{\\\"Cost\\\":2000,\\\"ObjectName\\\":\\\"TR\\\",\\\"Operator\\\":\\\"PHY_TABLE_SCAN\\\",\\\"Property\\\":\\\"table_rows:8593, physical_range_rows:85, logical_range_rows:85, index_back_rows:0, output_rows:85, est_method:default_stat, avaiable_index_name[ALL_VIRTUAL_AGENT]\\\",\\\"Rows\\\":86},{\\\"Children\\\":[{\\\"Cost\\\":2000,\\\"ObjectName\\\":\\\"NA\\\",\\\"Operator\\\":\\\"PHY_TABLE_SCAN\\\",\\\"Property\\\":\\\"table_rows:1750, physical_range_rows:17, logical_range_rows:17, index_back_rows:0, output_rows:17, est_method:default_stat, avaiable_index_name[ALL_VIRTUAL_AGENT]\\\",\\\"Rows\\\":18}],\\\"Cost\\\":2013,\\\"ObjectName\\\":\\\"NULL\\\",\\\"Operator\\\":\\\"PHY_SORT\\\",\\\"Property\\\":\\\"NULL\\\",\\\"Rows\\\":18}],\\\"Cost\\\":4024,\\\"ObjectName\\\":\\\"NULL\\\",\\\"Operator\\\":\\\"PHY_MERGE_JOIN\\\",\\\"Property\\\":\\\"NULL\\\",\\\"Rows\\\":26}],\\\"Cost\\\":4025,\\\"ObjectName\\\":\\\"NULL\\\",\\\"Operator\\\":\\\"PHY_SCALAR_AGGREGATE\\\",\\\"Property\\\":\\\"NULL\\\",\\\"Rows\\\":1}]}\""
      },
      "Executions": 1,
      "PlanUnionHash": "\"52c7c53****53e61b3f7586b17****ad\"",
      "QuerySql": "\"select 1 from t\"",
      "Bounded": false,
      "TableScan": false,
      "OutlineId": "3********",
      "OutlineStatus": "已生效",
      "ByFormatSqlId": false
    }
  ]
}

Error codes

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.