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: Performance issue with Oracle 8i : order by

Re: Performance issue with Oracle 8i : order by

From: PPN <ppn_at_mederic.fr>
Date: 26 Mar 2003 02:53:26 -0800
Message-ID: <6f6a7f59.0303260253.71487a31@posting.google.com>


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

Original text of this message

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