Re: Please Help: Sudden Drop in SQL Performance

One thing to check is whether the database has grown so that sorts which used to be done in memory now require the use of hard disk space. That could slow things down considerably.

Van

BillBerditzman <BillBerditzman_at_yahoo.com> wrote in message news:3877F91E.2125204C_at_yahoo.com...
> 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 Sun Jan 09 2000 - 17:40:54 CST