Prev: We are here for all your events!
Next: reThink Migration – It’s Time for Change!
From: joel garry on 3 Jun 2010 13:06
On Jun 2, 5:23 pm, galen_bo...(a)yahoo.com wrote:
> Helma <helma.vi...(a)hotmail.com> writes:
> > 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?
>
> At the end of the day, what is "over-indexing" a database?  If you don't
> see a slowdown because of the indexes, it sounds like you should be fine
> there.  So what if an index isn't used or one index would solve what 5
> indexes are currently solving.  If something is hitting you because of
> too many indexes, then indentify those indexes and fix that problem.
> Other than that, start putting good practices going forward and keep a
> look out for problems caused by over-indexing.
>

I may be wrong, but from the tone of the OP I gather they are pretty
close to this situation already, and asking for the next step.

I don't think there is any efficient way to accomplish it, and I'm not
sure that just using the type of business justification an external
consultant would use - "is there anything slowing down currently
because of this?" - is the correct response. I'd advise getting Tom's
Effective Oracle By Design book and comparing past and current
programming practices to that. I think with the internal knowledge of
a specific environment one could come up with a "top 10 past goofs we
oughta fix" list. Slow-by-slow processing, leftover RBO assumptions
(this could lead to over-indexing), there may be other low-hanging
design fruit the developers suspect once they understand the issues,
possibly even point out with some pattern matching on the code.

This is fixing accumulated deferred maintenance, so cost justification
is difficult. It takes some good strategic planning to not hit a
sudden-needed-upgrade wall. Sometimes, fixing a lot of small problems
that currently aren't a big deal can avoid big problems in the
future. Sometimes, you really do run into the limits of a system and
ought to upgrade. Then you have to watch out for newer systems not
being as efficient as the old.

I would be more paranoid than Mark about dropping unused indices,
unless they've been tracked for a year, at least. Unless, say, you
get overtime for tracking performance problems on New Years Eve.

jg
--
@home.com is bogus.
Who designed your database? http://www.signonsandiego.com/news/2010/jun/02/u-t-editorial-failure-leadership/
From: John Hurley on 3 Jun 2010 15:08
Mark:

# 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.

I think this is ( perhaps fairly widely ) mis understood. Monitoring
of statistics for tables and indexes is on by default afaik. The
usage monitoring needs to be turned on and off explicitly I believe.

From: Mariano Corral on 12 Jun 2010 14:20
On 3 jun, ,joel garry wrote:
[snip]
> I would be more paranoid than Mark about dropping unused indices,
> unless they've been tracked for a year, at least.  Unless, say, you
> get overtime for tracking performance problems on New Years Eve.

There is always a risk when dropping an index, so you may consider to
balance advantages and risks. A unnecessary index impacts negatively
the performance of inserts and updates of the indexed columns. Then,
the benefits of dropping an index are higher for tables with many
inserts or many updates of its indexed columns. On the other hand,
read-only tables can afford many indices without a performance
penalty. In other words, you may assume higher risks when dropping
indices for tables having many inserts or updates. For others, drop an
index only when it's 100% guaranteed it'll be harmless.

Regards,
Mariano Corral
First  |  Prev  | 
Pages: 1 2
Prev: We are here for all your events!
Next: reThink Migration – It’s Time for Change!