Re: Please Help: Sudden Drop in SQL Performance

Bill,
We have seen similar issues occur. A couple of things to check:

1)Do all the indexes, primary keys and foreign keys match between test and production database?

2) Have you done any large data loads on production? When was the last time you rebuilt indexes in production? A simple rebuild might help:

    spool /tmp/rebuild.em
    SELECT 'alter index '||index_name||' rebuild ;'     FROM user_indexes;
    spool off
    @/tmp/rebuild.em

3) Check if any of your tables/indexes have statistics. This will cause weird performance - even in rule based....if they are there Oracle uses them!

4) If all this fails, run the statement in SQL*Plus with trace on, then run an explain plan so you can see where it is spending its' when running the statement.

5) You don't say if you have eliminated the network as an issue. If you run the statement in step 4 above and it runs in the fast time you may have network issues. Possible culprits could be NFS mounting of drives, duplicate network address, sqlnet tracing turned on.......

Hope that helps Let me know what you find. Jack Richter

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 - 15:26:38 CST