DocsICP FilingConsole
Documentation

DescribeSqlPatternCompareReport

Updated at:
Copy as MD
My Favorites

Queries the details of an SQL Pattern comparison report.

Operation description

Performs a paged query of SQL Pattern comparison report details based on MetricType by using paging. Report type descriptions:

  • NEW: Returns Patterns that are new in time window 2. MetricValues returns Time2.

  • CHANGED: Returns Patterns that exist in both time windows and have increased average values for the current metric. MetricValues returns Avg, Sum, and Max.

Metric calculation methods:

  • Sum: The sum of metric values across valid query minute buckets.

  • Avg: The average of metric values across valid query minute buckets.

  • Max: The peak metric value within a single minute bucket.

Metric units:

  • QUERY_COUNT: count.

  • CPU_COST: seconds.

  • SHUFFLE_SIZE, PEAK_MEMORY, SCAN_SIZE: GB.

Note

  • Only reports with DetailEnabled set to true can be queried for details. Reports that are incomplete, canceled, or expired cannot be queried.

  • Fields ending with Percent are already expressed as percentages. When the time window 1 metric value is 0, ChangeRatePercent may not be returned and should not be treated as 0%.

  • Reports are isolated by instance and Alibaba Cloud account.

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

No authorization for this operation. If you encounter issues with this operation, contact technical support.

Request parameters

Parameter

Type

Required

Description

Example
RegionId

string

Yes

The region ID of the instance.

cn-beijing
DBClusterId

string

Yes

The ID of the AnalyticDB for MySQL instance.

am-2ze1234567890****
ReportId

integer

Yes

The ID of the SQL Pattern comparison report.

1001
MetricType

string

Yes

The analysis metric. Valid values:

  • QUERY_COUNT: the number of query executions.

  • CPU_COST: the CPU consumption.

  • SHUFFLE_SIZE: the amount of shuffle data.

  • PEAK_MEMORY: the peak memory consumption.

  • SCAN_SIZE: the amount of scanned data.

Valid values:

  • PEAK_MEMORY :

    peak memory consumption.

  • CPU_COST :

    CPU consumption.

  • QUERY_COUNT :

    number of query executions.

  • SHUFFLE_SIZE :

    amount of shuffle data.

  • SCAN_SIZE :

    amount of scanned data.

CPU_COST
PageNumber

integer

No

The page number. Pages start from page 1.

Default value: 1.

1
PageSize

integer

No

The number of entries per page. Valid values: 1 to 100.

Default value: 50.

50
Order

string

No

Sorts the query results by a specified field. The value is a JSON array string, such as [{"Field":"Time2SumValue","Type":"Desc"}]. The array can contain only one object. Parameters:

  • Field: the sort field. This parameter is case-sensitive. Valid values:

    • NEW report: Time2SumValue, Time2AvgValue, Time2MaxValue.

    • CHANGED report: AvgChangeRatePercent, AvgTime1Value, AvgTime2Value, SumChangeRatePercent, SumTime1Value, SumTime2Value, MaxChangeRatePercent, MaxTime1Value, MaxTime2Value.

    • All report types and analysis metrics: AvgRt, MaxRt.

    • QUERY_COUNT: TotalQueryTime.

    • CPU_COST: QueryCount, AvgPlanningTime, MaxPlanningTime, AvgExecutionTime, MaxExecutionTime.

    • SHUFFLE_SIZE, PEAK_MEMORY: QueryCount.

    • SCAN_SIZE: QueryCount, TotalScanCost.

  • Type: the sort order. This parameter is case-insensitive. Valid values:

    • Asc: ascending order.

    • Desc: descending order.

Note

  • NEW reports are sorted by Time2SumValue in descending order by default.

  • CHANGED reports are sorted by AvgChangeRatePercent in descending order by default.

  • The value of Field must be applicable to the current report type and MetricType.

[{"Field":"AvgChangeRatePercent","Type":"Desc"}]
ChangeRate

string

No

The average change rate filter range for CHANGED reports. The format is left~right, where values are expressed as percentages and the interval is left-exclusive and right-inclusive. Examples:

  • 100~500: greater than 100% and less than or equal to 500%.

  • 100~: greater than 100% with no upper limit.

