Obtains information of SQL templates based on query governance data.

Usage notes

  • If you use an Alibaba Cloud SDK, make sure that the aliyun-sdk-core version is later than V2.1.8. We recommend that you use the latest version.
  • The version of your Database Autonomy Service (DAS) SDK must be V2.1.8 or later.
  • If you use an SDK to call the DAS service, you must set the region to cn-shanghai.
  • This operation supports the following database services:
    • ApsaraDB RDS for MySQL
    • PolarDB for MySQL
    • ApsaraDB RDS for PostgreSQL

Request parameters

Parameter Type Required Example Description
Action String Yes GetQueryOptimizeDataStats

The operation that you want to perform. Set the value to GetQueryOptimizeDataStats.

Engine String Yes MySQL

The database engine. Valid values:

  • MySQL
  • PolarDBMySQL
  • PostgreSQL
InstanceIds String No rm-2ze8g2am97624****

The instance IDs. Separate multiple IDs with commas (,).

TagNames String No None

A reserved parameter.

Keywords String No select update

The keywords of the SQL templates. Separate multiple keywords with spaces.

LogicalOperator String No or

The logical relationship between multiple keywords.

  • or
  • and
Rules String No DAS_NOT_IMPORTANT

The tags that are used to filter SQL templates. Separate multiple tags with commas (,). For more information, see Query governance.

DbNames String No testdb01

The name of the database to be queried.

Time String Yes 1642953600000

The end of the time range to query. Specify the time in the UNIX timestamp format. Unit: milliseconds.

PageSize String No 10

The maximum number of entries to return on each page. Default value: 10.

PageNo String No 1

The number of the page to return. Pages start from page 1. Default value: 1.

OrderBy String No count

The field by which to sort the returned entries. Default value: count.

  • count: the number of executions.
  • maxQueryTime: the longest execution time.
  • avgQueryTime: the average execution time.
  • maxLockTime: the longest lock wait time.
  • avgLockTime: the longest lock wait time.
  • maxRowsExamined: the largest number of scanned rows.
  • avgRowsExamined: the average number of scanned rows.
  • maxRowsSent: the largest number of returned rows.
  • avgRowsSent: the average number of returned rows.
Asc String No true

Specifies whether to sort the returned entries in ascending order. Default value: true. Valid values:

  • true
  • false
OnlyOptimizedSql String No false

Specifies whether to limit the scope of this operation to SQL templates that need to be optimized only. Default value: false. Valid values:

  • true: Only SQL templates that need to be optimized are queried.
  • false: All SQL templates are queried.
SqlIds String No 2e8147b5ca2dfc640dfd5e43d96a****

The SQL template ID. You can query the ID of a template by calling the GetQueryOptimizeExecErrorStats operation.

Response parameters

Parameter Type Example Description
Code String 200

The returned HTTP status code.

Message String Successful

The message that is returned for the request.

Data Object

Details of the returned entries.

Total Long 10

The total number of returned entries.

PageNo Integer None

A reserved parameter.

PageSize Integer None

A reserved parameter.

Extra String None

A reserved parameter.

List Array of QueryOptimizeDataStats

The list of SQL templates.

InstanceId String rm-2ze8g2am97624****

The instance ID.

Dbname String testdb01

The name of the database to which the SQL template belongs.

SqlId String 2e8147b5ca2dfc640dfd5e43d96a****

The ID of the SQL template.

SqlType String INSERT

The type of the SQL statement.

Psql String select 1

The content of the SQL template.

SqlSample String select 2

The sample query that took the longest time to execute.

Count Integer 100

The number of times that the SQL template is executed.

MaxQueryTime double 1.1

The longest query execution time. Unit: seconds.

AvgQueryTime double 1.1

The average query execution time. Unit: seconds.

MaxLockTime double 0.1

The longest lock wait time. Unit: seconds.

AvgLockTime double 0.1

The average lock wait time. Unit: seconds.

MaxRowsExamined Long 100000

The largest number of scanned rows.

AvgRowsExamined double 100.1

The average number of scanned rows.

MaxRowsSent Long 10000

The largest number of returned rows.

AvgRowsSent double 100.1

The average number of returned rows.

MaxRowsAffected Long 10000

The largest number of rows affected by the SQL template.

