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.

Zigzagger

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

Bowerbird

Thanks Gary, you're right. I found the issue is caused by the "indexes" having degree as DEFAULT. That I guess it would take the degree value from DB Session level that could be different. After forcing the degree at index level to be 4, I was able to resolve it. Thanks. – Zigzagger

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.

Clover

Answer 3

Do the tables have a different DEGREE? Check this query in both environments:

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

Theophilus

Thanks Jonearles, appreciate your near to correct answer - in my situation. I am puzzled which answers I should accept, since all of them are right :) – Zigzagger

That's ok iwan, just upvote the answers that were helpful, mark as "right" the answer that was most helpful, and we'll all be happy :) – Clover