Note

  • The left boundary is required and must be no less than 0. The right boundary must be no less than the left boundary.

  • This parameter is ignored for NEW reports.

  • When the time window 1 metric value is 0 and the time window 2 value is greater than 0, the Pattern is classified as zero-baseline growth and is categorized as SEVERE (significant change). To exclude such Patterns, set an upper limit for the change rate.

100~500
IncludePattern

boolean

No

Specifies whether to return the parameterized SQL Pattern text. Valid values:

  • true: Returns the Pattern text.

  • false: Does not return the Pattern text, which reduces the response size.

Default value: true.

true

Response elements

Element

Type

Description

Example

object

The paginated results of the specified report and analysis dimension.

Items

array<object>

The Pattern details on the current page. An empty array is returned if no results match the conditions.

array<object>

The analysis result of a SQL Pattern for the current dimension.

AvgExecutionTime

string

The display string of the average execution duration for Time 2, in seconds. This field is returned only for the CPU_COST dimension.

0.4s
AvgPlanningTime

string

The display string of the average planning duration for Time 2, in seconds. This field is returned only for the CPU_COST dimension.

0.1s
AvgRt

string

The display string of the average query response time for Time 2, in seconds. This field is returned for all analysis dimensions.

0.5s
MaxExecutionTime

string

The display string of the maximum execution duration for Time 2, in seconds. This field is returned only for the CPU_COST dimension.

1.8s
MaxPlanningTime

string

The display string of the maximum planning duration for Time 2, in seconds. This field is returned only for the CPU_COST dimension.

0.2s
MaxRt

string

The display string of the maximum query response time for Time 2, in seconds. This field is returned for all analysis dimensions.

2s
MetricValues

object

The primary metric mapping for the current analysis dimension. Valid keys:

  • QUERY_COUNT: the number of query executions.

  • CPU_COST: the CPU consumption.

  • SHUFFLE_SIZE: the shuffle data volume.

  • PEAK_MEMORY: the peak memory consumption.

  • SCAN_SIZE: the scan data volume.

Note

Each result contains only one key that matches the MetricType request parameter.

object

The statistical result of a primary metric. The fields returned vary by report type:

  • NEW report: returns Time2.

  • CHANGED report: returns Avg, Sum, and Max.

Avg

object

The dual-window comparison of the average value across active query minute buckets for the CHANGED report. This field is returned only for CHANGED reports.

ChangeRatePercent

number

The change rate of the average value across active query minute buckets, calculated as (Time 2 value − Time 1 value) / Time 1 value × 100. A value of 200 indicates a 200% increase. When the Time 1 value is 0, a finite change rate cannot be calculated. This field may not be returned and must not be treated as 0%.

200
Time1DisplayValue

string

The display string of the average value across active query minute buckets for Time 1, with the unit included.

1s
Time1RatioPercent

number

The percentage of this Pattern's Time 1 average value across active query minute buckets relative to the sum of the corresponding statistics for all results before dimension filtering in the current report. A value of 10 indicates 10%.

10
Time1Value

number

The average value across active query minute buckets for Time 1. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

1
Time2DisplayValue

string

The display string of the average value across active query minute buckets for Time 2, with the unit included.

3s
Time2RatioPercent

number

The percentage of this Pattern's Time 2 average value across active query minute buckets relative to the sum of the corresponding statistics for all results before dimension filtering in the current report. A value of 10 indicates 10%.

10
Time2Value

number

The average value across active query minute buckets for Time 2. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

3
Max

object

The dual-window comparison of the peak value in a single minute bucket for the CHANGED report. This field is returned only for CHANGED reports.

ChangeRatePercent

number

The change rate of the peak value in a single minute bucket, calculated as (Time 2 value − Time 1 value) / Time 1 value × 100. A value of 200 indicates a 200% increase. When the Time 1 value is 0, a finite change rate cannot be calculated. This field may not be returned and must not be treated as 0%.

200
Time1DisplayValue

string

The display string of the peak value in a single minute bucket for Time 1, with the unit included.

3s
Time1Value

number

The peak value in a single minute bucket for Time 1. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

3
Time2DisplayValue

string

The display string of the peak value in a single minute bucket for Time 2, with the unit included.

9s
Time2Value

number

The peak value in a single minute bucket for Time 2. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

9
MetricCode

string

