My guess is that your sort_area_size in your init.ora is too small and you
are causing a sort to disk. Remember that is per connection so if you go
from 65K to 10M (I'm exaggerating) and you have 1,000 connections (at once)
then you go from 65,000,000 bytes to 10M * 1,000 in ram usage on the server
(potential, if someone doesn't do a sort then it won't allocate that memory)
Jim
"Stefan Busato" <stefan.busato_at_mitc.be> wrote in message
news:421cdd41.0204112322.39d2f424_at_posting.google.com...
> > > SELECT TABLE1.Col
> > > FROM TABLE1
> > > WHERE EXISTS (SELECT TABLE2.a
> > > FROM TABLE3, TABLE2
> > > WHERE TABLE3.b=TABLE2.b AND
> > > TABLE2.a=TABLE1.a AND
> > > TABLE3.c=Value)
> > >
> > > Table1 contains 16 rows.
> > > Table2 And Table3 contain over 100000 rows.
> > >
> Niall, Jim,
>
> Thanks for replying.
>
> My apologies, but it appeared that the problem was lying a bit further
> on:
> I am using the query above as a view on which I perform queries to
> populate a treeview. The query I used for the treeview looked like:
>
> SELECT DISTINCT VIEW.Col
> FROM VIEW
> ORDER BY VIEW.Col
>
> (no WHERE Clause)
>
> It seems that this last query was giving the problem. Once I omitted
> the (obsolete) DISTINCT everything worked fine.
>
> I'm really sorry to have kept you in the dark about this.
>
> Nevertheless, it seems really strange that, although my initial view
> only returns 12 rows, it still takes 45 seconds to perform the sorting
> (which is the reason why I omitted this next step in the first
> posting). Any idea why?
>
> Thanks,
>
> Stef
Received on Fri Apr 12 2002 - 09:21:55 CDT
