Re: Help:Index performance issue

<u518615722_at_spawnkill.ip-mobilphone.net> wrote

> We have a table with 13 indexes built
> on it.

Does not sound good to me. Whenever I hear someone using that many indexes on a single type, my kneejerk reaction is that they are clueless about performance tuning and are using the Large Hammer Approach.

> When we run explain plan for it, there is
> no full table scan, which means the sql
> does not have any problem, right?

No. Full table scans can be faster at times. Much faster (or why do you think the CBO at times want to use full table scans as oppose to an index range scan?).

> Where else could we do to improve the
> performance?

Before improving performance, you need to understand what you are trying to improve!

Do not mean to sound harsh, but I am looking after an OLTP production database where the largest table, has 4 indexes, and where those 4 indexes are THREE TIMES!!! the size of this table.

What really gets me is that these 4 indexes are essentially redundant. They are indexing the SAME columns in different sequences, with the odd new colum thrown in for good meassure. So I sit with space and performance problems, simply because the developers were clueless. Did they EVER stopped to think about the overheads on OLTP when every single transaction on the master table means keeping 4 massive indexes (3 times the size of the table) up to date?

Indexes are not the magic wand that can be waved to solve performance problems. If you are serious about improving performance, review the database design and SQLs with a real Oracle DBA. Alternatively, curl up with the Oracle Concepts and Performance Manuals and a jug of hot coffee and read up on it. Then get your hands dirty and looking at sys tables such V$SESSION_WAIT, V$SESSION_EVENT.

--
Billy