# Analyzing queries with the Top SQL tab in Performance Insights
In the Amazon RDS Performance Insights dashboard, you can find information about running and recent queries in the **Top SQL** tab in the **Top dimensions** table. You can use this information to tune your queries.
**Topics**
+ [
## Overview of the Top SQL tab
](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL)
+ [
# Accessing more SQL text in the Performance Insights dashboard
](USER_PerfInsights.UsingDashboard.SQLTextSize.md)
+ [
# Viewing SQL statistics in the Performance Insights dashboard
](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.AnalyzingSQLLevel.md)
## Overview of the Top SQL tab
By default, the **Top SQL** tab shows the 25 queries that are contributing the most to DB load. To help tune your queries, you can analyze information such as the query text and SQL statistics. You can also choose the statistics that you want to appear in the **Top SQL** tab.
**Topics**
+ [
### SQL text
](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.text)
+ [
### SQL statistics
](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.statistics)
+ [
### Load by waits (AAS)
](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.Load-by-waits)
+ [
### View SQL information
](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.SQL-information)
+ [
### Choose statistics preferences
](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.Preferences)
### SQL text
By default, each row in the **Top SQL** table shows 500 bytes of text for each statement.
![\[SQL text\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/sql-text-oracle.png)
To learn how to see more than the default 500 bytes of SQL text, see [Accessing more SQL text in the Performance Insights dashboard](USER_PerfInsights.UsingDashboard.SQLTextSize.md).
A *SQL digest* is a composite of multiple actual queries that are structurally similar but might have different literal values. The digest replaces hardcoded values with a question mark. For example, a digest might be `SELECT * FROM emp WHERE lname= ?`. This digest might include the following child queries:
```
SELECT * FROM emp WHERE lname = 'Sanchez'
SELECT * FROM emp WHERE lname = 'Olagappan'
SELECT * FROM emp WHERE lname = 'Wu'
```
To see the literal SQL statements in a digest, select the query, and then choose the plus symbol (\$1). In the following example, the selected query is a digest.
![\[Selected SQL digest\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_4b.png)
**Note**
A SQL digest groups similar SQL statements, but doesn't redact sensitive information.
Performance Insights can show Oracle SQL text as **Unknown**. The text has this status in the following situations:
+ An Oracle database user other than `SYS` is active but not currently executing SQL. For example, when a parallel query completes, the query coordinator waits for helper processes to send their session statistics. For the duration of the wait, the query text shows **Unknown**.
+ For an RDS for Oracle instance on Standard Edition 2, Oracle Resource Manager limits the number of parallel threads. The background process doing this work causes the query text to show as **Unknown**.
### SQL statistics
*SQL statistics* are performance-related metrics about SQL queries. For example, Performance Insights might show executions per second or rows processed per second. Performance Insights collects statistics for only the most common queries. Typically, these match the top queries by load shown in the Performance Insights dashboard.
Every line in the **Top SQL** table shows relevant statistics for the SQL statement or digest, as shown in the following example.
![\[Top SQL\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_4.png)
Performance Insights can report `0.00` and `-` (unknown) for SQL statistics. This situation occurs under the following conditions:
+ Only one sample exists. For example, Performance Insights calculates rates of change for RDS PostgreSQL queries based on multiple samples from the `pg_stat_statements` view. When a workload runs for a short time, Performance Insights might collect only one sample, which means that it can't calculate a rate of change. The unknown value is represented with a dash (`-`).
+ Two samples have the same values. Performance Insights can't calculate a rate of change because no change has occurred, so it reports the rate as `0.00`.
+ An RDS PostgreSQL statement lacks a valid identifier. PostgreSQL creates a identifier for a statement only after parsing and analysis. Thus, a statement can exist in the PostgreSQL internal in-memory structures with no identifier. Because Performance Insights samples internal in-memory structures once per second, low-latency queries might appear for only a single sample. If the query identifier isn't available for this sample, Performance Insights can't associate this statement with its statistics. The unknown value is represented with a dash (`-`).
For a description of the SQL statistics for the Amazon RDS engines, see [SQL statistics for Performance Insights](sql-statistics.md).
### Load by waits (AAS)
In **Top SQL**, the **Load by waits (AAS)** column illustrates the percentage of the database load associated with each top load item. This column reflects the load for that item by whatever grouping is currently selected in the **DB Load Chart**. For more information about Average active sessions (AAS), see [Average active sessions](USER_PerfInsights.Overview.ActiveSessions.md#USER_PerfInsights.Overview.ActiveSessions.AAS).
For example, you might group the **DB load** chart by wait states. You examine SQL queries in the top load items table. In this case, the **DB Load by Waits** bar is sized, segmented, and color-coded to show how much of a given wait state that query is contributing to. It also shows which wait states are affecting the selected query.
![\[DB load by waits\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_6.png)
### View SQL information
In the **Top SQL** table, you can open a statement to view its information. The information appears in the bottom pane.
![\[Top SQL table with literal query selected\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-sql-ids-open.png)
The following types of identifiers (IDs) that are associated with SQL statements:
+ **Support SQL ID** – A hash value of the SQL ID. This value is only for referencing a SQL ID when you are working with AWS Support. AWS Support doesn't have access to your actual SQL IDs and SQL text.
+ **Support Digest ID** – A hash value of the digest ID. This value is only for referencing a digest ID when you are working with AWS Support. AWS Support doesn't have access to your actual digest IDs and SQL text.
### Choose statistics preferences
You can control the statistics displayed in the **Top SQL** tab by choosing the **Preferences** icon.
![\[Statistics preferences\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-sql-ids-preferences-icon.png)
When you choose the **Preferences** icon, the **Preferences** window opens. The following screenshot is an example of the **Preferences** window.
![\[Preferences window\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-sql-ids-preferences.png)
To enable the statistics that you want to appear in the **Top SQL** tab, use your mouse to scroll to the bottom of the window, and then choose **Continue**.
For more information about per-second or per-call statistics for the Amazon RDS engines, see the engine specific SQL statistics section in [SQL statistics for Performance Insights](sql-statistics.md)
# Accessing more SQL text in the Performance Insights dashboard
By default, each row in the **Top SQL** table shows 500 bytes of SQL text for each SQL statement.
![\[500 bytes of SQL\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-top-sql-bytes.png)
When a SQL statement exceeds 500 bytes, you can view more text in the **SQL text** section below the **Top SQL** table. In this case, the maximum length for the text displayed in **SQL text** is 4 KB. This limit is introduced by the console and is subject to the limits set by the database engine. To save the text shown in **SQL text**, choose **Download**.
**Topics**
+ [
## Text size limits for Amazon RDS engines
](#sql-text-engine-limits)
+ [
# Setting the SQL text limit for Amazon RDS for PostgreSQL DB instances
](USER_PerfInsights.UsingDashboard.SQLTextLimit.md)
+ [
# Viewing and downloading SQL text in the Performance Insights dashboard
](view-download-text.md)
## Text size limits for Amazon RDS engines
When you download SQL text, the database engine determines its maximum length. You can download SQL text up to the following per-engine limits.
| DB engine | Maximum length of downloaded text |
| --- | --- |
| Amazon RDS for MySQL and MariaDB | The length is fixed at 4,096 bytes when the Performance Schema is enabled. If the Performance Schema isn't enabled, the length is fixed at 65,535 bytes. |
| Amazon RDS for Microsoft SQL Server | 4,096 characters |
| Amazon RDS for Oracle | 1,000 bytes |
The **SQL text** section of the Performance Insights console displays up to the maximum that the engine returns. For example, if MySQL returns at most 1 KB to Performance Insights, it can only collect and show 1 KB, even if the original query is larger. Thus, when you view the query in **SQL text** or download it, Performance Insights returns the same number of bytes.
If you use the AWS CLI or API, Performance Insights doesn't have the 4 KB limit enforced by the console. `DescribeDimensionKeys` and `GetResourceMetrics` return at most 500 bytes.
**Note**
`GetDimensionKeyDetails` returns the full query, but the size is subject to the engine limit.
# Setting the SQL text limit for Amazon RDS for PostgreSQL DB instances
Amazon RDS for PostgreSQL handles text differently. You can set the text size limit with the DB instance parameter `track_activity_query_size`. This parameter has the following characteristics:
Default text size
On Amazon RDS for PostgreSQL version 9.6, the default setting for the `track_activity_query_size` parameter is 1,024 bytes. On Amazon RDS for PostgreSQL version 10 or higher, the default is 4,096 bytes.
Maximum text size
The limit for `track_activity_query_size` is 102,400 bytes for Amazon RDS for PostgreSQL version 12 and lower. The maximum is 1 MB for version 13 and higher.
If the engine returns 1 MB to Performance Insights, the console displays only the first 4 KB. If you download the query, you get the full 1 MB. In this case, viewing and downloading return different numbers of bytes. For more information about the `track_activity_query_size` DB instance parameter, see [Run-time Statistics](https://www.postgresql.org/docs/current/runtime-config-statistics.html) in the PostgreSQL documentation.
To increase the SQL text size, increase the `track_activity_query_size` limit. To modify the parameter, change the parameter setting in the parameter group that is associated with the Amazon RDS for PostgreSQL DB instance.
**To change the setting when the instance uses the default parameter group**
1. Create a new DB instance parameter group for the appropriate DB engine and DB engine version.
1. Set the parameter in the new parameter group.
1. Associate the new parameter group with the DB instance.
For information about setting a DB instance parameter, see [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).
# Viewing and downloading SQL text in the Performance Insights dashboard
In the Performance Insights dashboard, you can view or download SQL text.
**To view more SQL text in the Performance Insights dashboard**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Performance Insights**.
1. Choose a DB instance.
1. Scroll down to the **Top SQL** tab in the Performance Insights dashboard.
1. Choose the plus sign to expand a SQL digest and choose one of the digest's child queries.
SQL statements with text larger than 500 bytes look similar to the following image.
![\[SQL statements with large text\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-large-text-1.png)
1. Scroll down to the **SQL text** tab.
![\[SQL information section shows more of the SQL text\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-large-text-2.png)
The Performance Insights dashboard can display up to 4,096 bytes for each SQL statement.
1. (Optional) Choose **Copy** to copy the displayed SQL statement, or choose **Download** to download the SQL statement to view the SQL text up to the DB engine limit.
**Note**
To copy or download the SQL statement, disable pop-up blockers.
# Viewing SQL statistics in the Performance Insights dashboard
In the Performance Insights dashboard, SQL statistics are available in the **Top SQL** tab of the **Database load** chart.
**To view SQL statistics**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the left navigation pane, choose **Performance Insights**.
1. At the top of the page, choose the database whose SQL statistics you want to see.
1. Scroll to the bottom of the page and choose the **Top SQL** tab.
1. Choose an individual statement or digest query.
![\[Viewing metrics for running queries\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_sql.png)
1. Choose which statistics to display by choosing the gear icon in the upper-right corner of the chart. For descriptions of the SQL statistics for the Amazon RDS engines, see [SQL statistics for Performance Insights](sql-statistics.md).
The following example shows the statistics preferences for Oracle DB instances.
![\[Preferences for metrics for running queries for Oracle DB instances\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_pref_oracle.png)
The following example shows the preferences for MariaDB and MySQL DB instances.
![\[Preferences for metrics for running queries for MariaDB and MySQL DB instances.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_pref_ams.png)
1. Choose Save to save your preferences.
The **Top SQL** table refreshes.
The following example shows statistics for an Oracle SQL query.
![\[Statistics for a SQL query\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_stats_oracle.png)