Performance decrease dramaticcaly by using statistics

The desire of CBO to do a full table scans is may the explain why it took longer than rule-base. In several situation by using the explain plan with statistics I saw more full table scans by CBO than by RULE BASE.
Rule base use directly the indexes and the I/O of de machine is still faster by a new generation hardware and the CBO technology is (HJR said it) from 1994 and we living know in 2002. A shame for Oracle. To make a transactions performance 10% better is already too difficult. I have to do a job to make the performace with statistics al least 2500% better and when I reached that goal is the performance equal that it was. After that .... Impossible work.

Ruud

-----Oorspronkelijk bericht-----
Van: Keith Boulton [mailto:kboulton_at_ntlworld.com] Gepost op: vrijdag 22 februari 2002 10:17 Gepost naar: server
Discussie: Performance decrease dramaticcaly by using statistics Onderwerp: 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 Wed Feb 27 2002 - 04:26:15 CST