Re: Parsing and many joins

What probably is happening is that the optimizer is examining too many join combinations that don't improve performance anyway. Since 8.0.5 there is a parameter that you can set to reduce the number of plans that the optimizer is looking at. The parameter is called optimizer_max_permutations. The default is 80000 which means go and look until you have examined all plans. Try a value of 1500 or so (works good in situation that I have seen).

Anjo

Ole C Meldahl wrote:

> Hi
>
> On Oracle 8.0.6.
>
> We have table containing the following columns: object_id, attribute_id
> and value columns.
>
> We need to get the full set of attribute names and values for an object
> as one row, this obviously means alot of joins. When the number of
> attributes is small the query runs reasonably, but when we move from 16
> to 32 attibutes the querytime goes skyhigh(more than quadrupled). Why?
> Getting 2 rows from the table shouldn't be worse than getting one and
> then one more?
>
> In general, what parses faster, hinted cost or rule? What can we do to
> reduce parsetime, pinning in or expanding shared pool is not an option.
>
> ole c
Received on Mon Nov 15 1999 - 03:05:38 CST