Re: Query CRAWLS!!!

Check the oracle init parameters, on your box you should have a large SGA buffer
pool, at least 128mb, also check sort_area_size at least 4-8mb (or larger).

Also run analyze on the three tables, and use the SQL hint select /*+ ALL_ROWS * ..... good luck,

dpm.

Asif Khan <dauji_at_my-deja.com> wrote in message news:7mick8$k7s$1_at_nnrp1.deja.com...
> We have an Oracle 8i server running on a Solaris Sparc E-4500 box(8
> 400mhz processors, 4 gig RAM, 12x36 gig HD).
>
> A simple query with a couple of joins like the one below takes 15
> minutes to run!!
>
> Another interesting thing is that the box is eating into 400 megs of
> SWAP space. It already has 4 gigs of RAM, and I see no reason why it
> should need SWAP.
> There are only 2 people using the server at the moment.
>
> Anyone have any suggestions???
>
> Here's the query:
> =======================================================
> select c.division_name, count(distinct a.customer_id)
> from rental_agreement a,
> lookup_station_location b,
> lookup_division_station c
> where b.division_number = c.division_number
> and a.checkout_station_code = b.station_number
> group by c.division_name
>
> ========================================================
> More info:
> a)rental_agreement has 16 million records.
> b)lookup_station_location has 10,000 records.
> c)lookup_division_location has 10 records.
>
> There are indexes on:
> a) lookup_station_location.division_number
> b) lookup_division_location.division_number
> c) rental_agreement.checkout_station_code
> d) lookup_station_location.station_number
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Jul 14 1999 - 20:17:17 CDT