What's Changed between my New Query Plan and the Old One?

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 Plans

Plan 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 11g

The 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 Report

The 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 |
--------------------------------------------


Comments:

The article is interesting and informative.
Few questions:
1. Where can we find documentation for dbms_xplan.diff_plan_outline function?

2. Is it possible to compare two plans based on Plan hash value?
For example, we want to compare two plans by say, making one or more indexes invisible OR by changing stats like histograms on one/more columns?

3. Related to this, is there a utility that can compare two 10053 trace files and bring out plan choice differences and reasons?

thanks.

Posted by guest on February 08, 2012 at 03:23 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today