Re: Oracle optimization

Michel Tilman wrote:
>
> In optimizing our Oracle 7.2 queries, we have several questions:
>
> 1. What decent references or literature exists around there regarding the
> optimization of:
> joins spanning several tables (the oracle fine-tuning courses so far
> essentially only discussed 2-table join optimizations in detail), in
> particular outer joins
> subqueries
> the use of rule-based vs cost-based optimizations
>
> 2. When switching from rule-based to cost-based optimization some queries
> involving subqueries performed MUCH slower. Is this typical of subqueries
> or do we need to take other factors into account?
>
> 3. Is there any gain to be had whatsover from modelling our own null
> records and null values in order to avoid the Oracle outer joins?
>
> 4. To which degree does the size of shared caches (queries, cursors) impact
> performance, for instance by deviating from the optimum setting as
> recommended by Oracle?
>
> Thanks,
>
> Michel Tilman
>
> ----------------------------------------------------
>
> Michel Tilman
> Unisys Belgium n.v.
> E-mail: mtilman_at_argo.be

when we converted from from 7.0 to 7.2 we had to reoptimize every query because the cost based optimizer changed it's calculations

as a rule 'rule' base queries ran faster but a number ran very slow

so we purchase platinums plan analyzer
this let us from one screen test four modes

    rule first_rows all_rows and any hint this showed the plan for all, rows searched for all, and elapsed time

this saved us weeks of work Received on Fri Dec 13 1996 - 00:00:00 CST