Performance degradation

Hi,

I have a huge performance degradation on Oracle side of my Web application. I found that problem come from select statement from table with above 1.5 millions records with "where condition" that contains function "upper", like following:

select field1, field2 from mytable where upper(field3)='MYVALUE';

I solved this problem by adding function based index for field3, but I still don't understand why this cause to degradation? I mean why right after restart of Oracle server this select runs for 5-10 seconds, but after 2-3 hours the same select statement runs for 2-3 minutes?

I have Orcale 9.2.0.6.0 installed on Windows 2003 Server. 2 CPU 2.4 GHZ and 2GB RAM.

Thanks,
Maxim. Received on Wed Apr 13 2005 - 08:16:16 CDT