Slow sql processing in Oracle Prod (vs Test)
- 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