Re: Performance degradation

zabutimaxim_at_gmail.com wrote:
> 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.

Are you are constructing and issuing unique SQL statements like you show:

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

Or are you binding like:

select field1, field2 from mytable where upper(field3)= :x;

And setting x to 'MYVALUE','YOURVALUE','OTHERVALUE'

If you do the first you are likely overparseing, consuming too much CPU and fragmenting the shared pool. That will result in the slowdown you see.

To fix you should change your code to issue bound SQL, as a short term option you could consider setting cursor_sharing to force for the session, but bear in mind this can cause unpleasant side affects including changing query plans. As can bind variables in general, so test, but at least the application will run.

-- 
MJB