Query CRAWLS!!!

From: Asif Khan <dauji_at_my-deja.com>
Date: Wed, 14 Jul 1999 16:07:47 GMT
Message-ID: <7mick8$k7s$1@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 - 11:07:47 CDT