Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Compare two executions plans in Oracle 19c using DBMS_XPLAN.COMPARE_PLANS

Posted by FatDBA on April 26, 2022

Hi All,

Today’s post is about one of the good featured added into Oracle 19c for DBMS_XPLAN package that provides an easy way to display the output of the EXPLAIN PLAN using a new function called COMPARE_PLANS. This function compares each plan in the list to a reference plan and returns a report and makes life of a DBA little easy.

The following example illustrates the usage of the COMPARE_PLANS function in 19c.

-- Lets create a sample table
SQL> CREATE TABLE bigtab (
   id         NUMBER,
   weight     NUMBER,
   adate      DATE
);  

Table created.

-- Insert some dummy rows into it
SQL> INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
       DBMS_RANDOM.RANDOM,
       SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
 FROM all_objects
/  

84217 rows created.

SQL> 


-- Lets query the table with our conditions 
SQL> select count(*) from bigtab where ID between 280 and 500;

  COUNT(*)
----------
     18564

SQL>
SQL> select count(*) from bigtab where ID not between 280 and 500;

  COUNT(*)
----------
     65653


-- All set lets get the SQLID for both of the SQLs that we ran
SQL> set linesize 400 pagesize 400
SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%from bigtab where ID%';

SQL> 

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
3k1b0y7scc8bd select count(*) from bigtab where ID not between :"SYS_B_0" and :"SYS_B_1"
51tfy4wqb5sg3 select count(*) from bigtab where ID between :"SYS_B_0" and :"SYS_B_1"

Now we have SQLIDs for both of the SQLs I ran in last session, let’s call DBMS_XPLAN.COMPARE_PLANS and compare both of the plans and see how the compare reports looks like and what all information it presents. There are multiple parameters available with the reference_plan and compare_plan_list, but to keep it simple I will be using only cursor_cache_object (to get all plans in the cursor cache generated for SQL ID) and plan_object_list(cursor_cache_object) which is a TYPE

Other available plan sources are:
plan_table_object(owner, plan_table_name, statement_id, plan_id) : for schedules
cursor_cache_object(sql_id, child_number) : For cursor cache
awr_object(sql_id, dbid, con_dbid, plan_hash_value) : for Hours or if you want to check for two different PHVs
sqlset_object (sqlset_owner, sqlset_name, sql_id, plan_hash_value) : for SQL Tuning Set
spm_object (sql_handle, plan_name) : For SQL Plan management
sql_profile_object (profile_name) : For SQL configuration file

Let’s go simple and will use cursor_cache_object with plan_object_list to get a simple plain execution plan compare report.

SQL>
SQL> VARIABLE v_rep CLOB
SQL> 
SQL> BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('3k1b0y7scc8bd', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('51tfy4wqb5sg3', NULL)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/  

PL/SQL procedure successfully completed.

SQL>



-- Now lets call the variable to see contents inside, the compare report.
SQL> set long 100000
SQL> COLUMN report FORMAT a200
SQL>
SQL> SELECT :v_rep REPORT FROM DUAL;

REPORT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 3k1b0y7scc8bd
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : select count(*) from bigtab where ID not between
                        :"SYS_B_0" and :"SYS_B_1"

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

 Plan Hash Value  : 2140185107

--------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes  | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |        |   82 |          |
|   1 |   SORT AGGREGATE     |        |     1 |     13 |      |          |
| * 2 |    TABLE ACCESS FULL | BIGTAB | 54028 | 702364 |   82 | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(("ID"<:SYS_B_0 OR "ID">:SYS_B_1))


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 51tfy4wqb5sg3
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : select count(*) from bigtab where ID between
                        :"SYS_B_0" and :"SYS_B_1"

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

 Plan Hash Value  : 3865534252

---------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |        |   79 |          |
|   1 |   SORT AGGREGATE      |        |     1 |     13 |      |          |
| * 2 |    FILTER             |        |       |        |      |          |
| * 3 |     TABLE ACCESS FULL | BIGTAB | 17969 | 233597 |   79 | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(:SYS_B_1>=:SYS_B_0)
* 3 - filter(("ID">=:SYS_B_0 AND "ID"<=:SYS_B_1))


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


Comparison Results (2):
-----------------------------
 1. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Some lines (id: 2) in the
    current plan are missing in the reference plan.
 2. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Some columns (ID, PARENT_ID,
    DEPTH) do not match between the reference plan (id: 2) and the current plan
    (id: 3).


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


SQL>
SQL>

This is great feature when the execution plans are messy and behemoth and just a glance is not enough and you want to compare two such execution plans. This will present a ‘comparison result’ at the end of the report like in our example the report says the Query Block with name SEL$1 which is an alias of BIGTAB in the current plan doesn’t have the line ID 2 (FILTER method in our case) and similar other observations.

Hope It Helped!
Prashant Dixit

8 Responses to “Compare two executions plans in Oracle 19c using DBMS_XPLAN.COMPARE_PLANS”

  1. Hemant K Chitale said

    Any specific reason why you have CURSOR_SHARING=FORCE ?

    • FatDBA said

      Hi Hemant,
      Thank you so much for reading the post and for your comment.
      I have two separate plans for both of the SQLs, could you please help me out what exactly you’re pointing out in the blog post for CUROSOR_SHRING.

      • Hemant K Chitale said

        The two SQLs are different (one does a BETWEEN and the other does a NOT BETWEEN). So that is not the issue.

        The fact that the SQLs are re-written by Oracle to have “SYS_B_0″ and :”SYS_B_1” as the Binds means that you have CURSOR_SHARING=FORCE (instead of the default EXACT). I was just wondering why you have CURSOR_SHARING=FALSE (nothing to do with COMPARE_PLANS)

      • FatDBA said

        Ahhh! I got it now. Yes, that’s right, default is EXACT. It was set to FORCE as earlier was trying something else for another test that I was performing on the same machine. Nothing to do with the post or the feature, yeah!

        Thanks for pointing it out.

  2. Ed said

    Hi Hemant K Chitale, how did you guess he is using using CURSOR_SHARING=FALSE 🙂
    I have tried FatDBA’s example and it didn’t come up with a different plan, and I have checked my CURSOR_SHARING=EXACT.

    • My reply to “FatDBA” on 28-April : “The fact that the SQLs are re-written by Oracle to have “SYS_B_0″ and :”SYS_B_1” as the Binds means that you have CURSOR_SHARING=FORCE (instead of the default EXACT).”

  3. Edwin Uy said

    Follow the example and EXPLAIN PLAN is the same, I have CURSOR_SHARING=EXACT. How did Hemant K Chitale knows you are using FORCE? 🙂

    • Hemant K Chitale said

      My reply to “FatDBA” on 28-April : “The fact that the SQLs are re-written by Oracle to have “SYS_B_0″ and :”SYS_B_1” as the Binds means that you have CURSOR_SHARING=FORCE (instead of the default EXACT).”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: