Search code examples
sqloracle-databaseperformancesetting

same SQL but different explain plan

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.

Solution

  • 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).