Different execution plans

Toad for Oracle - Beta
1

If I select "Display mode / DBMS_XPlan" and press CTRL-E I get:

----------------------------------------------------
| Id  | Operation                                  |
----------------------------------------------------
|   0 | MERGE STATEMENT                            |
|   1 |  MERGE                                     |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED      |
|*  3 |    INDEX RANGE SCAN                        |
|   4 |   VIEW                                     |
|   5 |    NESTED LOOPS OUTER                      |
|   6 |     VIEW                                   |
|   7 |      NESTED LOOPS                          |
|   8 |       NESTED LOOPS                         |
|   9 |        NESTED LOOPS                        |
|* 10 |         HASH JOIN                          |
|  11 |          TABLE ACCESS FULL                 |
|  12 |          MERGE JOIN CARTESIAN              |
|* 13 |           TABLE ACCESS FULL                |
|  14 |           BUFFER SORT                      |
|  15 |            TABLE ACCESS FULL               |
|  16 |         TABLE ACCESS BY INDEX ROWID BATCHED|
|* 17 |          INDEX RANGE SCAN                  |
|* 18 |        INDEX UNIQUE SCAN                   |
|* 19 |       TABLE ACCESS BY INDEX ROWID          |
|  20 |     TABLE ACCESS BY INDEX ROWID            |
|* 21 |      INDEX UNIQUE SCAN                     |
----------------------------------------------------

If I select "Display mode / Tree" and press CTRL-E I get a different plan

MERGE STATEMENT  RULE Cost: 204  Bytes: 2.871  Cardinality: 11  											
	22 MERGE 
		2 TABLE ACCESS BY INDEX ROWID BATCHED  Cost: 4  Bytes: 41  Cardinality: 1  									
			1 INDEX RANGE SCAN  Cost: 1  Cardinality: 66  								
		21 VIEW  									
			20 NESTED LOOPS OUTER  Cost: 204  Bytes: 3.278  Cardinality: 11  								
				17 VIEW  Cost: 175  Bytes: 2.343  Cardinality: 11  							
					16 NESTED LOOPS  Cost: 152  Bytes: 2.244  Cardinality: 11  						
						14 NESTED LOOPS  Cost: 152  Bytes: 2.244  Cardinality: 34  					
							12 NESTED LOOPS  Cost: 88  Bytes: 4.012  Cardinality: 34  				
								9 NESTED LOOPS  Cost: 10  Bytes: 88  Cardinality: 1  			
									6 NESTED LOOPS  Cost: 8  Bytes: 53  Cardinality: 1  		
										3 TABLE ACCESS FULL  Cost: 6  Bytes: 27  Cardinality: 1  	
										5 TABLE ACCESS BY INDEX ROWID BATCHED  Cost: 2  Bytes: 156  Cardinality: 6  	
											4 INDEX RANGE SCAN  Cost: 1  Cardinality: 6  
									8 TABLE ACCESS BY INDEX ROWID BATCHED  Cost: 2  Bytes: 70  Cardinality: 2  		
										7 INDEX RANGE SCAN  Cost: 1  Cardinality: 2  	
								11 TABLE ACCESS BY INDEX ROWID BATCHED  Cost: 78  Bytes: 7.920  Cardinality: 264  			
									10 INDEX RANGE SCAN  Cost: 2  Cardinality: 598  		
							13 INDEX UNIQUE SCAN  Cost: 1  Cardinality: 1  				
						15 TABLE ACCESS BY INDEX ROWID  Cost: 3  Bytes: 85  Cardinality: 1  							
					18 INDEX UNIQUE SCAN  Cost: 2  Cardinality: 1

Any idea what's causing this?

Dirk

3

Hm, I see what you mean...they start to differ around 10-12 lines down.

The tree view gets the plan info from either the plan_table or from v$sql_plan, depending on if it's a fresh explain plan or from the cache.

Can you turn on spool sql for each and see if we are explaining the same thing? Maybe the cursor number or sql id is different for each one.

4

If I do CTRL-E with "Display Mode" set to "Tree", the following is executed:

explain plan set statement_id='mt_admin:051926082343' For
<sql statement redacted>
:b2(Unknown,)=<NULL> 
:b1(Unknown,)=<NULL> 

----------------------------------
Select *
From PLAN_TABLE
Where statement_id = :STATEMENT_ID
order by id;
:STATEMENT_ID(VARCHAR[21],IN)='mt_admin:051926082343'

If I then switch to "DBMS_XPlan" this is executed:

select * from table(dbms_xplan.display(table_name => 'PLAN_TABLE',statement_id => 'mt_admin:051926082343', format => 'ALL, ALLSTATS, LAST, ROWS, BYTES, COST, OUTLINE, PARTITION, PARALLEL, PEEKED_BINDS, PREDICATE, PROJECTION, ALIAS, REMOTE, NOTE'));

Interestingly, the same execution plan is then displayed.

But If I press CTRL-E while "Display Mode" is set to "DBMS_XPlan" the following is executed:

declare
  v_ignore raw(100);
  v_oldhash number;
  v_hash number;
begin
  v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);
  :outHash := v_hash;
end;
:SQLText(VARCHAR[2276],IN)='<sql statement redacted>' 
:outHash(FLOAT,OUT)=24666573 

----------------------------------
select min(child_number) cn
from v$sql_plan
Where hash_value = :sqlhv;
:sqlhv(VARCHAR[8],IN)='24666573' 
-- Rows fetched: 1

----------------------------------
WITH display_map AS
  (SELECT X.*
   FROM   v$sql_plan,
          XMLTABLE ('/other_xml/display_map/row'
                    passing case when other_xml IS NULL then null else XMLTYPE(other_xml ) end
                    COLUMNS
                      op  NUMBER PATH '@op',
                      dis NUMBER PATH '@dis',
                      par NUMBER PATH '@par',
                      dep NUMBER PATH '@dep',
                      skp NUMBER PATH '@skp') as X
  Where hash_value = :sqlhv
  and   child_number = :cn
  and   other_xml IS NOT NULL)
SELECT
        NVL(m.dis, sp.id) as id,
        NVL(m.par, sp.parent_id) as parent_id,
        NVL(m.dep, sp.depth) as depth,
        decode(m.skp, 1, 'Y', null) as inactive,
        sp.*
FROM v$sql_plan sp,
     display_map m
Where m.op (+) = sp.id
and   sp.hash_value = :sqlhv
and   sp.child_number = :cn
AND NVL(m.skp,0) <> 1
ORDER BY sp.id;
:sqlhv(VARCHAR[8],IN)='24666573' 
:cn(INTEGER,IN)=0 
-- Rows fetched: 22

----------------------------------
select * from table(dbms_xplan.display_cursor(sql_id => 'd1hq5h40rhsfd', cursor_child_no => null, format => 'ALL, ALLSTATS, LAST, ROWS, BYTES, COST, OUTLINE, PARTITION, PARALLEL, PEEKED_BINDS, PREDICATE, PROJECTION, ALIAS, REMOTE, NOTE'));
-- Rows fetched: 389

----------------------------------
select * from table(dbms_xplan.display_cursor(sql_id => 'd1hq5h40rhsfd', cursor_child_no => 0, format => 'ALL, ALLSTATS, LAST, ROWS, BYTES, COST, OUTLINE, PARTITION, PARALLEL, PEEKED_BINDS, PREDICATE, PROJECTION, ALIAS, REMOTE, NOTE'));
-- Rows fetched: 194

Switching the "Display Mode" back to "Tree" does not execute anything but shows the same different execution plan (now as tree).

5

I am guessing Explain Plan vs DBMS_XPLAN got different Child Numbers when you had a difference

6

After reviewing the executed statements, it looks to me like this is an Oracle Server issue.

In one case, explain plan and SELECT * FROM PLAN_TABLE are used, while in the other case, the actual execution plan is displayed using dbms_xplan.display_cursor.

It seems as if explain plan is providing an incorrect execution plan, because I found another SQL statement where, based on the execution time, I can definitively say that the plan provided by explain plan is not being used, but rather the one displayed by dbms_xplan.display_cursor.

7

This option may help. If checked, and the SQL has been recently executed, then Toad will display the plan from Oracle's cache rather than running a fresh explain plan. It applies to both the tree view and dbms_xplan.

image
image408×571 13.9 KB

8

What exactly does “Load cached plan” mean?
What permissions are required for this to work?

9

The only privilege needed is SELECT on V$SQL_PLAN (or GV$SQL_PLAN if on RAC)

It means, if you recently ran the SQL, and we can find the execution plan that Oracle actually used to run it in V$SQL_PLAN, then we will display that rather than ask Oracle to do a fresh explain plan and give the result from that.