DescribeOasTopSQLList-阿里云帮助中心

Queries the list of SQL execution performance data collected by the diagnostic system.

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

get

*All Resource

*

None

Request parameters

Parameter	Type	Required	Description	Example
InstanceId	string	Yes	OceanBase cluster ID.	ob317v4uif****
TenantId	string	Yes	Tenant ID.	t4louaeei****
StartTime	string	Yes	Start time for querying TOPSQL parameters. Use UTC time in the format YYYY-MM-DDThh:mm:ssZ.	2023-04-12T04:38:38Z
EndTime	string	Yes	End time for querying TOPSQL parameters. Use UTC time in the format YYYY-MM-DDThh:mm:ssZ. This time supports only UTC in the format: YYYY-MM-DDThh:mm:ssZ.	2023-04-12T05:38:38Z
DbName	string	No	Database name.	test_db
SearchKeyWord	string	No	Keyword query.	update
SearchParam	string	No	Parameter query.	cputime
SearchRule	string	No	Query rule.	>
SearchValue	string	No	Query value.	0.01
SqlId	string	No	SQL ID.	8D6E84**0B8FB1823D199E2CA1**
NodeIp	string	No	Node IP.	i-bp18l4****str4uk03
FilterCondition	string	No	Reference all fields using the @ symbol. For available fields, see the columns returned by the Query SQL performance statistics API.	@avgCpuTime > 20 and @executions > 100
AcceptLanguage	string	No	Language for returned information: zh: Chinese en: English	zh-CN
SqlTextLength	integer	No	Maximum length of the returned SQL text.	65535
DynamicSql	boolean	No	Indicates whether the SQL is dynamic. Note Specifies whether the provided SQL ID belongs to a dynamic SQL statement.	false
MergeDynamicSql	boolean	No	Indicates whether to merge dynamic SQL statements in the response. Note Specifies whether to aggregate results for SQL statements containing IN clauses.	true
CustomColumns	string	No	Custom field rules. Reference all fields using the @ symbol. Supported operators include `+、-、*、/`. Field names must start with a lowercase letter. For available fields, see the columns returned by the Query SQL performance statistics API.	@failCount/@executions

Response elements

Element	Type	Description	Example
	object	Response to the request.
RequestId	string	Request ID.	EE205C00-30E4-**-**-87E3A8A2AA0C
Data	array<object>	List of TopSQL information.
	array<object>	TopSQL information.
