Analyzing execution plans using the Performance Insights dashboard for Amazon RDS
In the Amazon RDS Performance Insights dashboard, you can find information about execution plans for Oracle and SQL Server DB instances. You can use this information to know which plans contribute the most to DB load.
To analyze Oracle or SQL Server execution plans, see the following topics.
Analyzing execution plans
Overview of analyzing execution plans for Amazon RDS
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.
