DescribeOasSlowSQLList-阿里云帮助中心

This operation retrieves a list of slow SQL statements.

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

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 historical slow SQL data. This time must be in UTC format: YYYY-MM-DDThh:mm:ssZ.	2023-04-12T04:38:38Z
EndTime	string	Yes	End time for querying slow SQL data. This time must be in UTC format: YYYY-MM-DDThh:mm:ssZ.	2023-04-12T05:38:38Z
DbName	string	No	Database name.	test_db
SearchKeyWord	string	No	Search keyword.	update
SearchParam	string	No	Query parameter.	cputime
SearchRule	string	No	Query rule.	>
SearchValue	string	No	Query value.	0.01
SqlId	string	No	SQL ID.	8D6E84**0B8FB1823D199E2CA1**
NodeIp	string	No	Database node IP address.	i-bp18qljorblo8es*****
FilterCondition	string	No	Reference all fields using the at sign (@). For available fields, see the columns returned by the Query SQL performance statistics operation.	@avgCpuTime > 20 and @executions > 100
AcceptLanguage	string	No	Language for returned data.	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 with IN clauses.	true

Response elements

Element	Type	Description	Example
	object	Response to the request.
RequestId	string	Request ID.	EE205C00-30E4-**-**-87E3A8A2AA0C
Data	array<object>	List of slow SQL information.
	array<object>	Slow SQL information.
Executions	integer	Number of SQL executions.	100
RpcCount	number	Number of RPC calls during the period.	0
RemotePlans	integer	Number of remote executions during the period.	0
MissPlans	integer	Number of times the execution plan was not found in the cache during the period.	0
MaxElapsedTime	number	Maximum response time during the period.	260.044
TotalWaitTime	number	Total wait time during the period.	10.966
ExecPs	number	Average number of executions per second during the period.	0.31
MaxCpuTime	number	Maximum CPU time.	257.967
ClientIp	string	Client IP address.	12.*.13.***
UserName	string	Username.	test_user
DbName	string	Database name.	test_db
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.0
SumWaitTime	number	Wait time (in milliseconds) for the longest wait event.	9421.73
AvgWaitCount	number	Average number of waits.	0.0
AvgRpcCount	number	Average number of RPC calls.	8.0
LocalPlanPercentage	number	Percentage of local plans.	0.0
RemotePlanPercentage	number	Percentage of remote plans.	0.0
DistPlanPercentage	number	Percentage of distributed plans.	100.0
SumElapsedTime	number	Total response time (in milliseconds).	11452126.36
AvgNetTime	number	Average network I/O time (in milliseconds).	0.0
AvgExecutorRpcCount	number	Number of remote executions, corresponding to v$sl_audit.executor_rpc.	0.0
MissPlanPercentage	number	Percentage of plans not found in cache.	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 concurrency wait time (in milliseconds).	0.0
MaxUserIoWaitTime	number	Maximum I/O wait time (in milliseconds).	0.0
MaxDiskReads	number	Maximum number of disk reads.	0.0
AvgExpectedWorkerCount	number	Expected concurrency.	3
AvgUsedWorkerCount	number	Average number of threads used.	3
SumLogicalReads	number	Total logical reads.	0.0
Server	string	OBServer	i-bp1db1as038mej**
ServerIp	string	OBServer IP	i-bp1db1as038mej**
ServerPort	integer	OBServer port	10110
SqlTextShort	string	SQL text (first 100 characters).	select a from b where
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.	1.0
FailCount	integer	Number of execution errors.	0
AvgWaitTime	number	Average wait time (in milliseconds).	1.42
AvgElapsedTime	number	Average response time (in milliseconds).	1876.78
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 an 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 disk reads.	0.0
RetryCount	integer	Number of retries.	5
AvgMemstoreReadRows	number	Average number of Memstore rows read.	0.0
AvgSsstoreReadRows	number	Average number of SSTable rows read.	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.
	object	Response result.
