Re: order of predicates affects CPU usage hence elapse time on sequent

Hi,

I don't know whether I got your question right. I think you are trying to run the same query against two databases running 7.3 and 8. You need to check : a) Is the block size of the databases same? b) you have identical storage clause for both the tables? c) You have identical indexes on both the dbs? d) You are using CBO on both ? e) Have you analyzed the tables, indexes ? f) Your init.ora parameters are almost identical on both dbs ? - check db_file_multiblock_read_count. g) Was the load on the m/c same when you carried out this test ? h) Did you observe contention for resource like file i/o, block buffers etc. i) Is it likely that on Oracle 8 the query was affected by delayed block cleanout. j) Is the h/w on which the two dbs are running identical ?

Anyway if you are using CBO, I don't think the order of the predicates will not make a difference. I distinctly remember carrying out some Oracle 8 specific init.ora parameters tuning while carrying out beta testing of Oracle 8 (testing the performance of same application on oracle 7.3 and oracle 8). I think the optimizers of the two versions behave differently and may be you'll also need to look at ora 8 init.ora parameters.

I hope this helps.
Ajit

In article <01bd2eef$1869f2e0$1e21a4c2_at_evertonco>,   "Everton Elliott" <everton_at_aapi.co.uk> wrote:
>
> Hi,
> I have seen a unusual thing:-
>
> TABLE
> ======
> A CHAR INDEX
> B DATE INDEX
> C CHAR INDEX
> D CHAR INDEX
> E CHAR
>
> select count(*) from TABLE
> WHERE C='SSSS'
> AND B between '10-JAN-97' and '10-JAN-98'
>
> versus
>
> select count(*) from TABLE
> WHERE B between '10-JAN-97' and '10-JAN-98'
> AND C='SSSS'
>
> Under tkprof oracle 7.3 and under oracle 8.x I see that the IO are simular
> but
> the CPU usage diffrent by a multiple of four and hence the elapse time are
> different. Full table scan is used for both query.
>
> Why should swaping the 'AND' predicate cuase the CPU usage to change????
>
> Cheers

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet