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
Tales From A Lazy Fat DBA 
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.
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.
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).”
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).”