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.

It is such a general question that is based on your specific application and configuration. There are many potential reasons why, because Oracle and its interelation with the platform is very complex.

Again, you are wanting to know the cause of a specific symptom, and we can't answer that from the information given. But you can with statspack, as several have suggested. See hotsos.com for more info (if they still have their free analyzer), and you can ask about specific things in your statspack here, and if you google for statspack or even read the docs you can get started. You've been given several speculations, things we have all seen at one time or another that cause your symptom. You don't have to guess, in fact most tuning experts would say you shouldn't guess. But we can't look at your system (I hope!).

jg

--
@home.com is bogus.  "You think I'm going there? You're crazy." -
George Bush
http://www.signonsandiego.com/news/nation/20050414-1510-bush-asne.html