Oracle Database - Compare Execution Plans

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

Understanding SQL Plans is a very complex subject for DBA's , it is complex because of two reasons , oneis that most DBA's have no idea on what the SQL is intended to do and second is deciphering the SQL Plan output and understanding if they are are actually meaning anything.

I have noticed multiple cases where there are many SQL Plan's for a particular SQL but DBA's do not really have the necessary tools to actually compare the plans. Most DBA's would pin the SQL which takes the least time but they never had the opportunity to compare SQL Plans before doing that.

Compare SQL Plans ( from Oracle 19c )

Let's say an SQL 0pvp4726mvx5u with an multiple execution plans has been identified with child numbers 0 and 1 respectively each having a particular plan_hash_value.

From Oracle 19c DBA's have the power compare how the child numbers 0 and 1 behave with each of its execution

An example below to compare execution plans using dbms_xplan.compare_plans

The Oracle documentation for this package is here

var l_cmp clob;
begin
                :l_cmp := dbms_xplan.compare_plans(
                 reference_plan    => cursor_cache_object('0pvp4726mvx5u', 0),
                 compare_plan_list => plan_object_list(cursor_cache_object('0pvp4726mvx5u', 1)),
                 type              => 'TEXT',
                 level             => 'TYPICAL',
                 section           => 'ALL'
               ); 
end;
/
print l_cmp

The above code snippet would generate the compare sql plan report as below

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SYS
  Total number of plans  : 2
  Number of findings     : 1
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 0pvp4726mvx5u
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "UAT1DBUSER"
 SQL Text               : /* SQL Analyze(12,1) */ SELECT a.STAT1,
                        a.STATUS, COUNT(a.STAT1) AS RECORDCOUNT FROM
                        XYZ1_TABLE4567 a inner join ABCD_EFGHIJKL_TABLE_123 b
                        on a.re2id = b.re1id or a.re2id = b.xe2id
                        where a.TYPE ='01' AND
                        TRUNC(a.LASTUPDATEON) BETWEEN TO_DATE('01-01-2023',
                        'DD/MM/YYYY') AND TO_DATE('15-04-2024', 'DD/MM/YYYY')
                        GROUP BY a.STAT1, a.STATUS ORDER BY
                        a.STAT1

Plan
-----------------------------

 Plan Hash Value  : 1774313814

