Re: Query CRAWLS!!!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 14 Jul 1999 20:21:43 +0100
Message-ID: <931980667.1437.1.nnrp-02.9e984b29@news.demon.co.uk>

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 Received on Wed Jul 14 1999 - 14:21:43 CDT