Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issue with Oracle 8i : order by
Hans Forbrich <forbrich_at_telusplanet.net> wrote in message news:<3E80BAE5.BF6F76C5_at_telusplanet.net>...
> A few possibilities come to mind including network traffic & cache. If
> you ask the question in remote TOAD (or remote SQL*Plus) and then ask it
> from the SQL*Plus locally, you might find the 'question' and possibly
> the 'answer' is still in memory. Have you tried asking twice from TOAD
> in quick succession? Is there a difference in timing?
>
> As an aside, did you really mean to form the Select like that? Is there
> a reason for 1) retrieving all info, 2) ordering and 3) passing the
> result set to 'count(*)'? (Not criticizing, just curious)
> /H
Using TNSPING, it is fine ; running the request twice in TOAD give the
same performance problems.
The real problem is that when I don't specify any "ORDER BY", answer
time is rather good, whereas the "ORDER BY" makes the difference
between TOAD (and client-side SQLPLUS) and server-side SQLPLUS. It is
even more blatant when I add extra "WHERE" conditions : on the server
side, the answer comes instantly, despite the fact I'm adding a "LIKE"
condition, whereas the client side takes a little more time than
before (jumping from 9 seconds to 13).
So I'd like to know how come Oracle behaves differently depending on
whether I query it from a client or directly on the server.
To answer your question, I meant this SELECT because without an inner select, Oracle optimizes the query and instantly returns the number of lines that are in the table, whereas it is fooled by the inner select. I used the coun(*) so that I wouldn't get bothered by 25.000 lines scrolling in my telnet session :-)
Thanks for any answers.
P.
Received on Wed Mar 26 2003 - 04:53:26 CST