Executions	integer	Number of SQL executions.	100
RpcCount	number	Total number of SQL RPC calls.	100
RemotePlans	integer	Number of remote SQL executions.	100
MissPlans	integer	Number of times the SQL plan cache was missed.	100
MaxElapsedTime	number	Maximum SQL response time.	10000
TotalWaitTime	number	Total wait time in milliseconds during SQL execution. This corresponds to v$sql_audit.total_wait_time_micro (in milliseconds).	100000
ExecPs	number	SQL execution frequency.	0.31
MaxCpuTime	number	Maximum CPU time in milliseconds.	13641.9
CpuPercentage	number	CPU usage percentage.	100.0
ClientIp	string	Client IP address.	i-bp1db****38uemejio
UserName	string	Username.	test_user
DbName	string	Database name.	test_db
RetCode4012Count	integer	Number of occurrences of error code 4012 during the period.	0
RetCode4013Count	integer	Number of occurrences of error code 4013 during the period.	0
RetCode5001Count	integer	Number of occurrences of error code 5001 during the period.	0
RetCode5024Count	integer	Number of occurrences of error code 5024 during the period.	0
RetCode5167Count	integer	Number of occurrences of error code 5167 during the period.	0
RetCode5217Count	integer	Number of occurrences of error code 5217 during the period.	0
RetCode6002Count	integer	Number of occurrences of error code 6002 during the period.	0
FailPercentage	number	Error percentage during the period.	0.0
SumWaitTime	number	Total wait time in milliseconds.	9421.73
AvgWaitCount	number	Average number of waits.	0.0
AvgRpcCount	number	Average number of SQL RPC calls.	8.0
LocalPlanPercentage	number	Percentage of SQL local plans.	0.0
RemotePlanPercentage	number	Percentage of SQL remote plans.	0.0
DistPlanPercentage	number	Percentage of SQL distributed plans.	100.0
SumElapsedTime	number	Total SQL response time in milliseconds.	11452126.36
AvgNetTime	number	Average network transmission time in milliseconds during the period.	0.0
AvgExecutorRpcCount	number	Number of remote SQL executions. This corresponds to v$sl_audit.executor_rpc.	0.0
MissPlanPercentage	number	Percentage of SQL plan cache misses.	0.0
TableScanPercentage	number	Percentage of full table scans.	0.0
StrongConsistencyPercentage	number	Percentage of strong reads.	100.0
WeakConsistencyPercentage	number	Percentage of weak reads.	0.0
MaxAffectedRows	number	Maximum number of affected rows.	10000.0
MaxReturnRows	number	Maximum number of returned rows.	0.0
MaxWaitTime	number	Maximum wait time in milliseconds.	3.4
MaxApplicationWaitTime	number	Maximum application wait time in milliseconds.	0.0
MaxConcurrencyWaitTime	number	Maximum I/O wait time in milliseconds.	0.0
MaxUserIoWaitTime	number	Maximum UserIO event wait time in milliseconds during the period.	0.0
MaxDiskReads	number	Maximum number of disk reads.	0.0
AvgExpectedWorkerCount	number	Expected concurrency.	3
AvgUsedWorkerCount	number	Average number of threads used by SQL during the period.	3
SumLogicalReads	number	Total logical reads.	0.0
Server	string	OBServer.	i-bp1db1****8uemejio
ServerIp	string	OBServer IP.	i-bp1db1****8uemejio
ServerPort	integer	OBServer port.	389
SqlTextShort	string	SQL text (first 100 characters).	delete /+ XXX PARALLEL(4) / fro
SqlType	string	SQL type.	select
SqlId	string	SQL ID.	8D6E84**0B8FB1823D199E2CA1**
Inner	boolean	Indicates whether the SQL is internal.	false
WaitEvent	string	Longest wait event.	none
AvgAffectedRows	number	Average number of affected rows.	9978.75
AvgReturnRows	number	Average number of returned rows.	0.0
AvgPartitionCount	number	Average number of partitions accessed during the period.	1.0
FailCount	integer	Number of execution errors.	0
AvgWaitTime	number	Average wait time in milliseconds.	1442.49
AvgElapsedTime	number	Average response time in milliseconds during the period.	903.29
AvgCpuTime	number	Average CPU time in milliseconds.	1875.34
AvgNetWaitTime	number	Average network time in milliseconds.	0.0
AvgQueueTime	number	Average queue time in milliseconds.	0.01
AvgDecodeTime	number	Average parsing time in milliseconds.	0.0
AvgGetPlanTime	number	Average time to get the execution plan in milliseconds.	0.0
AvgExecuteTime	number	Average SQL execution time in milliseconds.	1895.7
AvgApplicationWaitTime	number	Average application wait time in milliseconds.	0.0
AvgConcurrencyWaitTime	number	Average concurrency wait time in milliseconds.	0.0
AvgUserIoWaitTime	number	Average user I/O time in milliseconds.	0.0
AvgScheduleTime	number	Average scheduling time in milliseconds.	0.0
AvgRowCacheHit	number	Average number of Row Cache hits.	0.0
AvgBloomFilterCacheHit	number	Average number of Bloom Filter Cache hits.	0.0
AvgBlockCacheHit	number	Average number of Block Cache hits.	0.0
AvgBlockIndexCacheHit	number	Average number of Block Index Cache hits.	0.0
AvgDiskReads	number	Average number of physical reads during the period.	0.0
RetryCount	integer	Number of retries.	0
AvgMemstoreReadRows	number	Average number of Memstore read rows.	0.0
AvgSsstoreReadRows	number	Average number of SSTable read rows.	0.0
AvgLogicalReads	number	Average logical reads.	0.0
DynamicSql	boolean	Indicates whether the SQL is dynamic.	false
SqlList	array<object>	List of SQL statements. Note This parameter appears only when mergeDynamicSql is set to true and the query contains an IN clause.
	object	Response to the request.
