Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query runs slower under Oracle8i
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