Re: Oracle performance

Andrew,

thanks for your quick response. I analyzed my statement and found the mistake.
The problem was that " (select .... from t1 ....where ..)" should read "select ....from t1,t2...where..."). I do not know much about Oracles internals, but it seems that the missing table t2 in the 'from' clause causes a lot of overhead. Much more than on MSSQL or Access. Now it takes less than 1 sec. to produce the result.

Thanks,
Thomas Waenke

"Andrew Mobbs" <andrewm_at_chiark.greenend.org.uk> wrote in message news:e6f*xTKUo_at_news.chiark.greenend.org.uk...
> Thomas Waenke <twaenke_at_maps-geosystems.com> wrote:
> >
> >select * from t1 where id in
> >(SELECT t2.id From t2 where t2.id = t1.id group by mgk_struct.pid)
> >
> >This query runs 2 seconds on MSSQL7 and MSAccess but 2 minutes on Oracle!
> >Where is the problem?
>
> Could be anywhere. First guesses, are there indexes on t2.id and
> t1.id? Are your Oracle tables analyzed?
>
> >Is there a general and easy way to check Oracles performance?
>
> For single SQL queries, use "set autotrace on" in SQL*Plus then run
> your query, this will give you the execution plan, and some statistics
> about the query.
>
> Beyond that, for larger sets of queries, look at using SQL tracing and
> "tkprof".
>
> For server tuning, look at statspack (or utlestat in version<8.1.6).
>
> --
> Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Received on Fri Apr 27 2001 - 05:07:21 CDT