Think you might have misread what I wrote. I said that the RULE-based
optimizer hasn't been developed since around 1994. The Cost-based optimizer
is getting smarter and better all the time. Witness things like
skip-scanning of indexes in 9i.
Regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Gijanto, Ruud" <RGijanto_at_ctgzorg.nl> wrote in message
news:7C3B10C067C2D2119CE60020354473CE691A10_at_SPICA...
> 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 - 14:36:14 CST
