Re: I/O performance problem

On Thu, 30 Apr 1998 10:28:47 +0200, Giuseppe De Donno <dedonno_at_ariadne.it> wrote:

>
>
>Dave O'Keeffe wrote:
>
>> Giuseppe,
>>
>> I think your problem is your doing 43285 reads and 24,000 odd disk
>> accesses in order to return 6 rows. This is about 4,000 disk accesses
>> per row returned! The SQL statement may be a little inefficient.
>>
>> If you post the SQL some of our tuning stars can probably help you.
>
>Ok, my query is:
>
> select distinct a.serditta, upper(a.codprovincia),
> upper(a.nummeccanog), upper(a.denominazione),
> upper(a.capoperativo), upper(a.comuneoperativo),
> upper(a.provinciaoperativo), upper(a.statoazienda),
> upper(a.flagvalidazione), upper(a.flagcesdir),
> upper(a.flagcessazione)
> from ditte a ,prodotti b
> where upper(a.classefatturato) like 'C' and
> upper(a.classeaddetti) like 'B' and
> ( upper(a.provinciaoperativo) like 'MI')
> and b.serditta = a.serditta and
> ( upper(b.desprodotto) like '%CARNE%' ) and
> b.impexp like 'I'
>UNION
>select distinct aa.serditta, upper(aa.codprovincia),
> upper(aa.nummeccanog), upper(aa.denominazione),
> upper(aa.capoperativo), upper(aa.comuneoperativo),
> upper(aa.provinciaoperativo), upper(aa.statoazienda),
> upper(aa.flagvalidazione), upper(aa.flagcesdir),
> upper(aa.flagcessazione)
> from dittemi aa ,prodotti bb
> where upper(aa.classefatturato) like 'C' and
> upper(aa.classeaddetti) like 'B' and
> ( upper(aa.provinciaoperativo) like 'MI') and
> bb.serditta = aa.serditta and
> ( upper(bb.desprodotto) like '%CARNE%' ) and
> bb.impexp like 'I'
>order by 4
>
>The indexes on column in the where condition are present .
>
>Thanks
>Giuseppe
>

This makes clear that Oracle does full tablescans for all 4 tables to execute this query. Oracle can not use an index if there is a function on the column in the where clause so those upper(classe...) functions in the where clause, not the select clause, are the culprits here.

On a further note it seems these classe... )and possibly also the provincia...) columns are classifications, it would be a matter of good database design to make them all either uppercase or lowercase. It would certainly be worth it to find out which columns _can_ be all one case and then make them so. In the long run you would nearly always win more on retrieval performance than the extra effort will have cost you. If your database does not yet use pre-insert triggers you don't even have to adapt the inserting applications. Simply create a preinsert trigger for each row that does : :new.classe... := upper(:new.classe...); (or lower, just what you prefer) for every column that can be only one case.

Install the trigger, do a one time update of all the records already inserted and remove the upper() functions from your where clauses and Oracle will certainly repay you with a much higher performance.

I am not sure about this but there might also be something with those like comparisons. If you dont use % or _, the wildcard characters, in the string they work identical to the equal operator, so why don't you just use that? The only place where you need the like operator is in the lines with '%CARNE%'.

I hope this helps

Henk de Wilde. Received on Thu Apr 30 1998 - 08:00:41 CDT