Retrieve SQL Tuning Advice & Optimize Query Performance via DescribeSQLTuningAdvices - ApsaraDB for OceanBase - Alibaba Cloud - ApsaraDB for OceanBase (Deprecated)

You can call this operation to query SQL optimization suggestions for two purposes: creating indexes and binding execution plans.

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:DescribeSQLTuningAdvices

get

*All Resource

*

None

Request parameters

Parameter	Type	Required	Description	Example
InstanceId	string	Yes	The ID of the OceanBase cluster.	obsdf43****
TenantId	string	Yes	The ID of the tenant.	tsdc2s2****
DbName	string	Yes	The name of the database.	testdb
SqlId	string	Yes	The ID of the SQL statement.	123456
StartTime	string	Yes	The start time.	2023-04-12T04:38:38Z
EndTime	string	Yes	The end time.	2023-04-12T05:38:38Z
AcceptLanguage	string	No	The supported language. Valid values: zh-CN en-US	zh-CN

Response elements

Element	Type	Description	Example
	object	The return result of the request.
RequestId	string	The ID of the request.	473469C7-AA6F-4DC5-B3DB-A3DC0DE3C83E
Data	array<object>	The returned data.
	array<object>	The information about the execution plans of the SQL statement.
Type	string	The optimization type.	PLAN_BINDING/INDEX_CREATION
ColumnNames	string	The sequence of index columns.	c1;c2;c3
LocalityType	string	The type of the index.	Local/Global
Columns	array<object>	The list of index columns.
	object	The information about index columns.
ColumnName	string	The name of the column.	xxxx
Ndv	number	The NDV of the column.	100
MinValue	string	The minimum value of the column.	1
MaxValue	string	The maximum value of the column.	100
DbName	string	The name of the database.	test_db
Table	string	The name of the table.	test_table
Plan	object	The execution plan.
Executions	integer	The total number of executions within the specified period of time.	2062
AvgCpuTime	number	The average CPU time within the specified period of time, in milliseconds.	100.36
PlanHash	string	The unique identifier of the execution plan in the diagnostic system.	-86290582****886880
TimeoutPercentage	number	The percentage of executions with a timeout error.	0
AvgConcurrencyWaitTime	number	The average amount of time spent on a Concurrency wait event within the specified period of time, in milliseconds.	0
FirstLoadTimeUs	integer	The time when the execution plan was loaded for the first time.	1672367130576163
AvgDiskWrites	number	The average number of physical writes within the specified period of time.	0
AvgUserIoWaitTime	number	The average amount of time spent on a UserIo wait event within the specified period of time, in milliseconds.	0
PlanSize	integer	The size of the execution plan.	49216
ServerSn	string	The ID of the node.	i-bp1***sw64dhb***
OutlineData	string	The outline_data field of the execution plan.	xxxxx
PlanType	string	The type of the execution plan. Valid values: LOCAL, REMOTE, and DIST.	LOCAL
ObDbId	integer	The ID of the database.	1108307720799259
HitPercentage	number	The hit rate.	66.8
AvgBufferGets	number	The average number of cache reads within the specified period of time.	0
ExecPs	number	The average number of executions per second within the specified period of time.	1.45
DelayedLargeQueryPercentage	number	The percentage of delayed long-running queries.	0
TableScan	boolean	Indicates whether full table scan is performed.	false
LargeQueryPercentage	number	The percentage of completed long-running queries.	0
SchemaVersion	integer	The version of the schema.	1672367130529680
PlanId	integer	The ID of the execution plan.	818
FirstLoadTime	string	The time when the execution plan was loaded for the first time.	2022-12-30T02:25:30.576Z
AvgRowProcessed	number	The average wait time of row processing within the specified period of time, in milliseconds.	1
MergedVersion	integer	The version of the major compaction.	3
HitDiagnosis	boolean	Indicates whether the diagnosis is hit.	false
Uid	string	The unique identifier of the execution plan.	AAAAAAAAAAEAAAAAAAADMgAF8QJQwIUj
AvgApplicationWaitTime	number	The average amount of time spent on an Application wait event within the specified period of time, in milliseconds.	0
CollectTimeUs	integer	The period of time during which data is collected.	1672358400000000
AvgElapsedTime	number	The average interval wait time within the specified period of time, in milliseconds.	713.62
ObServerId	integer	The ID of the server.	1
OutlineId	integer	The ID of the outline.	1
AvgDiskReads	number	The average number of physical reads within the specified period of time.	0

Examples

Success response

JSON format

{
  "RequestId": "473469C7-AA6F-4DC5-B3DB-A3DC0DE3C83E",
  "Data": [
    {
      "Type": "PLAN_BINDING/INDEX_CREATION",
      "ColumnNames": "c1;c2;c3",
      "LocalityType": "Local/Global",
      "Columns": [
        {
          "ColumnName": "xxxx",
          "Ndv": 100,
          "MinValue": "1",
          "MaxValue": "100"
        }
      ],
      "DbName": "test_db",
      "Table": "test_table",
      "Plan": {
        "Executions": 2062,
        "AvgCpuTime": 100.36,
        "PlanHash": "-86290582****886880",
        "TimeoutPercentage": 0,
        "AvgConcurrencyWaitTime": 0,
        "FirstLoadTimeUs": 1672367130576163,
        "AvgDiskWrites": 0,
        "AvgUserIoWaitTime": 0,
        "PlanSize": 49216,
        "ServerSn": "i-bp1*****sw64dhb*****",
        "OutlineData": "xxxxx",
        "PlanType": "LOCAL",
        "ObDbId": 1108307720799259,
        "HitPercentage": 66.8,
        "AvgBufferGets": 0,
        "ExecPs": 1.45,
        "DelayedLargeQueryPercentage": 0,
        "TableScan": false,
        "LargeQueryPercentage": 0,
        "SchemaVersion": 1672367130529680,
        "PlanId": 818,
        "FirstLoadTime": "2022-12-30T02:25:30.576Z",
        "AvgRowProcessed": 1,
        "MergedVersion": 3,
        "HitDiagnosis": false,
        "Uid": "AAAAAAAAAAEAAAAAAAADMgAF8QJQwIUj",
        "AvgApplicationWaitTime": 0,
        "CollectTimeUs": 1672358400000000,
        "AvgElapsedTime": 713.62,
        "ObServerId": 1,
        "OutlineId": 1,
        "AvgDiskReads": 0
      }
    }
  ]
}

Error codes

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.