The primary metric code, which matches the key in MetricValues and the MetricType request parameter. Valid values:

  • QUERY_COUNT: the number of query executions.

  • CPU_COST: the CPU consumption.

  • SHUFFLE_SIZE: the shuffle data volume.

  • PEAK_MEMORY: the peak memory consumption.

  • SCAN_SIZE: the scan data volume.

CPU_COST
MetricName

string

The primary metric name. The mapping is as follows:

  • QUERY_COUNT: QueryCount.

  • CPU_COST: OperatorCost.

  • SHUFFLE_SIZE: ShuffleSize.

  • PEAK_MEMORY: PeakMemory.

  • SCAN_SIZE: ScanSize.

OperatorCost
Primary

boolean

Indicates whether this is the primary metric for the current analysis dimension. The current value is true.

true
Sum

object

The dual-window comparison of the sum of metric values across active query minute buckets for the CHANGED report. This field is returned only for CHANGED reports.

ChangeRatePercent

number

The change rate of the sum of metric values across active query minute buckets, calculated as (Time 2 value − Time 1 value) / Time 1 value × 100. A value of 200 indicates a 200% increase. When the Time 1 value is 0, a finite change rate cannot be calculated. This field may not be returned and must not be treated as 0%.

200
Time1DisplayValue

string

The display string of the sum of metric values across active query minute buckets for Time 1, with the unit included.

60s
Time1RatioPercent

number

The percentage of this Pattern's Time 1 sum of metric values across active query minute buckets relative to the sum of the corresponding statistics for all results before dimension filtering in the current report. A value of 10 indicates 10%.

10
Time1Value

number

The sum of metric values across active query minute buckets for Time 1. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

60
Time2DisplayValue

string

The display string of the sum of metric values across active query minute buckets for Time 2, with the unit included.

180s
Time2RatioPercent

number

The percentage of this Pattern's Time 2 sum of metric values across active query minute buckets relative to the sum of the corresponding statistics for all results before dimension filtering in the current report. A value of 10 indicates 10%.

10
Time2Value

number

The sum of metric values across active query minute buckets for Time 2. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

180
Time2

object

The aggregated result for Time 2 in the NEW report. This field is returned only for NEW reports.

AvgDisplayValue

string

The display string of the average value across active query minute buckets for Time 2, with the unit included.

3s
AvgRatioPercent

number

The percentage of this Pattern's Time 2 average value relative to the sum of average values across all Patterns before dimension filtering in the current report. A value of 10 indicates 10%.

10
AvgValue

number

The average value across active query minute buckets for Time 2, calculated as the total sum divided by the number of minute buckets that contain queries for this Pattern. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

3
MaxDisplayValue

string

The display string of the peak value in a single minute bucket for Time 2, with the unit included.

9s
MaxValue

number

The maximum metric value in a single minute bucket for Time 2. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

9
SumDisplayValue

string

The display string of the total sum for Time 2, with the unit included.

180s
SumRatioPercent

number

The percentage of this Pattern's Time 2 total sum relative to the total sum of all results before dimension filtering in the current report. A value of 10 indicates 10%.

10
SumValue

number

The sum of metric values across active query minute buckets for Time 2. The unit depends on MetricCode: count for QUERY_COUNT, seconds for CPU_COST, and GB (1 GB = 1024³ bytes) for SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE.

180
Pattern

string

The parameterized SQL Pattern text. This field is empty or not returned when IncludePattern is set to false. When the text is unavailable, a prompt containing a hash identifier may be returned.

SELECT * FROM orders WHERE order_id = ?
QueryCount

integer

The number of query executions for Time 2, in count. This field is returned for the CPU_COST, SHUFFLE_SIZE, PEAK_MEMORY, and SCAN_SIZE dimensions.

120
QueryCountDisplayValue

string

The display string of the number of query executions for Time 2. The applicable scope is the same as QueryCount.

120 times
Rank

integer

The global sequence number in the current filtered and sorted results, starting from 1 and numbered continuously across pages.

1
RiskLevel

string

The change level for the current analysis dimension. Valid values:

  • NEW: A new Pattern. Returned only for NEW reports.

  • SLIGHT: A slight change. The average change rate is in the range of (0%, 20%].

  • MODERATE: A moderate change. The average change rate is in the range of (20%, 50%].

  • HIGH: A high change. The average change rate is in the range of (50%, 100%].

  • SEVERE: A severe change. The average change rate is greater than 100%, or the change represents zero-baseline growth.

