Re: SELECT statement efficiency question

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Mon, 09 Apr 2007 15:13:17 +1000
Message-ID: <4619cb6d$0$30648$c30e37c6@pit-reader.telstra.net>

Mladen Gogala @ 9/04/2007 3:17 AM:
> 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
>

Hi All,

I was just cruising by to see what might be being said about oracle-l when I noticed the post above. I see that Charles has already shown that there is indeed a very significant impact on I/O, because the foreground processes doing the inserts and deletes need to read extra index blocks into the buffer cache prior to each change. This may not involve real physical I/O, but it does require latching if nothing else.

What has not yet been said is that in OLTP environments over-indexing also limits concurrency in redo generation and in the application of changes to the data blocks. Because the redo records have more change vectors, and more buffer locks and thus more latches need to be taken for change application, the redo latches and buffer locks are held for longer than they would otherwise be. The increased latching and locking and the increased latch and lock retention in both the log buffer and the buffer cache reduce scalability.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all

Received on Mon Apr 09 2007 - 00:13:17 CDT