PostgreSQL Live Plans feature in DPA

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

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

1. From the DPA home page, click the name of the PostgreSQL database instance on which the SQL statement ran. DPA displays the Trends charts for the PostgreSQL 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. 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 PostgreSQL:

     • Analyze: Execute the SQL and show actual run times and other statistics.

     • Costs: Include estimated information about costs of each plan node as well as number rows and other details.

     • Buffers: Include information on buffer usage.

     • Verbose: Display verbose format of information for the plan.

     Note: These options are described in additional detail in the PostgreSQL documentation.

   • 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 PostgreSQL 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 PostgreSQL. The only exception is if the user that you selected did not have the permissions required to run the SQL statement.