Re: SELECT statement efficiency question

On Mon, 09 Apr 2007 09:40:57 -0700, hpuxrac wrote:

> Mladen what you wrote just doesn't make sense. Any time you add indexes
> to any table you affect to some degree the scalability of an
> application. Tom Kyte's test harness is an easy way to look at the
> impact of adding indexes. A 10046 trace is another way.

The 10046 is not a good way as it doesn't show writing to indexes. The only writes done by the user process are direct writes. In other words, you don't see much of an impact from the 10046 trace. The only measure you can use to measure the overall impact of an index is timing, precisely what my test did. That is what "set timing on" was all about. Look at the Charles's trace: you don't see anything like "single block writes", indicative of an index maintenance. The only thing you see is an increased wait for the log sync, but you cannot quantify that.

Furthermore, my statement said that I've never seen an unacceptable impact. Mostly, for the OLTP transactions, indexes will extend the transaction duration from 300 milisec to 500 milisec or 1/2 of a second. Most users will not complain about 1/5 of a second. The only cases when this impact becomes significant is when we start working with large batch transactions, usually scheduled not to run during the peak usage. If a load runs from 1-3 AM instead of 1-2 AM is a big difference performancewise,  but I will not give it a second thought because it doesn't have any business impact.

-- 
http://www.mladen-gogala.com