same SQL but different explain plan

Question

I am running same SQL (below), at 2 different environments with same index set and table size. But they gave me 2 different explain plans (attached)

1. uses a Merge Join Cartesian -- very slow
2. uses PX Coordinator / PX Send / PX RECEIVE -- 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 is the possible things to cause this difference? And what does 2nd explain plan with (PX things) mean?

Note that I am not looking for changing the SQL query (freeze in production).

Thanks a lot.

Answer 1

PX indicates parallel processing. That may not be available on the other database due to session settings (or if the other database is a different edition or version).

Answer 2

The plan for a query is not just dependant on the table size or indexes, but also on many other factors, mainly the statistics for the table, its columns, and its indexes. These statistics include such things as the clustering factor, which can make a big difference to the calculated cost.

In addition, different system statistics, optimizer parameters, table structure (e.g. partitioned vs. non-partitioned), and database block size, all come into play and the slightest difference between environments can cause a different plan to be favoured.