Re: SELECT statement efficiency question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 08 Apr 2007 20:15:42 +0200
Message-ID: <4619314E.4000008@gmail.com>

Mladen Gogala schrieb:
> On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote:
>

>> Good advice given so far.  I would caution against creating too many
>> indexes, as this will likely negatively impact performance of other
>> parts of the system

>
> Charles, this is the line I frequently find in many books, CBT and
> manuals and yet I have never seen insert or delete slowed down to the
> unacceptable levels because of too many indexes. The only method to
> diagnose that this is indeed happening would be to observe significant
> increase in average I/O time on the underlying data file.
> Again, I've never even seen this happening. I believe that this thing
> with too many indexes is dangerous only in the extreme situations and it
> is very hard to diagnose because the process that waits for writing the
> index blocks is DBWR so the users never wait for the blocks to be
> written. Users may wait for checkpoints or log file sync but not for the
> index
>

I think, the increased IO activity is negligible compared to increased latch contention in this case.
Here was an interesting blog entry about this http://esemrick.blogspot.com/2006/03/unused-indexes-and-scalability.html One can simply run this test to see the difference in 'cache buffers chains' latches:

create table d as select * from dba_objects where 1=2; create table d1 as select * from dba_objects where 1=2; spool d.sql
select 'CREATE INDEX D_'||COLUMN_ID||' ON D('||COLUMN_NAME||');' from dba_tab_columns where table_name = 'DBA_OBJECTS' spool off
@d
exec runstats_pkg.rs_start
insert into d1 select * from dba_objects; exec runstats_pkg.rs_middle
insert into d select * from dba_objects; exec runstats_pkg.rs_stop

To be consequent, one should run 10046 trace for the indexed case and compare time spent due to IO related wait events and latch related wait events...

Some years ago i've to do a big data load into OLTP Siebel system over weekend - some of very important tables (like s_customer) here often have about 40-50 indexes per table (they are just illdesigned so, to be generic) . The only possibility to get it done was to drop all indexes not required for load and recreate them after it. I suppose, many ERP systems don't differ much in this regard from Siebel.

Best regards

Maxim Received on Sun Apr 08 2007 - 13:15:42 CDT