Re: EXPLAIN PLAN

Try:

alter session set events '10053 trace name context forever , level 12'; before the explain plan, and you may see what Oracle goes through.

12 minutes seems a bit high for simple tables but:

1. Are there any UNION ALLs involved
2. Are there any bitmap indexes on the tables
3. Do you have histogram analysis in lots of columns

Check the memory being used by the session - I recently had one parse which demanded over 400Mb (and then crashed the box).

Bear in mind that 10 tables, each with 3 join methods (sort merge, hash join, or nested loop), and assuming 2 possible indexes each gives the optimiser something like:

    10! x 3 ^ 10 x 3 ^ 10
possible execution paths - and that is a very, very big number - even allowing for the optimiser cutting out huge fractions by optimistic analysis (a.k.a. guesswork).

--

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

Tanasescu Iosif wrote in message <36E96D2B.821A140D_at_bellsygma.com>...
>Could anybody tell me how an explain plan (eevn for a querry impliyng 10
>tables in outer join) could take 12 minutes to be executed, in an
>environment where no other processes are running, only one database, sga
>fo 250Mb (from 1.5 Gb machine), 2 CPU, HP-UX 10.20 and ORACLE 7.3.4 (no
>patches)
>
Received on Fri Mar 12 1999 - 16:36:58 CST