Please Help: Sudden Drop in SQL Performance

Hello,

I'm at my wit's end. SQL statements in our Production database that used to take seconds can now run 15 minutes The same statement will execute in seconds in our Test database which resides on a different server. This is a PeopleSoft database on Oracle 7.3.3.5. An example of a poorly performing SQL statement is:

SELECT DISTINCT EMPLID, EMPL_RCD#, NAME, SSN FROM PS_FSA_HC_SRCH WHERE EMPLID LIKE '12900%' AND OPRCLASS='ALLPANLS' ORDER BY EMPLID; If I remove the 'distinct', the statement will execute fine. However, that isn't an option because this is a delivered statement. Our databases use Rule based optimization. As far as we know, nothing has changed as far as UNIX or Oracle parameters. The only difference between Production and Test is that they reside on different servers. Again, in Test, these performance problems do not exist.

Does anyone have any idea of what could possibly have changed or would cause such a sudden change in performance?

Thanks for any suggestions. Received on Sat Jan 08 2000 - 20:57:34 CST