Use GetPfsSqlSummaries to query full request data from performance insight - Database Autonomy Service

Call the GetPfsSqlSummaries API to query all request data using the SQL ID dimension in Performance Insight (New Version) for a database instance.

Operation description

Prerequisites for using this API:

Use the latest version of the Alibaba Cloud or DAS SDK.
When calling the DAS service using the SDK, specify the region as cn-shanghai.
The target database instance is RDS MySQL or PolarDB MySQL.
The target database instance has Performance Insight (New Version) enabled. For more information, see Performance Insight (New Version).

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

hdm:GetPfsSqlSummaries

get

*All Resource

*

None

Request parameters

Parameter	Type	Required	Description	Example
InstanceId	string	No	Database instance ID.	rm-uf61swc4cru0b****
NodeId	string	No	Node ID. Note Provide a node ID for RDS MySQL Cluster Edition or PolarDB for MySQL database instances.	r-****-db-0
SqlId	string	No	SQL ID. Note If you specify an SQL ID, the system returns statistics for that SQL ID only. If you leave this parameter empty, the system returns statistics for the entire database instance.	651b56fe9418d48edb8fdf0980ec****
Keywords	string	No	Keywords in the SQL text. Separate multiple keywords with spaces.	select update
StartTime	integer	No	Start time of the query, in Unix timestamp format, in milliseconds.	1675833788056
EndTime	integer	No	End time of the query, in Unix timestamp format, in milliseconds. Note The end time must be later than the start time. You can query data for any seven-day period within the last 30 days.	1679297005999
OrderBy	string	No	Field to sort by. Default is count. count: Number of executions. avgLatency: Average execution time. rowsExamined: Total number of scanned rows. rowsExaminedAvg: Average number of scanned rows. rowsSentAvg: Average number of returned rows.	count
Asc	boolean	No	Sort in ascending order. Default is false. true: Yes. false: No.	false
PageNo	integer	No	Page number for paged queries. Start from 1. Default is 1.	1
PageSize	integer	No	Maximum number of records per page for paged queries. Default is 10. Maximum is 100.	10

Response elements

Element	Type	Description	Example
	object	List of SQL summaries.
Message	string	Response message. Note If the request succeeds, this parameter returns Successful. If it fails, it returns error details such as an error code.	Successful
RequestId	string	Request ID.	54F3DBAE-9420-511A-9C29-265E8C04****
Data	object	Response data.
Total	integer	Total number of records.	264
List	array<object>	Detailed information list.
	object	List of returned items.
UserId	string	User ID.	196278346919****
Psql	string	SQL template.	select ?
InstanceId	string	Instance ID.	rm-2ze8g2am97624****
NodeId	string	Node ID. Note This parameter is returned for ApsaraDB RDS for MySQL Cluster Edition or PolarDB for MySQL database instances.	r-x****-db-0
SqlType	string	SQL type. Valid values: SELECT UPDATE DELETE	SELECT
LogicId	integer	Logical database ID.	58275984
SqlId	string	SQL template ID.	2e8147b5ca2dfc640dfd5e43d96a****
Timestamp	integer	Data timestamp in Unix time format, in milliseconds.	1643040000000
RtRate	number	Percentage of total execution duration.	0.1384
CountRate	number	Percentage of total executions.	0.0586
FullScan	boolean	Indicates whether a full table scan occurred. Valid values: true: Yes. false: No.	true
Count	integer	Total number of executions.	100000
ErrCount	integer	Number of errors.	0
WarnCount	integer	Number of warnings.	0
TotalLatency	number	Total execution duration, in milliseconds.	60913.256
MaxLatency	number	Maximum execution duration, in milliseconds.	36.233
AvgLatency	number	Average SQL execution duration, in milliseconds.	0.1717
RowsSent	integer	Number of rows returned.	0
RowsSendAvg	number	Average number of rows sent.	0
RowsExamined	integer	Total number of rows scanned.	100
RowsExaminedAvg	number	Average number of rows scanned.	0
RowsAffected	integer	Number of rows affected.	0
RowsAffectedAvg	number	Average number of rows affected.	0
TmpTables	integer	Number of temporary tables.	0
TmpDiskTables	integer	Number of temporary disk tables.	0
RowsSorted	integer	Number of rows sorted.	0
SortMergePasses	integer	Number of merge passes required by the sort algorithm.	0
PhysicalReads	integer	Number of physical nodes.	0
PhysicalAsyncReads	integer	Number of physical asynchronous nodes.	0
SemisyncDelayTime	number	Semi-synchronous replication delay, in milliseconds.	0.12
RowsSentAvg	number	Average number of rows returned per SQL statement.	0.52
LogicReads	integer	Number of logical nodes.	0
RedoWrites	integer	Number of redo nodes.	0
DataWriteTime	number	Data write time, in milliseconds.	0
DataWrites	integer	Number of writable data nodes.	0
DataReadTime	number	Data read time, in milliseconds.	0
DataReads	integer	Number of readable data nodes.	0
RwlockOsWaits	integer	Indicates whether read/write splitting is enabled. Valid values: 0: Disabled. 1: Enabled.	0
RwlockSpinRounds	integer	Read/write splitting parameter.	0
RwlockSpinWaits	integer	Indicates whether multiple index scans are enabled. Valid values: 0: Disabled. 1: Enabled.	0
MutexWaits	integer	Number of mutex waits.	1
MutexSpins	integer	Number of mutex spins.	1
TransactionLockTime	number	Transaction lock time, in milliseconds.	0
ServerLockTime	number	Server lock time, in milliseconds.	0
CpuTime	number	CPU runtime, in milliseconds.	0
ElapsedTime	number	Actual runtime, in milliseconds.	0
Id	integer	Primary key ID.	26186357
SelectScanAvg	number	Average number of scans.	0
SelectRangeAvg	number	Average range selection.	0
SelectFullJoinAvg	number	The average number of connections that perform table scans without using an index. Important If this parameter value is not 0, carefully check the indexes of the table.	0
SelectFullRangeJoinAvg	number	Average number of range joins.	0
SortScanAvg	number	Average number of sorted scans.	0
SortRowsAvg	number	Average number of sorted rows.	0
TmpTablesAvg	number	Average number of temporary tables.	0
TmpDiskTablesAvg	number	Average number of temporary disk tables.	0
FirstTime	integer	First execution time, in Unix time format, in milliseconds.	1659308149000
LastTime	integer	Last update time, in Unix time format, in milliseconds.	1661306520000
LockLatencyAvg	number	Average lock wait time, in milliseconds.	0
TimerWaitAvg	number	Reserved parameter.	None
SortRangeAvg	number	Average number of range-based sorts.	0
CpuRate	number	Ratio of CPU execution time to total SQL execution time.	0
Db	string	Database name.	testDB
Tables	array	Database table names.
	string	Database table name.	test_table
