Re: Index strategy
Date: Wed, 2 Jun 2010 13:34:30 -0700 (PDT)
Message-ID: <94a32db3-8a76-4916-8aba-6550c841c6fc_at_s41g2000vba.googlegroups.com>
On Jun 2, 8:49 am, Helma <helma.vi..._at_hotmail.com> wrote:
> I now work at a shop where the developers over the years have added
> many (random) indexes on the database, and (still) having problems
> with performance. I see many sql-statements coming by that use
> suboptimal indexes, I think it would be a good idea to mass-review the
> sql and indexes, increasing the quality of the indexes and reduce the
> 'overindexing' of the DB. Seniors! I assume you have encountered this
> situation before. How to efficiently deal with such situation?
>
> Oracle 10g
Rather than mass review indexes I think I would use Statspack or AWR (if Licensed and 10g+) reports to find heavy hitter SQL and/or trace critical processes and attach any performance issues I found in those.
Monitoring is on by default in 10g so I would look for indexes that do not appear to be used and potentially drop those to free up space and eliminate the need to maintain them.
The idea of performing a mass reivew seems like it would be largely a waste of DBA time; however, I am not totally against the idea as it may be the only way to find and eliminate garbage but first I would want to attack real problems. To do that you have to identify specific problems and Oracle provides tools to do just that.
HTH -- Mark D Powell -- Received on Wed Jun 02 2010 - 15:34:30 CDT