Executions	integer	Number of SQL executions.	100
RpcCount	number	Total number of SQL RPC calls.	100
RemotePlans	integer	Number of remote SQL executions.	100
MissPlans	integer	Number of times the SQL plan cache was missed.	100
MaxElapsedTime	number	Maximum SQL response time.	10000
TotalWaitTime	number	Total wait time in milliseconds during SQL execution. This corresponds to v$sql_audit.total_wait_time_micro (in milliseconds).	100000
ExecPs	number	SQL execution frequency.	0.31
MaxCpuTime	number	Maximum CPU time.	13641.9
CpuPercentage	number	CPU usage percentage.	100
ClientIp	string	Client IP address.	127...*
UserName	string	Username.	test_user
DbName	string	Database name.	test
RetCode4012Count	integer	Number of occurrences of error code 4012.	0
RetCode4013Count	integer	Number of occurrences of error code 4013.	0
RetCode5001Count	integer	Number of occurrences of error code 5001.	0
RetCode5024Count	integer	Number of occurrences of error code 5024.	0
RetCode5167Count	integer	Number of occurrences of error code 5167.	0
RetCode5217Count	integer	Number of occurrences of error code 5217.	0
RetCode6002Count	integer	Number of occurrences of error code 6002.	0
FailPercentage	number	SQL execution error rate.	0
SumWaitTime	number	Total wait time in milliseconds during the period. (waitTime, corresponding to v$sql_audit.wait_time_micro)	9421.73
AvgWaitCount	number	Average number of waits.	0
AvgRpcCount	number	Average number of SQL RPC calls.	8
LocalPlanPercentage	number	Percentage of SQL local plans.	0
RemotePlanPercentage	number	Percentage of SQL remote plans.	0
DistPlanPercentage	number	Percentage of SQL distributed plans.	100
SumElapsedTime	number	Total SQL response time in milliseconds.	11452126.36
AvgNetTime	number	Average SQL network I/O time in milliseconds.	0
AvgExecutorRpcCount	number	Number of remote SQL executions. This corresponds to v$sl_audit.executor_rpc.	0
MissPlanPercentage	number	Percentage of SQL plan cache misses.	0
TableScanPercentage	number	Percentage of full table scans.	0
StrongConsistencyPercentage	number	Percentage of strong reads.	100
WeakConsistencyPercentage	number	Percentage of weak reads.	0
MaxAffectedRows	number	Maximum number of affected rows.	10000
MaxReturnRows	number	Maximum number of returned rows.	0
MaxWaitTime	number	Maximum wait time in milliseconds.	3.4
MaxApplicationWaitTime	number	Maximum application wait time in milliseconds.	0
MaxConcurrencyWaitTime	number	Maximum concurrency wait time in milliseconds.	0
MaxUserIoWaitTime	number	Maximum I/O wait time in milliseconds.	0
MaxDiskReads	number	Maximum number of disk reads.	0
AvgExpectedWorkerCount	number	Expected concurrency.	3
AvgUsedWorkerCount	number	Average number of threads used by SQL during the period.	3
SumLogicalReads	number	Total logical reads.	0
Server	string	OBServer	i-bp1db1****8uemejio
ServerIp	string	OBServer IP	i-bp1db1****8uemejio
ServerPort	integer	OBServer port.	389
SqlTextShort	string	SQL text (first 100 characters).	delete /+ XXX PARALLEL(4) / fro
SqlType	string	SQL type.	select
SqlId	string	SQL ID.	8D6E84**0B8FB1823D199E2CA1**
Inner	boolean	Indicates whether the SQL is internal.	false
WaitEvent	string	Longest wait event.	none
AvgAffectedRows	number	Average number of affected rows.	9978.75
AvgReturnRows	number	Average number of returned rows.	0
AvgPartitionCount	number	Average number of partitions accessed.	1
FailCount	integer	Number of execution errors.	0
AvgWaitTime	number	Average wait time in milliseconds.	1442.49
AvgElapsedTime	number	Average response time in milliseconds.	903.29
AvgCpuTime	number	Average CPU time in milliseconds.	1875.34
AvgNetWaitTime	number	Average network time in milliseconds.	0
AvgQueueTime	number	Average queue time in milliseconds.	0.01
AvgDecodeTime	number	Average parsing time in milliseconds.	0
AvgGetPlanTime	number	Average time to get the execution plan in milliseconds.	0
AvgExecuteTime	number	Average SQL execution time in milliseconds.	1895.7
AvgApplicationWaitTime	number	Average application wait time in milliseconds.	0
AvgConcurrencyWaitTime	number	Average concurrency wait time in milliseconds.	0
AvgUserIoWaitTime	number	Average user I/O time in milliseconds.	0
AvgScheduleTime	number	Average scheduling time in milliseconds.	0
AvgRowCacheHit	number	Average number of Row Cache hits.	0
AvgBloomFilterCacheHit	number	Average number of Bloom Filter Cache hits.	0
AvgBlockCacheHit	number	Average number of Block Cache hits.	0
AvgBlockIndexCacheHit	number	Average number of Block Index Cache hits.	0
AvgDiskReads	number	Average number of disk reads.	0
RetryCount	integer	Number of retries.	2
AvgMemstoreReadRows	number	Average number of Memstore read rows.	0
AvgSsstoreReadRows	number	Average number of SSTable read rows.	0
AvgLogicalReads	number	Average logical reads.	0
AvgDbTime	number	Average database time in milliseconds.	100
SumDbTime	number	Total database time in milliseconds.	100
FormatSqlId	string	FormatSqlId
LastFailCode	integer	Error code from the last failed execution.	-4012
AvgDbTime	number	Average database time in milliseconds.	100
SumDbTime	number	Total database time in milliseconds.	100
CustomColumns	object	Custom columns.
Expression	string	Expression for the custom column.	@failCount/@executions
Value	string	Value calculated from the expression.	0.0
ObDbId	integer	Database ID.	11006111****4828
FormatSqlId	string	FormatSqlId