PageNo	integer	Page number.	1
PageSize	integer	Maximum number of records per page.	10
Extra	any	Reserved parameter.	None
Code	integer	Response status code.	200
Success	boolean	Indicates whether the request succeeded: true: Succeeded. false: Failed.	true

Examples

Success response

JSON format

{
  "Message": "Successful",
  "RequestId": "54F3DBAE-9420-511A-9C29-265E8C04****",
  "Data": {
    "Total": 264,
    "List": [
      {
        "UserId": "196278346919****",
        "Psql": "select ?",
        "InstanceId": "rm-2ze8g2am97624****",
        "NodeId": "r-x****-db-0",
        "SqlType": "SELECT",
        "LogicId": 58275984,
        "SqlId": "2e8147b5ca2dfc640dfd5e43d96a****",
        "Timestamp": 1643040000000,
        "RtRate": 0.1384,
        "CountRate": 0.0586,
        "FullScan": true,
        "Count": 100000,
        "ErrCount": 0,
        "WarnCount": 0,
        "TotalLatency": 60913.256,
        "MaxLatency": 36.233,
        "AvgLatency": 0.1717,
        "RowsSent": 0,
        "RowsSendAvg": 0,
        "RowsExamined": 100,
        "RowsExaminedAvg": 0,
        "RowsAffected": 0,
        "RowsAffectedAvg": 0,
        "TmpTables": 0,
        "TmpDiskTables": 0,
        "RowsSorted": 0,
        "SortMergePasses": 0,
        "PhysicalReads": 0,
        "PhysicalAsyncReads": 0,
        "SemisyncDelayTime": 0.12,
        "RowsSentAvg": 0.52,
        "LogicReads": 0,
        "RedoWrites": 0,
        "DataWriteTime": 0,
        "DataWrites": 0,
        "DataReadTime": 0,
        "DataReads": 0,
        "RwlockOsWaits": 0,
        "RwlockSpinRounds": 0,
        "RwlockSpinWaits": 0,
        "MutexWaits": 1,
        "MutexSpins": 1,
        "TransactionLockTime": 0,
        "ServerLockTime": 0,
        "CpuTime": 0,
        "ElapsedTime": 0,
        "Id": 26186357,
        "SelectScanAvg": 0,
        "SelectRangeAvg": 0,
        "SelectFullJoinAvg": 0,
        "SelectFullRangeJoinAvg": 0,
        "SortScanAvg": 0,
        "SortRowsAvg": 0,
        "TmpTablesAvg": 0,
        "TmpDiskTablesAvg": 0,
        "FirstTime": 1659308149000,
        "LastTime": 1661306520000,
        "LockLatencyAvg": 0,
        "TimerWaitAvg": 0,
        "SortRangeAvg": 0,
        "CpuRate": 0,
        "Db": "testDB",
        "Tables": [
          "test_table"
        ]
      }
    ],
    "PageNo": 1,
    "PageSize": 10,
    "Extra": "None"
  },
  "Code": 200,
  "Success": true
}

Error codes

HTTP status code	Error code	Error message	Description
400	InvalidParams	The request parameters are invalid.
403	NoPermission	You are not authorized to do this action.

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.