In most cases the first step in debugging a performance problem caused by a
plan change is to visually inspect both of the execution plans generated by the query optimizer. Usually the customer has a known plan that performed well and the
new plan that performs worse.
Visual inspection of plans is easy when the query is not too complex but becomes a tedious exercise when the query is complex (involving tens of joins, sub-queries, views, etc).
This article introduces a new plan comparison tool implemented in Oracle Database 11gR2.
1. Comparing Complex PlansPlan comparison is becoming a more critical issue for two key reasons:
- The majority of SQL queries are now generated by applications and they are becoming larger and more complex.
- The optimizer has kept pace with the complexity of the queries by
implementing newer and smarter algorithms to rearrange the user's queries.
In the increasingly complex optimizer world, entire tables can get eliminated from the plan, views can get merged, sub-queries can get unnested, expressions can be converted to virtual columns, complete predicates can be eliminated, new join methods can be introduced, and so on. Therefore, the query execution plan can potentially look radically different from the user's queries.
With increasing query complexity and query optimization, one thing has not
changed - developers, support engineers, DBAs, and consultants still visually inspect plans to figure out why one is different from the other. Worse case, they may have to look at the raw dump files from 10053 traces side by side.
2. Plan Comparison in Oracle Database 11gThe Plan Comparison (
Plan Diff) in Oracle Database 11gR2 addresses the issue of comparing complex plans. It uses optimizer kernel level information
to come up with the key reasons why one plan is different from the other.
Consider this simple query that joins the Sales and Customers tables.
Example 1
SELECT s.prod_id
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
The 11g plan for the query is shown below.
Plan Hash Value : 2489314924
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 960 | 4800 | 5 | 00:00:01 |
| 1 | PARTITION RANGE ALL | | 960 | 4800 | 5 | 00:00:01 |
| 2 | TABLE ACCESS FULL | SALES | 960 | 4800 | 5 | 00:00:01 |
-----------------------------------------------------------------------
A cursory look at the plan tells us that the Customers table has vanished from
the query. This means that the optimizer has optimized the query to a
point where the customers table has been deemed redundant.
Now, lets look at the 10g plan:
Plan Hash Value : 2774269550
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 960 | 14400 | 8 | 00:00:01 |
| * 1 | HASH JOIN | | 960 | 14400 | 8 | 00:00:01 |
| 2 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 630 | 3150 | 2 | 00:00:01 |
| 3 | PARTITION RANGE ALL | | 960 | 9600 | 5 | 00:00:01 |
| 4 | TABLE ACCESS FULL | SALES | 960 | 9600 | 5 | 00:00:01 |
----------------------------------------------------------------------------------
The 10g plan is more conventional - all the tables in the users query are
still present.
Typically, the first step in comparing two plans would be to look at the row
sources. But, this won't yield any more information for us, in this case, because the entire Customers table has vanished in the 11g plan. Without Plan Comparison, we would have to look into the optimizer trace. Deep in the trace file, we would find a line telling us that JOIN ELIMINATION was successful in 11g.
By using Plan Comparison, we can get the same information by calling one of the new functions in the well known DBMS_XPLAN package.
3. Invoking Plan Comparison APIs In the example below, we will use the dbms_xplan.diff_plan_outline function to compare our two plans. The function takes three arguments,
- SQL Statement text
- Hints to reproduce the first plan
- Hints to reproduce the second plan
In our example we only need to specify one hint to reproduce each plan,
OPTIMIZER_FEATURES_ENABLE. By setting it to 11.1.0.7 and 10.1.0.3 we will be able to reproduce each plan.
Step #1: Create a package to house our SQL statement
connect / as sysdba
grant connect, dba, advisor, resource, query rewrite to sh;
connect sh/sh;
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 1000
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 8000
set serveroutput on
SET LONG 20000
SET DEFINE OFF
-- setup the query text
create or replace package define_vars is
sql_stmt varchar2(2000) := q'#
select s.prod_id
from sales s, customers c
where s.cust_id = c.cust_id
#';
end;
/
PL/SQL procedure successfully completed.
Step #2: Run the
Diff_Plan_Outline function to generate a report
DECLARE
t_id varchar2(50);
BEGIN
t_id := dbms_xplan.diff_plan_outline(
sql_text => define_vars.sql_stmt,
outline1 => 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
outline2 => 'OPTIMIZER_FEATURES_ENABLE(''10.1.0.3'')');
END;
/
And you should see something like the following as output:
URL:
http://host.my.com:portnumber/orarep/plandiff/all?task_id=31&format=html&method=qbreg
PL/SQL procedure successfully completed.
Note the
task_id number as it will be used in the next API to fetch the
comparison report. In this case the
task_id=31
Step #3: Run the
DBMS_REPORT.Get_Report function to generate the report.
You now can retrieve and display the report in TEXT format by changing the '&format=html' output you got above to '&format=text'.
set define off;
set long 1000000;
select dbms_report.get_report(
'/orarep/plandiff/all?task_id=31&format=text&method=qbreg')
from dual;
Remember to replace the task_id value with the one you see after running the report
generating task above.
4. The Plan Diff ReportThe first part of the report tells you the first points of deviation between
the two plans at a Query Block level. In our example, the report tells
us that 'JOIN REMOVED FROM QUERY BLOCK' is the difference between two plans.
In more complicated plans, there might be more than one points of deviation.
Each of those points will be show in the first part of the report.
After the Query Block level Diff, the report contains the Join Order Diff.
In this sample query, there is no join order diff so this section is empty.
Report Details: SQL Plan Comparison Query Block Diff
------------------------------------------------------------------------
| Query Block | Plan 1 | Plan 2 | Diff |
------------------------------------------------------------------------
| .. SEL$5B7CE5FC | Yes | NA | .. JOIN REMOVED FROM QUERY BLOCK |
------------------------------------------------------------------------
Let's look at two more examples to help illustrate the other areas of the report that can be of interest when comparing two plans.
Example2: Plan Diff with
Join Order difference
select count(*)
from products, costs
where products.prod_list_price = costs.unit_cost;
Again this query is a simple two table join but there are two possible join orders for this statement. By using plan plan_diff we can compare the two alternative plans.
Step #1: Create a package to house our SQL statement
SQL> create or replace package define_vars is
2 sql_stmt varchar2(2000) := q'#
3 select count(*)
4 from products, costs
5 where products.prod_list_price = costs.unit_cost
6 #';
7 end;
8 /
Package created.
Step #2: Run the
Diff_Plan_Outline function to generate a report. Again in this case we will pass in two hints to generate the plans with both join orders.
SQL> DECLARE
2 t_id varchar2(50);
3 BEGIN
4 t_id := dbms_xplan.diff_plan_outline(sql_text =>define_vars.sql_stmt,
5 outline1 => 'LEADING(@"SEL$1" "COSTS"@"SEL$1" "PRODUCTS"@"SEL$1")',
6 outline2 => 'LEADING(@"SEL$1" "PRODUCTS"@"SEL$1" "COSTS"@"SEL$1")');
7 end;
8 /
Step #3: Run the
DBMS_REPORT.Get_Report function to generate the report.
set define off;
set long 1000000;
select dbms_report.get_report(
'/orarep/plandiff/all?task_id=64&format=text&method=qbreg')
from dual;
At the start of the report is the Query Block level Diff section, followed by the Join Order Diff section. For our example the output of the Join Order Diff section is as follows
Join Order Diff
-------------------------------------------------
| Query Block | SQL Plan 1 | SQL Plan 2 |
-------------------------------------------------
| SEL$1 | COSTS,PRODUCTS | PRODUCTS,COSTS |
-------------------------------------------------
Plan Hash Value : 3848794364
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| * 2 | HASH JOIN | |
| 3 | PARTITION RANGE ALL | |
| 4 | TABLE ACCESS FULL | COSTS |
| 5 | TABLE ACCESS FULL | PRODUCTS |
--------------------------------------------
Plan Hash Value : 1193021615
--------------------------------------------
| Id | Operation |Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| * 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | PRODUCTS |
| 4 | PARTITION RANGE ALL | |
| 5 | TABLE ACCESS FULL | COSTS |
--------------------------------------------
It shows the different orders in a summary table followed by the two different plans.
Example 3: Plan Diff with
different Join Methods
select /*+ LEADING(@"SEL$1" "COSTS"@"SEL$1" "PRODUCTS"@"SEL$1") */ count(*)
from products, costs
where products.prod_list_price = costs.unit_cost;
In this example we use the same query again except this time we have added a LEADING hint to force a particular join order. We will now use plan diff to look at the two possible join types for this statement.
Step #1: Create a package to house our SQL statement
SQL> create or replace package define_vars is
2 sql_stmt varchar2(2000) := q'#
3 select /*+ LEADING(@"SEL$1" "COSTS"@"SEL$1" "PRODUCTS"@"SEL$1") */ count(*)
4 from products, costs
5 where products.prod_list_price = costs.unit_cost
6 #';
7 end;
8 /
Package created.
Step #2: Run the
Diff_Plan_Outline function to generate a report
SQL>DECLARE
2 t_id varchar2(50);
3 BEGIN
4 t_id := dbms_xplan.diff_plan_outline(sql_text => define_vars.sql_stmt,
5 outline1 => 'USE_MERGE(products)',
6 outline2 => 'USE_HASH(@"SEL$1" "PRODUCTS"@"SEL$1")');
7 end;
8 /
Step #3: Run the
DBMS_REPORT.Get_Report function to generate the report.
set define off;
set long 1000000;
select dbms_report.get_report(
'/orarep/plandiff/all?task_id=245&format=text&method=qbreg')
from dual;
This time in the report the first two sections of the report are empty but the third section called Join Method Diff is populated (indicating this is where the difference occurs. It shows the two different join methods used and their corresponding execution plans.
Task Information: Workload Information:
------------------------------------- -------------------------------------
Task Name : TASK_245
Task Owner : SH
Description : diff_plan_outline
Report Details: SQL Plan Comparison Query Block Diff
------------------------------------------------
| Query Block | SQL Plan 1 | SQL Plan 2 | Diff |
------------------------------------------------
Join Order Diff
-----------------------------------------
| Query Block | SQL Plan 1 | SQL Plan 2 |
-----------------------------------------
Join Method Diff
--------------------------------------------------------------
| Query Block | Table to Join | SQL Plan 1 | SQL Plan 2
|
--------------------------------------------------------------
| SEL$1 | "PRODUCTS"@"SEL$1" | MERGE | HASH |
--------------------------------------------------------------
Plan Hash Value : 1572665727
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | PARTITION RANGE ALL | |
| 5 | TABLE ACCESS FULL | COSTS |
| * 6 | SORT JOIN | |
| 7 | TABLE ACCESS FULL | PRODUCTS |
---------------------------------------------
Plan Hash Value : 3848794364
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| * 2 | HASH JOIN | |
| 3 | PARTITION RANGE ALL | |
| 4 | TABLE ACCESS FULL | COSTS |
| 5 | TABLE ACCESS FULL | PRODUCTS |
--------------------------------------------
