Overview of analyzing execution plans - Amazon Relational Database Service
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Overview of analyzing execution plans

You can use the Amazon RDS Performance Insights dashboard to know which plans contribute the most to DB load for Oracle and SQL Server DB instances.

For example, the top SQL statements at a given time might be using the plans shown in the following table.

Top SQL Plan

SELECT SUM(amount_sold) FROM sales WHERE prod_id = 10

Plan A

SELECT SUM(amount_sold) FROM sales WHERE prod_id = 521

Plan B

SELECT SUM(s_total) FROM sales WHERE region = 10

Plan A

SELECT * FROM emp WHERE emp_id = 1000

Plan C

SELECT SUM(amount_sold) FROM sales WHERE prod_id = 72

Plan A

With the plan feature of Performance Insights, you can do the following:

  • Find out which plans are used by the top SQL queries.

    For example, you might find out that most of the DB load is generated by queries using plan A and plan B, with only a small percentage using plan C.

  • Compare different plans for the same query.

    In the preceding example, three queries are identical except for the product ID. Two queries use plan A, but one query uses plan B. To see the difference in the two plans, you can use Performance Insights.

  • Find out when a query switched to a new plan.

    You might see that a query used plan A and then switched to plan B at a certain time. Was there a change in the database at this point? For example, if a table is empty, the optimizer might choose a full table scan. If the table is loaded with a million rows, the optimizer might switch to an index range scan.

  • Drill down to the specific steps of a plan with the highest cost.

    For example, the for a long-running query might show a missing a join condition in an equi-join. This missing condition forces a Cartesian join, which joins all rows of two tables.

You can perform the preceding tasks by using the plan capture feature of Performance Insights. Just as you can slice queries by wait events and top SQL, you can slice them by the plan dimension.