Re: Performance degradation

zabutimaxim_at_gmail.com wrote:
> Holger,
>
> What do you want to know about my application? It is known application
> (I just can't say here it's name). It has 600MB of sources? So it is
> pretty difficult for me to explain here what does it does:-) And I
> don't think it will help.
> The information that I provided here is very simple and should be
> enough I think, you have 2 inputs:
> 1. select field1, field2 from mytable where upper(field3)='MYVALUE';
> on start it takes 2-3 seconds and after 2-3 hours it takes 2-3
> minutes.
> 2. after building function based index like following:
> create index my_idx on mytable(upper(field3));
> it takes 2-3 seconds on start and also after 2,3,5,10...hours.
> No more degradations.
>
> Again I don't looking for solution for my problem, I already solved it.
>
> All I'm asking is why full table scan on large table can cause
> performance degradation in Oracle.
> This is very general question and I don't think it connected to
> specifically my application.
>
> Thanks,
> Maxim.
>

Sorry, I was not clear. From your previous posts it was not clear, that your webapplication which you are benchmarking, does solely selects.

And somehow, I don't think that it does. Why do you think, that the full table scan degrades the performance, instead of that the performance of the full table scan was degraded due to some other reason? The index plan might require a magnitude less IO to perform, that *might* be the reason why you don't see the degradation.

The baseline of my assumption (or guess work) boils down to: the full table scan encounters a degraded performance because either a) the time to perform the same number of LIO/PIO got higher (more

    waits) or
b) the amount of work to perform the full scan increased (e.g.

    to recreate the table blocks as of the time the select started,     more information from the rollback segments was needed which     could have led to more physical IO)
c) your buffercache is way to large and gets, when filled, swapped to disk

In other words, I believe you're asking the wrong question and not telling the whole story.

Cheers,

Holger Received on Thu Apr 14 2005 - 09:10:56 CDT