Aggregating Metrics from Syntactically Similar Queries

You can aggregate monitoring data for multiple statements in the statement cache. Although the statements are syntactically distinct—having individual comments or differences in their text format—represent semantically identical queries.

By providing a way to identify semantically equivalent statements, the show_condensed_text function can be used to aggregate the values from the monCachedStatement table into a single row in the query result.

monCachedStatement stores detailed monitoring information about the statement cache, including:
This information can be helpful when you are troubleshooting, and when you are deciding which statements to retain in the cache. Although you can use show_cached_text, to view logically identical SQL statements that include nonunique SQL text, you may have difficulty identifying them in the output, especially if such statements are numerous. For example, if you have three queries that start with this text:
select * from db1.tableA where col1 in (?,?,?) additional_comment
select * from db1.tableA where col1 in (?,?,?) different_additional_comment
select * from db1.tableA where col1 in (?,?,?) different_additional_comment

Each query creates a separate entry in the global statement cache, compiles its own query plan, and produces three different SQL texts when you select from monCachedStatement.

However because these three queries have different SSQLIDs, metrics are distributed across multiple entries, and are reported separately in the top SQL statements monitor, resulting in:

Use show_condensed_text to group the statements reported in the monCachedStatement so the metrics for semantically identical statements can be aggregated together.

show_condensed_text uses these rules to condense SQL text (including SQL text larger than 16KB):