Prev: We are here for all your events!
Next: reThink Migration – It’s Time for Change!
From: Helma on 2 Jun 2010 08:49
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
From: John Hurley on 2 Jun 2010 09:13
helma;

> 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?

Well it is a divide and conquer strategy type of plan that probably
will work best. Not quite sure what you mean by "mass-review".

I would probably start by looking at the top 5 or 10 tables and/or
indexes ( by gets and/or by reads ) and seeing what falls out of the
trees. If you are licensed for AWR stuff the reporting makes it
pretty obvious where you will get the best bang for your buck.

Finding some examples and case studies where you document some
improvements ... how and why ... what gain it gives to your business
by fixing the design ... is one way to go.

It can get exhausting mentally if you are in this battle by yourself.
It can get to be fun if you can get some buyin from the developers and
as their knowledge base increases if they start to suggest changes
themselves.

Ultimately there is no one size way of efficiently reviewing
everything nor is it going to be cost effective.
From: Mark D Powell on 2 Jun 2010 16:34
On Jun 2, 8:49 am, Helma <helma.vi...(a)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 --


From: phil_herring on 2 Jun 2010 18:31
I find that looking at tables and indexes on their own doesn't help
much; really what you want to look at is the SQL running against the
DB. One simple approach involves looking at the SGA stats for the DB
after it's been up for a while (at least a few weeks), and identifying
the SQL that has the highest total elapsed time. Pick off the top 5,
work on those, put the changes in, clear the stats, and repeat.
However, you have to be sensible; some SQL just takes a long time to
run and may not need tuning. Batch jobs often fall into this category.

If you're lazy, TOAD has a built-in report that will help you do
exactly this.

-- Phil
From: galen_boyer on 2 Jun 2010 20:23
Helma <helma.vinke(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.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
 |  Next  |  Last
Pages: 1 2
Prev: We are here for all your events!
Next: reThink Migration – It’s Time for Change!