Same SQL but different explanation plan

Question

I run the same SQL (below) in 2 different environments with the same set of indexes and table size. But they gave me two different plan of explanations ( attached )

• uses Merge Join Cartesian - very slow
• uses the PX / PX Send / PX RECEIVE coordinator - very fast

Query:

SELECT *
FROM SIEBEL.S_PARTY PRTY, SIEBEL.S_CONTACT CONT, HPQ_IF_ENTERPRISE_DIRECTORY ED,SIEBEL.S_BU BU 
WHERE PRTY.ROW_ID = CONT.PAR_ROW_ID 
AND BU.ROW_ID(+)=CONT.BU_ID 
AND CONT.EMP_NUM IS NOT NULL 
AND ED.HPSTATUS NOT IN ('Terminated', 'Retired', 'Deceased') 
AND ED.EMPLOYEENUMBER = UPPER (LPAD (CONT.EMP_NUM, 8, '0')) 
AND (SUBSTR(ED.MODIFYTIMESTAMP,1,14) >= '19800101' OR ED.MODIFYTIMESTAMP IS NULL)

Any idea what possible things might make that difference? And what does supporting explanation mean (things PX)?

Please note that I am not looking for changes to the SQL query (freezing during production).

Many thanks.

Answer 1

PX . - ( ).

Answer 2

, , , . , , .

, , , (, ) , .

Answer 3

? :

select table_name, degree from all_tables where table_name in ('S_PARTY', 'S_CONTACT', 'HPQ_IF_ENTERPRISE_DIRECTORY','S_BU');