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.

It wasn't solely the table scan that was causing your problems, although that activity didn't help matters any (traversnig a table record by record IS time consuming, especially for large tables). The other part of your performance problem was the function call on every record to find relevant values. Of course, the function-based index dramatically immproved performance by pre-generating the upper() values for which you search (other benefits of an indexed column aside).

Let's compoare what WAS happening before the index to what happens AFTER the index. Before the index was created Oracle was:

1. Starting at the first block of the table, and the first record in that block, and 'walking' through the entire table, block by block, record by record, reading the relevant column in each.
2. For each record applying the upper() function.
3. Comparing this generated value to the upper-case constant value supplied.
4. Returning all matching rows, one row at a time, without benefit of using the ROWID.

After the index was created Oracle now does this:

1. Scan the relevant index for matching values.
2. Return the ROWID of the matching entries and directly access them in the table.

Quite a difference in methodology, and quite an increase in performance between scenario 1 (before the index) and scenario 2 (with the index). Direct access is almost always faster than a table scan except under conditions of a small table or a query returning a large portion of the table rows (if you're returning 80% of the table data an index scan probably won't be much better than a table scan, and could be slower). Obviously your condition is not returning a majority of the table data, thus your function-based index solved your problem by dramatically reducing the work required by Oracle to create your recordset.

I hope this answers your question.

David Fitzjarrell Received on Sat Apr 30 2005 - 09:13:15 CDT