Applications Systems

MySQL Live Plans feature in DPA

This article describes the MySQL live plan feature in DPA.

First published date

11/12/2018 8:50 PM

Last published date

4/8/2020 12:12 AM

Overview

The DPA Live Plans feature lets you generate the execution plan for SQL statements that ran against a MySQL database instance. Use this feature to troubleshoot performance problems with a SQL statement.

Product section

Database Performance Analyzer

Resolution

To generate a MySQL live plan, complete the following steps:

  1. From the DPA homepage, click the name of the MySQL database instance on which the SQL statement ran.

    DPA displays the Trends charts for the MySQL database instance.

  2. On the Top SQL Statements chart, click a bar that represents a day when the SQL statement ran.

    The hourly interval is displayed.

  3. Click a bar that represents an hour when the SQL statement ran.

    DPA displays horizontal bars representing the top SQL statements that ran during that hour.

  4. In DPA 11.1 or earlier, click the bar or hash that represents the SQL statement.

    In DPA 12.0 or later, open the "Filter on this Query" drop-down menu, and select the SQL statement.

    DPA displays the normalized SQL text on the SQL Text tab.

    Note: DPA uses normalized SQL to group similar SQL statements. DPA replaces the literal strings and numbers in each SQL statement with the wildcard character ?. This enables DPA to aggregate data about SQL statements whose only differences are literal strings and numbers. You cannot run normalized SQL.

  5. Click the Live Plans tab.

    The Example SQL Text section displays the SQL that will run to generate the live plan. You can edit the SQL text.

    Note: In some cases, the Live Plans tab might also show the following:

    • Examples: In addition to the normalized SQL, DPA collects one or more examples of the actual SQL statement that ran. By default, DPA collects only one example per normalized SQL statement, and that example is displayed automatically (as shown in the screenshot above). However, if the advanced option SQL_Example_Count has been set to more than 1, the Live Plans tab lists the examples under "Select a SQL to display". If this section is shown, select an example to use to generate the plan.

    • Notifications: If the example SQL statement cannot be run (for example, because the SQL text was truncated), DPA displays a message. If you receive a message, edit the example SQL statement so that it is a valid statement that can be run to generate a plan.

  6. If necessary, edit the SQL text.

    Note: You can replace wildcards or other values in the example with any value that is syntactically correct. The values do not have to be valid. For example, DPA can generate a live plan using the example below, even though the table type 'xxxxxx' and schema 'yyyyyy' do not exist.

  7. Click Run Live Plan. The Live Plan Options dialog box opens.

  8. Specify the following options: 

    • Explain plan options: A choice of which version of the EXPLAIN command to run is provided for MySQL 5.7.2 and earlier database instances. For these database instances, running EXPLAIN EXTENDED or EXPLAIN PARTITIONS returns additional information. 
    • Show warnings: Select this option to execute the SHOW WARNINGS command after generating the explain plan. This option displays additional information from MySQL. Warnings don't necessarily indicate that something is wrong.
    • Database: DPA populates the Database drop-down menu with databases detected in the selected time slice. To specify a different database, click the radio button next to the blank text field and enter the name of the database.
    • Run Plan as User: The DPA monitoring user is selected by default. To generate the explain plan as a different user (for example, if the DPA monitoring user does not have the required privileges), specify a user with the required privileges on the monitored MySQL instance.

  9. Click Run to execute the plan. The generated plan and any warnings are shown at the bottom of the window.

Note about errors: 
Most errors that DPA displays when you generate a plan come directly from MySQL. The only exception is if the user that you selected did not have the permissions required to run the SQL statement.