You can use Performance Hub to monitor and analyze SQL performance and obtain greater visibility into performance issues.

Performance Hub for HeatWave and External MySQL DB systems displays SQL statement information for the selected time period. Using Performance Hub, you can improve the performance of your database applications by monitoring query performance. It enables you to accurately pinpoint SQL code that is the root cause of a slow down; and improve SQL code during active development as well as continuously monitor and tune queries running on production systems.

To go to Performance Hub, click Performance Hub on the MySQL database details page in Database Management. For information, see Monitor a Single HeatWave or External MySQL DB System.

In Performance Hub, select an option in the Last seen drop-down list to specify the duration for which you want to monitor SQL activity and view the last seen SQL statements. If you select the Custom option in the Last seen drop-down list, you can specify a custom duration within the last seven-day period. The Time range field displays the time period depending on the selected Last seen option. You can also click the Time range field to specify a custom duration within the last seven-day period. To refresh the data in Performance Hub, click Refresh in the upper-right corner.

Based on the selected time period, relevant information is displayed in the following charts and section:

• Average statement latency (seconds): Displays the average latency (in seconds) for the SQL statements executed against the DB system.

  Click Select charts in the upper-right corner and select Statement count to view the Statement count chart. This chart displays the total number of SQL statements executed against the DB system, during the selected time period. For a HeatWave DB system, the Statement count chart also displays the number of SQL statements offloaded to HeatWave for execution.

• Top 100 by <indicator>: Displays the aggregated summary information for the top SQL statements based on a selected indicator such as average statement latency, total execution count, or execution status. In the Top 100 by <indicator> section, you can select an indicator to sort and monitor SQL statements by that performance measure, helping you quickly identify expensive statements. Database Management uses the data stored in the events_statements_summary_by_digest table in the MySQL Performance Schema. The SQL statements listed in the Top 100 by <indicator> section are normalized statements, and the data shown is aggregated from the time each statement was first seen. Based on the data, the slowest, most frequent, or resource-intensive SQL statements are displayed, and you can use this information to identify the statements causing performance issues.

  Note that some indicators in the Top 100 by <indicator> drop-down list are displayed as default columns. If you choose to sort the SQL statements using an indicator that is not a default column, for example, First seen, it will be added as the last column. To further customize the information displayed in this section, select or deselect options in the Columns drop-down list. For a HeatWave-enabled DB system, you can also filter SQL statements by selecting an option in the HeatWave offload status drop-down list.

  Here's the list of indicators or columns in the Top 100 by <indicator> section, in alphabetical order.

  Indicator/Column	Description
  Average statement latency (default indicator)	The average time the SQL statement takes to execute, calculated across all executions of the statement.
  Database	The name of the database on which the SQL statement was executed or "-" if no database is specified. This is a default column and is not included in the list of indicators.
  Execution status	The current state or result of the execution of the SQL statement. If an error occurs during the execution of a SQL statement, the Status column displays an error icon (). Click this icon to view the error code, the number of occurrences, and the error message in the Digest error details panel. Note that error details may not be displayed if certain variables or tables are not configured or enabled in your MySQL setup.
  First seen	The time at which the SQL statement was first seen.
  HeatWave offloaded	The number of SQL statement executions that were offloaded to the HeatWave in-memory query accelerator. Note: In addition to the data from the events_statements_summary_by_digest table, HeatWave offload status is listed in the indicator drop-down list for HeatWave-enabled DB systems.
  Last seen	The time at which the SQL statement was most recently seen.
  No good index used	The total number of times MySQL could not find an effective index to execute the SQL statement, leading to less efficient execution plans and slower performance.
  No index used	The total number of times no index was used to execute the SQL statement.
  Query	The normalized SQL statement. This is a default column and is not included in the list of indicators. Click the statement to view SQL details.
  Temporary tables	The total number of internal temporary tables, created in-memory or on disk by the SQL statement.
  Total execution count	The total number of times the SQL statement has been executed.
  Total rows affected	The total number of rows modified by the SQL statement.
  Total rows examined	The total number of rows examined by the SQL statement.
  Total rows sent	The total number of rows returned by the SQL statement.
  Total statement latency	The total time taken for all executions of the SQL statement.

  On reviewing the SQL statements listed by the selected performance indicator, click the link in the Query column to examine a single SQL statement of interest in the SQL details panel. The SQL details panel has two tabs:

  • Query analyzer: On this tab, you can:
    • Use the statement digest ID to write a query and obtain additional information from the DB system.
    • View the normalized SQL statement.
    • Monitor detailed information about the execution time, number of rows, and temporary tables. For example, you can monitor the number of rows that were examined or returned and the number of temporary tables that were created.

    For information on statement digests, see Performance Schema Statement Digests and Sampling.

  • Explain plan: On this tab, you can:
    • View the details of a sample SQL statement, such as when it was executed, the time taken, and the actual SQL with literal values that were passed at execution time.
    • Monitor the execution plan generated for the SQL statement, which provides valuable insights into how the SQL statement is executed. By visualizing the execution strategy, you can identify performance bottlenecks, such as full table scans or inefficient joins, and optimize the statements.

      By default, a graphical representation of the EXPLAIN output is displayed, making it easier to understand the execution plan, identify inefficiencies and make informed decisions for optimization. The graphical explain plan displays the execution plan as a tree, where each node represents a specific operation in the execution process, such as table scans, joins, and sorts. The nodes are color-coded to represent different types of operations, and you can click on individual nodes for more information about each step. When viewing the graphical explain plan, click Rotate to rotate the diagram counterclockwise; you can also use the mouse wheel, touchpad, or the +/- keys to zoom in and out of the explain plan diagram.

      In the View option drop-down list, select Tabular explain plan to view the execution plan, or the sequence of operations used to execute a statement in a tabular format. This table provides the list of operations and the following details:

      • Table name: The name of the table being accessed during execution.
      • Type: The type of access method used to access the table during execution.
      • Key: The index used by the statement, if any.
      • Ref: If the statement involves a join, the columns or constants that are being compared to the key.
      • Rows: The estimated or actual number of rows that the operation is expected to process or return.
      • Filtered: The percentage of rows that are expected to be filtered based on the WHERE clause.
      • Cost: The estimated cost of executing the operation.

      To customize the information displayed in the Tabular explain plan view, select or deselect columns in the Columns drop-down list.