Re: Query CRAWLS!!!

From: Asif Khan <dauji_at_my-deja.com>
Date: Fri, 16 Jul 1999 03:13:35 GMT
Message-ID: <7mm80o$3bc$1@nnrp1.deja.com>

Someone from Oracle is coming in to take a look @ the box. I'll keep you posted on what she suggests.

In article <931980667.1437.1.nnrp-02.9e984b29_at_news.demon.co.uk>, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> One oddity, I can't think why there are 3 'sort group by'
> in the execution plan. The presence of 4 sorts of 16M
> rows is probably having some effect on your performance.
>
> Is this query actually running in parallel - your execution
> path shows no parallel execution, but this may be the
> result of the way you generated it rather than the way
> in which it executed. (The multiple appearance of
> SORT GROUP BY suggests that it might actually be
> an incompletely reported parallel execution).
>
> One difficulty with the query is that the lookup_division_location
> has very few rows, and if it goes through a parallel hash
> then the hashing algorithm may produce a very badly balanced
> load, and effectively remove the benefit of parallel execution.
>
> You might try:
>
> set all tables parallel
> Order the tables from smallest to largest in the FROM clause
> Insert the ORDERED hint
> Insert the USE_HASH() hint for all 3 tables
>
> This might make the two small tables hash together and
> redistribute evenly before hashing in the 3rd table.
>
> --
>
> 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 Thu Jul 15 1999 - 22:13:35 CDT