Oracle JOIN operation performance changes when swapping SELECT statements

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:

?

Not a direct answer, but I would try rewriting the query to avoid the need to always execute both parts:

select 1from   dualwhere exists (        SELECT 1         FROM USER_OBJECTS         WHERE OBJECT_NAME = UPPER('Client_Sys.Clear_Info')         AND OBJECT_TYPE = 'PROCEDURE') or      exists (        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 )); 

Oracle 11G:

In my opinion or what I have see so far, UNION or VIEW statements process more rows but with negligible overhead. Also just comparing the cost of the plan will not give you the best plan even though they are two different queries fired on the same time, same DB, and same settings etcIt is just an indicating factor and induces the observor effect.

When you run those two queries, did you find any big change in execution time? I found NOTHING.

But if you ask why there is a difference in the cost while swapping, here is my two cents.

On plan 1, Cost was 161, CPU cost was 74M, IO Cost was 151On plan 2, Cost was 161, CPU cost was 23M, IO Cost was 6

I found no difference in processing the selects while swapping, but the UNION is the point where the plan differs. As I said above, the cost difference is due to additional row processing on UNION and VIEW.