Executions	integer	Total number of executions during the period.	100
RpcCount	number	Total number of RPC calls.	100
RemotePlans	integer	Number of remote executions.	100
MissPlans	integer	Number of times the plan cache was missed.	100
MaxElapsedTime	number	Maximum response time.	10000
TotalWaitTime	number	Total wait time (in ms) during SQL execution, corresponding to v$sql_audit.total_wait_time_micro (in μs).	100000
ExecPs	number	SQL execution frequency.	0.31
MaxCpuTime	number	Maximum CPU time.	257.967
ClientIp	string	Client IP address.	127.0.0.1
UserName	string	Username.	admin
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	Wait time (in milliseconds) for the longest wait event.	9421.73
AvgWaitCount	number	Average wait time (in milliseconds).	0
AvgRpcCount	number	Average number of RPC calls.	0
LocalPlanPercentage	number	Percentage of local plans.	0
RemotePlanPercentage	number	Percentage of remote plans.	0
DistPlanPercentage	number	Percentage of distributed plans.	100
SumElapsedTime	number	Total response time (in milliseconds).	11452126.36
AvgNetTime	number	Average network I/O time (in milliseconds).	0
AvgExecutorRpcCount	number	Number of remote executions, corresponding to v$sl_audit.executor_rpc.	0
MissPlanPercentage	number	Percentage of plans not found in cache.	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	Concurrency used.	3
SumLogicalReads	number	Total logical reads.	0
Server	string	OBServer	10...6
ServerIp	string	OBServer IP	11.*..69
ServerPort	integer	OBServer port	389
SqlTextShort	string	SQL text (first 100 characters).	select * from test
SqlType	string	SQL type.	SELECT
SqlId	string	SQL ID	4CE89**8536A03374DD2**E76DE5
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.	22
AvgWaitTime	number	Average wait time (in milliseconds).	1.42
AvgElapsedTime	number	Average response time (in milliseconds).	1876.78
AvgCpuTime	number	Average CPU time (in milliseconds).	@avgCpuTime > 20 and @executions > 100
AvgNetWaitTime	number	Average network time (in milliseconds).	0
AvgQueueTime	number	Average queue time (in milliseconds).	0.01
AvgDecodeTime	number	Average syntax parsing time (in milliseconds).	0
AvgGetPlanTime	number	Average plan generation time (in milliseconds).	0
AvgExecuteTime	number	Average plan 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 rows read.	0
AvgSsstoreReadRows	number	Average number of SSTable rows read.	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
AvgDbTime	number	Average database time (in milliseconds).	100
SumDbTime	number	Total database time (in milliseconds).	100
FormatSqlId	string	FormatSqlId

Examples

Success response

JSON format

{
  "RequestId": "EE205C00-30E4-****-****-87E3A8A2AA0C",
  "Data": [
    {
      "Executions": 100,
      "RpcCount": 0,
      "RemotePlans": 0,
      "MissPlans": 0,
      "MaxElapsedTime": 260.044,
      "TotalWaitTime": 10.966,
      "ExecPs": 0.31,
      "MaxCpuTime": 257.967,
      "ClientIp": "1*2.***.1*3.***",
      "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-bp1db1a**s038**mej**",
      "ServerIp": "i-bp1db1a**s038**mej**",
      "ServerPort": 10110,
      "SqlTextShort": "select a from b where",
      "SqlType": "select ",
      "SqlId": "8D6E84****0B8FB1823D199E2CA1****",
      "Inner": false,
      "WaitEvent": "none",
      "AvgAffectedRows": 9978.75,
      "AvgReturnRows": 0,
      "AvgPartitionCount": 1,
      "FailCount": 0,
      "AvgWaitTime": 1.42,
      "AvgElapsedTime": 1876.78,
      "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": 5,
      "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": 257.967,
          "ClientIp": "127.0.0.1",
          "UserName": "admin",
          "DbName": "test",
          "RetCode4012Count": 0,
          "RetCode4013Count": 0,
          "RetCode5001Count": 0,
          "RetCode5024Count": 0,
          "RetCode5167Count": 0,
          "RetCode5217Count": 0,
          "RetCode6002Count": 0,
          "FailPercentage": 0,
          "SumWaitTime": 9421.73,
          "AvgWaitCount": 0,
          "AvgRpcCount": 0,
          "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": "10.**.**.6",
          "ServerIp": "11.***.**.69",
          "ServerPort": 389,
          "SqlTextShort": "select * from test",
          "SqlType": "SELECT",
          "SqlId": "4CE89****8536A03374DD2****E76DE5",
          "Inner": false,
          "WaitEvent": "none",
          "AvgAffectedRows": 9978.75,
          "AvgReturnRows": 0,
          "AvgPartitionCount": 1,
          "FailCount": 22,
          "AvgWaitTime": 1.42,
          "AvgElapsedTime": 1876.78,
          "AvgCpuTime": 0,
          "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": ""
        }
      ],
      "AvgDbTime": 100,
      "SumDbTime": 100,
      "FormatSqlId": ""
    }
  ]
}

Error codes

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.