Re: SELECT statement efficiency question

On Apr 9, 11:10 am, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net> wrote:
> 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 performance-
> wise, but I will not give it a second thought because it doesn't have any
> business impact.
>

I've seen business impact because the batch needs to fit into a window. Interestingly enough, I only was looking at that because of a report that was running off that same table took too long for the online version. On investigation, it turned out the real problem was an in-memory table was being used not as documented, a consequence of that database-independent thing. But fixing that made me a hero and led to thousands of hours of other work. They hadn't even realized there was a fix for the business impact, and wouldn't have to known to ask about it, or who to ask (which is my major complaint about MethodR

• sometimes people just think [or are told] "that's just the way it is" or otherwise make invalid decisions about ordering business impact importance). Subsequent hardware improvements made it all moot, anyways. So you are kind of wrong and kind of right, since on a saturated system something that adds a small effect (such as Jonathan posted about scattered index inserts) can rapidly deteriorate performance.

Did make the idiots who couldn't read doco mad at me, though.

jg

--
@home.com is bogus.
I want my flying car!  http://news.com.com/2300-11389_3-6173182-2.html