DescribeOasAnomalySQLList-阿里云帮助中心

This API returns a list of SQL statements flagged by the diagnostic system for potential performance issues.

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

get

*All Resource

*

None

Request parameters

Parameter	Type	Required	Description	Example
InstanceId	string	Yes	The ID of the OceanBase instance.	ob317v4uif****
TenantId	string	Yes	The tenant ID.	t4louaeei****
StartTime	string	Yes	The beginning of the query period. The time must be in UTC and formatted as `YYYY-MM-DDTHH:mm:ssZ`.	2023-04-12T04:38:38Z
EndTime	string	Yes	The end of the query period. The time must be in UTC and formatted as `YYYY-MM-DDTHH:mm:ssZ`.	2023-04-12T05:38:38Z
DbName	string	No	The database name.	db_****
SearchKeyWord	string	No	The search keyword.	update
SearchParam	string	No	The performance metric to search by.	cputime
SearchRule	string	No	The search operator. Valid values: `=`, `>`, `>=`, `<`, and `<=`.	>
SearchValue	string	No	The search value.	0.01
SqlId	string	No	The SQL ID.	8D6E84**0B8FB1823D199E2CA1**
NodeIp	string	No	The IP address of the node.	i-bp19y05uq6x*********
FilterCondition	string	No	Use the at sign (@) to reference fields. For a list of available fields, see all columns returned by the Query performance statistics for an SQL statement API.	@avgCpuTime > 20 and @executions > 100
AcceptLanguage	string	No	The language of the response. Defaults to `zh-CN` for services in Chinese mainland and `en-US` for other regions.	zh-CN
SqlTextLength	integer	No	The maximum length of the returned SQL text.	65535
Current	integer	No	The page number to return.	1
PageSize	integer	No	The number of entries to return on each page. Minimum value: 1. Default value: 10.	10
DynamicSql	boolean	No	Indicates if the SQL statement specified by `SqlId` is dynamic SQL. Note	false
MergeDynamicSql	boolean	No	Whether to merge the results of dynamic SQL statements. Note This option aggregates SQL statements that are identical except for the values in their `IN` clauses.	false

Response elements

Element	Type	Description	Example
	object	The response to the request.
Data	array<object>	Suspicious SQL information list.
	array<object>	Suspicious SQL information.
AvgCpuTime	number	Average CPU time of suspicious SQL.	100.24
AvgElapsedTime	number	Average response time of suspicious SQL.	100.28
AvgGetPlanTime	number	The average time to obtain execution plans for suspicious SQL.	0
CpuTime	number	CPU time of suspicious SQL.	100.23
DbName	string	Database name.	db_***
DiagTypes	array	Diagnosis type.
	string	Diagnosis type.	[ "CPU_TIME_PROPORTION_HIGH" ]
Diagnosis	string	Diagnosis details. Note This parameter is deprecated.	-
Executions	number	The total number of executions of suspicious SQL.	1
LastExecutedTime	string	The last time the suspicious SQL was executed.	2023-04-12T04:38:38Z
RiskLevel	string	Risk level.	high
SqlId	string	SQL ID.	8D6E84**0B8FB1823D199E2CA1**
SqlTextShort	string	SQL text prefix.	delete /+ XXX PARALLEL(4) / from
Suggestion	string	Recommendations for suspicious SQL.	review
SumElapsedTime	number	The total response time of suspicious SQL.	11452126.36
UserName	string	Username.	test_user
DynamicSql	boolean	Whether the SQL is dynamic.	false
SqlList	array<object>	SQL list.
	object	SQL list.
AvgCpuTime	number	Average CPU time.	100.24
AvgElapsedTime	number	Average response time.	100.28
AvgGetPlanTime	number	Average plan generation time.	0
CpuTime	number	Total CPU time.	100.23
DbName	string	Database name.	test_hsp****eway
DiagTypes	array	Diagnosis type.
	string	Diagnosis type.	[ "CPU_TIME_PROPORTION_HIGH" ]
Diagnosis	string	Diagnosis details. Note This parameter is deprecated.	-
Executions	number	Total number of executions.	100
LastExecutedTime	string	Last run time.	2023-04-12T04:38:38Z
RiskLevel	string	Risk level.	LOW
SqlId	string	SQL ID	3A645**789F13DE0CF6D084FF9**
SqlTextShort	string	SQL text.	select * from test
Suggestion	string	Suggestions for suspicious SQL.	{'Role': 'eSG', 'Result': '100', 'Suggestion': 'TRACER_OTHER_ERR'}
SumElapsedTime	number	Total response time.	11452126.36
UserName	string	Username.	test_user
AvgDbTime	number	Average DB time.	100
SumDbTime	number	Total DB time.	100
AvgRetryCount	number	Average retry count.	4
SumRetryCount	number	Total retry count.	6
AvgLogicalReads	number	Average logical read count.	25
FormatSqlId	string	FormatSqlId
AvgDbTime	number	Average DB time.	100
SumDbTime	number	total DB time	100
AvgRetryCount	number	Average retry count.	4
SumRetryCount	number	Total retry count.	6
AvgLogicalReads	number	Average logical read count.	25
FormatSqlId	string	FormatSqlId
RequestId	string	Request ID.	473469-AA6F-4D-B3DB-A***********
TotalCount	integer	Total count.	9

Examples

Success response

JSON format

{
  "Data": [
    {
      "AvgCpuTime": 100.24,
      "AvgElapsedTime": 100.28,
      "AvgGetPlanTime": 0,
      "CpuTime": 100.23,
      "DbName": "db_***",
      "DiagTypes": [
        "[\n      \"CPU_TIME_PROPORTION_HIGH\"\n]"
      ],
      "Diagnosis": "-",
      "Executions": 1,
      "LastExecutedTime": "2023-04-12T04:38:38Z",
      "RiskLevel": "high",
      "SqlId": "8D6E84****0B8FB1823D199E2CA1****",
      "SqlTextShort": "delete /*+ XXX PARALLEL(4) */ from",
      "Suggestion": "review",
      "SumElapsedTime": 11452126.36,
      "UserName": "test_user",
      "DynamicSql": false,
      "SqlList": [
        {
          "AvgCpuTime": 100.24,
          "AvgElapsedTime": 100.28,
          "AvgGetPlanTime": 0,
          "CpuTime": 100.23,
          "DbName": "test_hsp****eway",
          "DiagTypes": [
            "[\n          \"CPU_TIME_PROPORTION_HIGH\"\n        ]"
          ],
          "Diagnosis": "-",
          "Executions": 100,
          "LastExecutedTime": "2023-04-12T04:38:38Z",
          "RiskLevel": "LOW",
          "SqlId": "3A645****789F13DE0CF6D084FF9****",
          "SqlTextShort": "select * from test",
          "Suggestion": "{'Role': 'eSG', 'Result': '100', 'Suggestion': 'TRACER_OTHER_ERR'}",
          "SumElapsedTime": 11452126.36,
          "UserName": "test_user",
          "AvgDbTime": 100,
          "SumDbTime": 100,
          "AvgRetryCount": 4,
          "SumRetryCount": 6,
          "AvgLogicalReads": 25,
          "FormatSqlId": ""
        }
      ],
      "AvgDbTime": 100,
      "SumDbTime": 100,
      "AvgRetryCount": 4,
      "SumRetryCount": 6,
      "AvgLogicalReads": 25,
      "FormatSqlId": ""
    }
  ],
  "RequestId": "473469**-AA6F-4D**-B3DB-A***********",
  "TotalCount": 9
}

Error codes

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.