CODEWITHSUNDEEP
sqloracle-databasejoinsql-execution-plan
pragan
pragan

Reputation: 143

Oracle JOIN operation performance changes when swapping SELECT statements

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,

enter image description here

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,

enter image description here

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!

Upvotes: 4

Views: 799

Answers (3)

Srini V
Srini V

Reputation: 11375

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 etc It 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 151
On 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.

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36902

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:

?

Upvotes: 5

David Aldridge
David Aldridge

Reputation: 52376

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

select 1
from   dual
where 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 ));

Upvotes: 2

Related Questions