• TheSQLGuru (11/1/2007)


    2) Besides, many performance fixes are either adding/altering indexes, defragmentation of same, updating statistics or rewriting a query to removed unnecessary temp tables, cursors, etc. Only the latter really carries any significant risk, and see one for risk management.

    Adding/Altering indexes has significant risk.

    Adding an index WILL increase the time involved for inserts. It WILL impact the locks that need applied. It COULD increase time involved for updates (+ or -).

    Altering an index can also have a significant impact. For example: Say you change the order of fields in an index only to find out that Quarter-end reports that you have never seen run in your testing needed that index as written.

    Another hazard of alteringing an index especially if it is a clustered index. The order of results will change if an order by is not in the sql. This could be considered an application issue, but it will still be caused by your change.

    Updating statistics is a common thing, and is generally considered safe... However, there are situations when you DON'T update statistics. This of course depends on the system, but this is a BIG NO-NO in older versions of Oracle (8-9i) since they didn't do automatic statistics updates. In general, I will agree this is a safe operation, but don't be fooled into thinking this is not changing the system which can lead to unexpected results (bug).

    Don't misunderstand me. I agree that a WELL performing system is a goal, and that you should strive to never hear from the user/customer. That can be prevented with monitoring and such. Good instrumentation can be used to justify a performance only change. However in general, you will find that if you propose such a change to management unless you are talking about an insane gain in performance, the answer to changing the system will be "Not at this time".