Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query runs slower under Oracle8i

Re: Query runs slower under Oracle8i

From: <jdarrah_co_at_my-deja.com>
Date: Tue, 07 Nov 2000 18:00:56 GMT
Message-ID: <8u9g0h$pob$1@nnrp1.deja.com>

Have you done an explain plan to see if this query has the same plan on both database versions?
In article <8u7j12$sj12_at_imsp212.netvigator.com>,   "O.K.Man" <okman_at_netvigator.com> wrote:
> Dear all,
>
> I have a query in the format like this :
>
> SELECT TAB1.COL1
> ,TAB1.COL2
> ,SUM(TMP.COL4)
> FROM TAB1
> ,(SELECT TAB2.COL1
> ,TAB3.COL2
> ,TAB3.COL3
> ,SUM(TAB3.COL4) COL4
> FROM TAB2
> ,TAB3
> WHERE TAB2.COL2 = TAB3.COL1
> AND TAB2.COL3 = TAB3.COL5
> GROUP BY
> TAB2.COL1
> ,TAB3.COL2
> ,TAB3.COL3
> ) TMP
> WHERE TAB1.COL3 = TMP.COL1
> AND TAB1.COL4 = TMP.COL2
> GROUP BY
> TAB1.COL1
> ,TAB1.COL2
>
> which run fine under Oracle7 (7.3.4). However, under Oracle8i
 (8.1.6), I
> found that the query generated a lot of file i/o on the temporary
 tablespace
> (there is no file i/o on the temporary tablespace under Oracle7
 during the
> execution of the same query) and caused the query run much slower
 than under
> Oracle7. Is there any default setting for Oracle8i would cause the
 optimizer
> to write the temporary result to disk ?
>
> Thanks in advance,
>
> O.K.Man
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 07 2000 - 12:00:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US