Oracle JOIN operation performance changes when swapping SELECT statements

Question

I came across the following behavior while studying Oracle query parsing and performance. I used following query for that,

SELECT 1 
FROM USER_PROCEDURES 
WHERE OBJECT_NAME = SUBSTR( UPPER('Client_Sys.Clear_Info' ), 1, INSTR( UPPER('Client_Sys.Clear_Info'), '.' ) - 1 ) 
AND PROCEDURE_NAME = SUBSTR( UPPER('Client_Sys.Clear_Info' ), INSTR( UPPER('Client_Sys.Clear_Info' ),'.' ) + 1 ) 
UNION 
SELECT 1 
FROM USER_OBJECTS 
WHERE OBJECT_NAME = UPPER('Client_Sys.Clear_Info') 
AND OBJECT_TYPE = 'PROCEDURE';

The explain plan for the above query is as follows,

Now I changed the query into the following format [ Swap the SELECT statements],

SELECT 1 
FROM USER_OBJECTS 
WHERE OBJECT_NAME = UPPER('Client_Sys.Clear_Info') 
AND OBJECT_TYPE = 'PROCEDURE'
UNION 
SELECT 1 
FROM USER_PROCEDURES 
WHERE OBJECT_NAME = SUBSTR( UPPER('Client_Sys.Clear_Info' ), 1, INSTR( UPPER('Client_Sys.Clear_Info'), '.' ) - 1 ) 
AND PROCEDURE_NAME = SUBSTR( UPPER('Client_Sys.Clear_Info' ), INSTR( UPPER('Client_Sys.Clear_Info' ),'.' ) + 1 ) ; 

Corresponding explain plan is as follows,

There is a significant different between the cost of operations. This could be a pretty basic thing but since I'm new to Oracle [or any DB stuff] this is a puzzle for me. Hope you could help me to figure this behavior.

Thanks in advance!

Answer 1

Oracle 12c:

The function NO_ROOT_SW_FOR_LOCAL in USER_PROCEDURES appears to be the cause of the difference.

In general, changing the order of things like predicates, tables in the FROM clause, or query blocks in a UNION statement has no meaningful effect on execution plans. With UNION, some of the subplans may be flipped around but the total cost will be the same.

For this specific case, first simplify the statements to these:

explain plan for select 1 from user_procedures union select 1 from dual;
select * from table(dbms_xplan.display);

explain plan for select 1 from dual union select 1 from user_procedures;
select * from table(dbms_xplan.display);

The two sub-execution plans are not merely switched, they are significantly changed and the totals are different. As with most data dictionary queries, the plans are large and the 266 lines of output are not displayed here.

The source code of USER_PROCEDURES contains the odd code ... from NO_ROOT_SW_FOR_LOCAL(INT$DBA_PROCEDURES) .... The plan difference goes away when NO_ROOT_SW_FOR_LOCAL is removed.

I have no idea what that function does and I cannot find any references it. Nothing in DBA_OBJECTS, DBA_SOURCE, support.oracle.com, or even Google. This is as far as we can dig without any serious hacking. If this cost difference is an issue then you will need to raise a service request with Oracle.

11g:

?