Re: Query CRAWLS!!!

You don't really give us enough to go on, but that time isn't necessarily too unreasonable.

Assume index nested loops -

    4 logical I/Os to get a lookup_station_location     2 logical I/Os to get a lookup_division_station 16M rows x 6 -> ca. 100,000,000 logical I/Os before sorting.

Say 40,000 logical I/Os per second as a ballpark for a single 400 Mhz CPU (you don't say you are using parallel query).

    100,000,000 / 40,000 = 2,500

So a simple access path would take in the order of 40 minutes and you are getting the result in 15 minutes.

Oracle is probably doing a couple of hash joins and ignoring your indexes to do this, but

1. we need to see the exection path
2. have you analyzed the tables to generate optimal stats
3. are you running PQO

Don't worry about the swap, it is probably completely irrelevant and misleading - its a feature of how swap is reported and does not mean that memory is over-crowded.
--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Asif Khan wrote in message <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.
Received on Wed Jul 14 1999 - 11:49:30 CDT