Examples

Success response

JSON format

{
  "RequestId": "EE205C00-30E4-****-****-87E3A8A2AA0C",
  "Data": [
    {
      "Executions": 100,
      "RpcCount": 100,
      "RemotePlans": 100,
      "MissPlans": 100,
      "MaxElapsedTime": 10000,
      "TotalWaitTime": 100000,
      "ExecPs": 0.31,
      "MaxCpuTime": 13641.9,
      "CpuPercentage": 100,
      "ClientIp": "i-bp1db****38uemejio",
      "UserName": "test_user",
      "DbName": "test_db",
      "RetCode4012Count": 0,
      "RetCode4013Count": 0,
      "RetCode5001Count": 0,
      "RetCode5024Count": 0,
      "RetCode5167Count": 0,
      "RetCode5217Count": 0,
      "RetCode6002Count": 0,
      "FailPercentage": 0,
      "SumWaitTime": 9421.73,
      "AvgWaitCount": 0,
      "AvgRpcCount": 8,
      "LocalPlanPercentage": 0,
      "RemotePlanPercentage": 0,
      "DistPlanPercentage": 100,
      "SumElapsedTime": 11452126.36,
      "AvgNetTime": 0,
      "AvgExecutorRpcCount": 0,
      "MissPlanPercentage": 0,
      "TableScanPercentage": 0,
      "StrongConsistencyPercentage": 100,
      "WeakConsistencyPercentage": 0,
      "MaxAffectedRows": 10000,
      "MaxReturnRows": 0,
      "MaxWaitTime": 3.4,
      "MaxApplicationWaitTime": 0,
      "MaxConcurrencyWaitTime": 0,
      "MaxUserIoWaitTime": 0,
      "MaxDiskReads": 0,
      "AvgExpectedWorkerCount": 3,
      "AvgUsedWorkerCount": 3,
      "SumLogicalReads": 0,
      "Server": "i-bp1db1****8uemejio",
      "ServerIp": "i-bp1db1****8uemejio",
      "ServerPort": 389,
      "SqlTextShort": "delete /*+ XXX PARALLEL(4) */ fro",
      "SqlType": "select ",
      "SqlId": "8D6E84****0B8FB1823D199E2CA1****",
      "Inner": false,
      "WaitEvent": "none",
      "AvgAffectedRows": 9978.75,
      "AvgReturnRows": 0,
      "AvgPartitionCount": 1,
      "FailCount": 0,
      "AvgWaitTime": 1442.49,
      "AvgElapsedTime": 903.29,
      "AvgCpuTime": 1875.34,
      "AvgNetWaitTime": 0,
      "AvgQueueTime": 0.01,
      "AvgDecodeTime": 0,
      "AvgGetPlanTime": 0,
      "AvgExecuteTime": 1895.7,
      "AvgApplicationWaitTime": 0,
      "AvgConcurrencyWaitTime": 0,
      "AvgUserIoWaitTime": 0,
      "AvgScheduleTime": 0,
      "AvgRowCacheHit": 0,
      "AvgBloomFilterCacheHit": 0,
      "AvgBlockCacheHit": 0,
      "AvgBlockIndexCacheHit": 0,
      "AvgDiskReads": 0,
      "RetryCount": 0,
      "AvgMemstoreReadRows": 0,
      "AvgSsstoreReadRows": 0,
      "AvgLogicalReads": 0,
      "DynamicSql": false,
      "SqlList": [
        {
          "Executions": 100,
          "RpcCount": 100,
          "RemotePlans": 100,
          "MissPlans": 100,
          "MaxElapsedTime": 10000,
          "TotalWaitTime": 100000,
          "ExecPs": 0.31,
          "MaxCpuTime": 13641.9,
          "CpuPercentage": 100,
          "ClientIp": "127.*.*.*",
          "UserName": "test_user",
          "DbName": "test",
          "RetCode4012Count": 0,
          "RetCode4013Count": 0,
          "RetCode5001Count": 0,
          "RetCode5024Count": 0,
          "RetCode5167Count": 0,
          "RetCode5217Count": 0,
          "RetCode6002Count": 0,
          "FailPercentage": 0,
          "SumWaitTime": 9421.73,
          "AvgWaitCount": 0,
          "AvgRpcCount": 8,
          "LocalPlanPercentage": 0,
          "RemotePlanPercentage": 0,
          "DistPlanPercentage": 100,
          "SumElapsedTime": 11452126.36,
          "AvgNetTime": 0,
          "AvgExecutorRpcCount": 0,
          "MissPlanPercentage": 0,
          "TableScanPercentage": 0,
          "StrongConsistencyPercentage": 100,
          "WeakConsistencyPercentage": 0,
          "MaxAffectedRows": 10000,
          "MaxReturnRows": 0,
          "MaxWaitTime": 3.4,
          "MaxApplicationWaitTime": 0,
          "MaxConcurrencyWaitTime": 0,
          "MaxUserIoWaitTime": 0,
          "MaxDiskReads": 0,
          "AvgExpectedWorkerCount": 3,
          "AvgUsedWorkerCount": 3,
          "SumLogicalReads": 0,
          "Server": "i-bp1db1****8uemejio",
          "ServerIp": "i-bp1db1****8uemejio",
          "ServerPort": 389,
          "SqlTextShort": "delete /*+ XXX PARALLEL(4) */ fro",
          "SqlType": "select",
          "SqlId": "8D6E84****0B8FB1823D199E2CA1****",
          "Inner": false,
          "WaitEvent": "none",
          "AvgAffectedRows": 9978.75,
          "AvgReturnRows": 0,
          "AvgPartitionCount": 1,
          "FailCount": 0,
          "AvgWaitTime": 1442.49,
          "AvgElapsedTime": 903.29,
          "AvgCpuTime": 1875.34,
          "AvgNetWaitTime": 0,
          "AvgQueueTime": 0.01,
          "AvgDecodeTime": 0,
          "AvgGetPlanTime": 0,
          "AvgExecuteTime": 1895.7,
          "AvgApplicationWaitTime": 0,
          "AvgConcurrencyWaitTime": 0,
          "AvgUserIoWaitTime": 0,
          "AvgScheduleTime": 0,
          "AvgRowCacheHit": 0,
          "AvgBloomFilterCacheHit": 0,
          "AvgBlockCacheHit": 0,
          "AvgBlockIndexCacheHit": 0,
          "AvgDiskReads": 0,
          "RetryCount": 2,
          "AvgMemstoreReadRows": 0,
          "AvgSsstoreReadRows": 0,
          "AvgLogicalReads": 0,
          "AvgDbTime": 100,
          "SumDbTime": 100,
          "FormatSqlId": ""
        }
      ],
      "LastFailCode": -4012,
      "AvgDbTime": 100,
      "SumDbTime": 100,
      "CustomColumns": {
        "Expression": "@failCount/@executions",
        "Value": "0.0"
      },
      "ObDbId": 0,
      "FormatSqlId": ""
    }
  ]
}

Error codes

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.