Note If this parameter is not collected, -1 is returned.
AvgRowsAffected double 100.1

The average number of rows affected by the SQL template.

Note If this parameter is not collected, -1 is returned.
RuleList Array of Rules

The list of tags.

Name String DAS_NOT_IMPORTANT

The tag name. For more information, see Query governance.

Type String Predefined

The type of the tag. Valid values:

  • Predefined
  • UserDefined
RequestId String B6D17591-B48B-4D31-9CD6-9B9796B2****

The ID of the request.

Success String true

Indicates whether the request is successful. Valid values:

  • true
  • false

Examples

Sample requests

http(s)://das.cn-shanghai.aliyuncs.com/?Action=GetQueryOptimizeDataStats
&Engine=MySQL
&InstanceIds=rm-2ze8g2am97624****
&TagNames=None
&Keywords=select update
&LogicalOperator=or
&Rules=DAS_NOT_IMPORTANT
&DbNames=testdb01
&Time=1642953600000
&PageSize=10
&PageNo=1
&OrderBy=count
&Asc=true
&OnlyOptimizedSql=false
&SqlIds=2e8147b5ca2dfc640dfd5e43d96a****
&Common request parameters

Sample success responses

XML format

HTTP/1.1 200 OK
Content-Type:application/xml

<GetQueryOptimizeDataStatsResponse>
    <Code>200</Code>
    <Message>Successful</Message>
    <Data>
        <Total>10</Total>
        <Extra>None</Extra>
        <List>
            <InstanceId>rm-2ze8g2am97624****</InstanceId>
            <Dbname>testdb01</Dbname>
            <SqlId>2e8147b5ca2dfc640dfd5e43d96a****</SqlId>
            <SqlType>INSERT</SqlType>
            <Psql>select 1</Psql>
            <SqlSample>select 2</SqlSample>
            <Count>100</Count>
            <MaxQueryTime>1.1</MaxQueryTime>
            <AvgQueryTime>1.1</AvgQueryTime>
            <MaxLockTime>0.1</MaxLockTime>
            <AvgLockTime>0.1</AvgLockTime>
            <MaxRowsExamined>100000</MaxRowsExamined>
            <AvgRowsExamined>100.1</AvgRowsExamined>
            <MaxRowsSent>10000</MaxRowsSent>
            <AvgRowsSent>100.1</AvgRowsSent>
            <MaxRowsAffected>10000</MaxRowsAffected>
            <AvgRowsAffected>100.1</AvgRowsAffected>
            <RuleList>
                <Name>DAS_NOT_IMPORTANT</Name>
                <Type>Predefined</Type>
            </RuleList>
        </List>
    </Data>
    <RequestId>B6D17591-B48B-4D31-9CD6-9B9796B2****</RequestId>
    <Success>true</Success>
</GetQueryOptimizeDataStatsResponse>

JSON format

HTTP/1.1 200 OK
Content-Type:application/json

{
  "Code" : "200",
  "Message" : "Successful",
  "Data" : {
    "Total" : 10,
    "Extra" : "None",
    "List" : [ {
      "InstanceId" : "rm-2ze8g2am97624****",
      "Dbname" : "testdb01",
      "SqlId" : "2e8147b5ca2dfc640dfd5e43d96a****",
      "SqlType" : "INSERT",
      "Psql" : "select 1",
      "SqlSample" : "select 2",
      "Count" : 100,
      "MaxQueryTime" : 1.1,
      "AvgQueryTime" : 1.1,
      "MaxLockTime" : 0.1,
      "AvgLockTime" : 0.1,
      "MaxRowsExamined" : 100000,
      "AvgRowsExamined" : 100.1,
      "MaxRowsSent" : 10000,
      "AvgRowsSent" : 100.1,
      "MaxRowsAffected" : 10000,
      "AvgRowsAffected" : 100.1,
      "RuleList" : [ {
        "Name" : "DAS_NOT_IMPORTANT",
        "Type" : "Predefined"
      } ]
    } ]
  },
  "RequestId" : "B6D17591-B48B-4D31-9CD6-9B9796B2****",
  "Success" : "true"
}

Error codes

HTTP status code Error code Error message Description
400 InvalidParams The request parameters are invalid. The error message returned because the specified request parameters are invalid.

For a list of error codes, visit the API Error Center.