Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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