Re: Performance decrease dramaticcaly by using statistics

the optimiser has a desire to do full table scans and hash joins.

you may find that putting a first_rows hint in your code is the easiest thing to do: ie select /*+ first_rows */ ...

alternatively look at init parameters OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Gijanto, Ruud <RGijanto_at_ctgzorg.nl> wrote in message news:7C3B10C067C2D2119CE60020354473CE6806C7_at_SPICA...
> We have a Oracle Cliënt-application fully generated by Designer 2.1.2
> and a Databaseserver 8.1.7.2 on a IBM RS6000/AIX. The cliënt-application
> call a user-exit on the RS6000 AIX-machine and written on c (.csql).
> The embedded sqls in the user-exit use a lot of table joins. A view is
> not use because the sql-statement will be build dynamically depends on
> the transactions job and a various parameter variables.
> OPTIMIZER_MODE = CHOOSE
> Without statistics is the elapsed time of a jobs/transactions
> approximately 1 minute.
> With statistics increase the elapsed-time to min. 25 minutes.
> I don't understand why this happened.
> Could someone help me with hints where i have to looking for to analyze
> this problem.
>
> Thanks,
> Ruud Gijanto
> rgijanto_at_ctgzorg.nl
>
>
>
>
Received on Fri Feb 22 2002 - 03:16:49 CST