• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
We are concerned about a serious difference in sql processing between test and prod environments.

For example, the query below takes about 6 seconds in test, and 150 seconds in prod (30 times longer), for the same result. About the same on other days.

SELECT ABC01.Z13.Z13_REC_KEY||'ABC01' FROM ABC01.Z13,ABC60.Z13,ABC60.Z103
WHERE substr(Z103_REC_KEY,6,9)=ABC60.Z13.Z13_REC_KEY
AND substr(Z103_REC_KEY_1,6,9)=ABC01.Z13.Z13_REC_KEY
AND substr(Z103_REC_KEY_1,1,5)='ABC01'
AND ABC60.Z13.Z13_OPEN_DATE = '20120220';

What could cause the difference? Do you have ideas of what could be done to optimize oracle on production ?

Resolution:
Following the principles described in KB 16384-25265, I executed the following query on Production:

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ABC01.Z13.Z13_REC_KEY||'ABC01' FROM ABC01.Z13,ABC60.Z13,ABC60.Z103
WHERE substr(Z103_REC_KEY,6,9)=ABC60.Z13.Z13_REC_KEY
AND substr(Z103_REC_KEY_1,6,9)=ABC01.Z13.Z13_REC_KEY
AND substr(Z103_REC_KEY_1,1,5)='ABC01'
AND ABC60.Z13.Z13_OPEN_DATE = '20120220';

It took about 10 seconds.

We suggest that you add the DYNAMIC SAMPLING hint to all of *your own* SQL queries -- especially those involving the z103.

• Article last edited: 10/8/2013