Re: Query CRAWLS!!!

From: Asif Khan <dauji_at_my-deja.com>
Date: Wed, 14 Jul 1999 18:38:07 GMT
Message-ID: <7milee$odk$1@nnrp1.deja.com>

Thanks for responding!

> a) we need to see the exection path

---

SELECT STATEMENT
 SORT (GROUP BY)
    SORT (GROUP BY)

       SORT (GROUP BY)
	 HASH JOIN
	 	INDEX (FAST FULL SCAN)--XPKLOOKUP_DIVISION_STATIONNAME
(NON-UNIQUE)
		MERGE JOIN
		  SORT (JOIN)
			INDEX (FAST FULL SCAN)--XPKRENTAL_AGREEMENTCUST
(NON-UNIQUE)
		  SORT (JOIN)
			INDEX (FAST FULL

SCAN)--XPKLOOKUP_STATION_LOCATIONDIV (NON-UNIQUE)

---

> b) have you analyzed the tables to generate optimal stats
===
I haven't done this myself, but the DBA I'm working with has. ===
> c) are you running PQO

===
Yes.
===

In article <931971371.8977.0.nnrp-12.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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
>
> a) we need to see the exection path
> b) have you analyzed the tables to generate optimal stats
> c) 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
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jul 14 1999 - 13:38:07 CDT