Re: Cost-based optimizer parser

The problem is inherent in cost based optimisation, although 5 tables is a little low for serious timing problems to occur.

Consider a 10 table query, where each table has two indexes: an optimiser in theory examine

        10 x 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 orders (i.e. ca. 3.5 M)

Then each of the 9 joins could be

1. nested loop using one, none or both of 2 indexes
2. hash joined
3. sort merge joined

scaling the problem up by a factor of 6 ^ 9 = 10 M Time another three for way of getting into the first table

        3 x 10M x 3.5M = 100,000,000,000,000

A few minutes sounds quite good to me !!

P.S. You've probably guessed that Oracle uses lots of short cuts to avoid most of the work.

P.P.S There are also some bugs in the 7.3.3 optimiser code where Oracle allocates far too much memory and does much to much work - trace 'truss' on the process and you may find it malloc'ing literally thousands of 4K or 8K chunks.

> Several months ago I posted a message indicating we had some
problemes with
> regards to the Oracle 7.3.3 optimizer, NOT with regards to the
execution
> plan but related to very high parse times (e.g. 3 min parse vs 4sec
> execution). I could not follow up that message because of time
constraints.
> In the meantime, we encountered the same problem with some other
queries,
> usually, when the number of tables in the select clause grows
larger (e.g.
> >= 5 tables).
> So far we have been unable to find any documentation on the parse
algorithm
> (to see if we could apply some sensible heuristics, as some of our
queries
> may be generated dynamically), neither was our local Oracle
distributor able
> to provide us with advanced information about the problem. Is it
related to
> older (say pre-8) versions, is it intrinsic to cost-based
optimization (i.e.
> a finding an optimum plan vs a good one), ... . Anyone having any
ideas or
> some good pointers to information on this topic?
Received on Sat May 16 1998 - 02:28:05 CDT