Note

The change level only indicates the magnitude of metric growth and cannot be used alone to determine the cause of a fault.

Valid values:

  • SLIGHT :

    slight change.

  • NEW :

    new.

  • MODERATE :

    moderate change.

  • SEVERE :

    severe change.

  • HIGH :

    high change.

SEVERE
SqlPatternHash

string

The hash identifier of the SQL Pattern, returned as a string. Store and pass this value as a string to avoid precision loss caused by numeric conversion.

1234567890123456789
TotalQueryTime

string

The display string of the total query duration for Time 2, in seconds. This field is returned only for the QUERY_COUNT dimension.

60s
TotalScanCost

string

The display string of the total scan duration for Time 2, in seconds. This field is returned only for the SCAN_SIZE dimension.

12s
MetricType

string

The analysis metric. Valid values:

  • QUERY_COUNT: the number of query executions.

  • CPU_COST: the CPU consumption.

  • SHUFFLE_SIZE: the amount of shuffle data.

  • PEAK_MEMORY: the peak memory consumption.

  • SCAN_SIZE: the amount of scanned data.

Valid values:

  • PEAK_MEMORY :

    peak memory consumption.

  • CPU_COST :

    CPU consumption.

  • QUERY_COUNT :

    number of query executions.

  • SHUFFLE_SIZE :

    amount of shuffle data.

  • SCAN_SIZE :

    amount of scanned data.

CPU_COST
PageNumber

integer

The page number of the returned page, starting from 1.

1
PageSize

integer

The maximum number of entries returned per page for this query.

50
ReportId

integer

The ID of the SQL Pattern comparison report.

1001
RequestId

string

The request ID.

9A1B2C3D-4E5F-6789-ABCD-0123456789AB
TotalCount

integer

The total number of Patterns that match the current report, analysis dimension, and change rate filter conditions. This is not the number of entries on the current page.

1

Examples

Success response

JSON format

{
  "Items": [
    {
      "AvgExecutionTime": "0.4s",
      "AvgPlanningTime": "0.1s",
      "AvgRt": "0.5s",
      "MaxExecutionTime": "1.8s",
      "MaxPlanningTime": "0.2s",
      "MaxRt": "2s",
      "MetricValues": {
        "key": {
          "Avg": {
            "ChangeRatePercent": 200,
            "Time1DisplayValue": "1s",
            "Time1RatioPercent": 10,
            "Time1Value": 1,
            "Time2DisplayValue": "3s",
            "Time2RatioPercent": 10,
            "Time2Value": 3
          },
          "Max": {
            "ChangeRatePercent": 200,
            "Time1DisplayValue": "3s",
            "Time1Value": 3,
            "Time2DisplayValue": "9s",
            "Time2Value": 9
          },
          "MetricCode": "CPU_COST",
          "MetricName": "OperatorCost",
          "Primary": true,
          "Sum": {
            "ChangeRatePercent": 200,
            "Time1DisplayValue": "60s",
            "Time1RatioPercent": 10,
            "Time1Value": 60,
            "Time2DisplayValue": "180s",
            "Time2RatioPercent": 10,
            "Time2Value": 180
          },
          "Time2": {
            "AvgDisplayValue": "3s",
            "AvgRatioPercent": 10,
            "AvgValue": 3,
            "MaxDisplayValue": "9s",
            "MaxValue": 9,
            "SumDisplayValue": "180s",
            "SumRatioPercent": 10,
            "SumValue": 180
          }
        }
      },
      "Pattern": "SELECT * FROM orders WHERE order_id = ?",
      "QueryCount": 120,
      "QueryCountDisplayValue": "120次",
      "Rank": 1,
      "RiskLevel": "SEVERE",
      "SqlPatternHash": "1234567890123456789",
      "TotalQueryTime": "60s",
      "TotalScanCost": "12s"
    }
  ],
  "MetricType": "CPU_COST",
  "PageNumber": 1,
  "PageSize": 50,
  "ReportId": 1001,
  "RequestId": "9A1B2C3D-4E5F-6789-ABCD-0123456789AB",
  "TotalCount": 1
}

Error codes

HTTP status code

Error code

Error message

Description
400 IdempotentParameterMismatch The request uses the same client token as a previous, but non-identical request. Do not reuse a client token with different requests, unless the requests are identical.

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.