----------------------------------------------------------------------------------------------------------
| Id   | Operation                         | Name                    | Rows  | Bytes  | Cost  | Time     |
----------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                  |                         |       |        | 78128 |          |
|    1 |   SORT GROUP BY                   |                         |    86 |   1806 | 78128 | 00:00:04 |
|    2 |    VIEW                           | VW_ORE_4C2BA79E         |  2865 |  60165 | 78127 | 00:00:04 |
|    3 |     UNION-ALL                     |                         |       |        |       |          |
|  * 4 |      HASH JOIN                    |                         |  2472 | 111240 | 76720 | 00:00:03 |
|  * 5 |       TABLE ACCESS FULL           | XYZ1_TABLE4567          |  2472 | 101352 | 76100 | 00:00:03 |
|  * 6 |       TABLE ACCESS FULL           | ABCD_EFGHIJKL_TABLE_123 | 53391 | 213564 |   620 | 00:00:01 |
|    7 |      NESTED LOOPS                 |                         |   393 |  18471 |  1407 | 00:00:01 |
|    8 |       NESTED LOOPS                |                         |   393 |  18471 |  1407 | 00:00:01 |
|  * 9 |        TABLE ACCESS FULL          | ABCD_EFGHIJKL_TABLE_123 |   393 |   2358 |   620 | 00:00:01 |
| * 10 |        INDEX UNIQUE SCAN          | XYZ1_TABLE4567_PK       |     1 |        |     1 | 00:00:01 |
| * 11 |       TABLE ACCESS BY INDEX ROWID | XYZ1_TABLE4567          |     1 |     41 |     2 | 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("A"."re2id"="B"."re1id")
* 5 - filter(("A"."TYPE"='01' AND TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))>=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
  TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))<=TO_DATE(' 2024-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 6 - filter("B"."re1id" IS NOT NULL)
* 9 - filter("B"."xe2id" IS NOT NULL)
* 10 - access("A"."re2id"="B"."xe2id")
* 10 - filter(LNNVL("A"."re2id"="B"."re1id"))
* 11 - filter(("A"."TYPE"='01' AND TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))>=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
  TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))<=TO_DATE(' 2024-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 0pvp4726mvx5u
 Child Number           : 1
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "UAT1DBUSER"
 SQL Text               : /* SQL Analyze(12,1) */ SELECT a.STAT1,
                        a.STATUS, COUNT(a.STAT1) AS RECORDCOUNT FROM
                        XYZ1_TABLE4567 a inner join ABCD_EFGHIJKL_TABLE_123 b
                        on a.re2id = b.re1id or a.re2id = b.xe2id
                        where a.TYPE ='01' AND
                        TRUNC(a.LASTUPDATEON) BETWEEN TO_DATE('01-01-2023',
                        'DD/MM/YYYY') AND TO_DATE('15-04-2024', 'DD/MM/YYYY')
                        GROUP BY a.STAT1, a.STATUS ORDER BY
                        a.STAT1

Plan
-----------------------------

 Plan Hash Value  : 2833413552

----------------------------------------------------------------------------------------------------------
| Id   | Operation                         | Name                    | Rows  | Bytes  | Cost  | Time     |
----------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                  |                         |       |        | 78128 |          |
|    1 |   SORT GROUP BY                   |                         |    86 |   1806 | 78128 | 00:00:04 |
|    2 |    VIEW                           | VW_ORE_4C2BA79E         |  2865 |  60165 | 78127 | 00:00:04 |
|    3 |     UNION-ALL                     |                         |       |        |       |          |
|  * 4 |      HASH JOIN                    |                         |  2472 | 111240 | 76720 | 00:00:03 |
|  * 5 |       TABLE ACCESS FULL           | ABCD_EFGHIJKL_TABLE_123 | 53391 | 213564 |   620 | 00:00:01 |
|  * 6 |       TABLE ACCESS FULL           | XYZ1_TABLE4567          |  2472 | 101352 | 76100 | 00:00:03 |
|    7 |      NESTED LOOPS                 |                         |   393 |  18471 |  1407 | 00:00:01 |
|    8 |       NESTED LOOPS                |                         |   393 |  18471 |  1407 | 00:00:01 |
|  * 9 |        TABLE ACCESS FULL          | ABCD_EFGHIJKL_TABLE_123 |   393 |   2358 |   620 | 00:00:01 |
| * 10 |        INDEX UNIQUE SCAN          | XYZ1_TABLE4567_PK       |     1 |        |     1 | 00:00:01 |
| * 11 |       TABLE ACCESS BY INDEX ROWID | XYZ1_TABLE4567          |     1 |     41 |     2 | 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("A"."re2id"="B"."re1id")
* 5 - filter("B"."re1id" IS NOT NULL)
* 6 - filter(("A"."TYPE"='01' AND TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))>=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
  TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))<=TO_DATE(' 2024-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 9 - filter("B"."xe2id" IS NOT NULL)
* 10 - access("A"."re2id"="B"."xe2id")
* 10 - filter(LNNVL("A"."re2id"="B"."re1id"))
* 11 - filter(("A"."TYPE"='01' AND TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))>=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
  TRUNC(INTERNAL_FUNCTION("A"."LASTUPDATEON"))<=TO_DATE(' 2024-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))


Comparison Results (1):
-----------------------------
 1. Query block SET$669E0EF5_1: Join order is different at position 1
    (reference plan: "A"@"SET$669E0EF5_1", current plan: "B"@"SET$669E0EF5_1").


---------------------------------------------------------------------------------------------

In this case though we do not see much of a difference as the cost of both the sql's are 78128. But it gives us the ability to compare plans , identify what is different betwen them . These are especially useful for complex SQL's and DBA's can provide relevant updates to the devlopment teams.

The importantaspect is the the *Comparison results * section which shows how the SQL query plans are different

Comparison Results (1):
-----------------------------
 1. Query block SET$669E0EF5_1: Join order is different at position 1
    (reference plan: "A"@"SET$669E0EF5_1", current plan: "B"@"SET$669E0EF5_1").

The above example does not tune any SQL but helps compare 2 SQL plans a very handy tool that DBA's can use , this can be used as a check to do a comparison before pinning the SQL plan with the lowest execution times.