Re: oracle 8.0.6 and rule-based optimizer

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E59CDE1.36755DC0_at_exesolutions.com>...
> david spaisman wrote:
>
> > Hello:
> >
> > I just was given responsibility for an application(ERP) that is written
> > in microfocus cobol and runs on Oracle 8.0.6. The application must run
> > on an rule-based optimiser according to the vendor.
> >
> > I realize now tht my tuning possibiklites will be greatly limited
> > becuase of rule-based leaves me with few tuning chocices.
> >
> > I have some questiosn:
> >
> > 1) Cn I still use hints?
> >
> > 2)are there any other problems I should be aware of?
> >
> > The application deadlocks at least twice a week because of slects with
> > updates written by the vendor in their code(I checked he laert log). Any
> > recommendatiuons around this?
> >
> > David Spaisman
>
> Talk to management about bringing in an independent consultant to evaluate
> the cost to the organization of keeping this pathetic dinosaur or moving to
> something supported by Oracle in the current millenium. It will likely never
> get any better than it currently is. And an upgrade to the operating system
> could make it substantially worse.
>
> If they won't listen ... polish up your resume and get out while you still
> have marketable skills.
>
> If you need references to an independent consultant in your area you can
> contact me off-line.
>
> Daniel Morgan

David, since 7.3 if you use a any hint in your SQL except for RULE the statement runs cost based even in the absence of statistics on the tables. But this implies yoy have access to the source SQL.

We had a vendor package written per the vendor to run rule based. The customer was unhappy with performance so we added statistics and went CBO. It worked; I had expected at least one critical job to go to lunch so I expected to have to back the change off, but it was all I had to offer since we had no access to the source. We ran that way with almost good performance for a couple of years before changing vendor packages.

If you can upgrade the Oracle version to 8.1.7.4 then you will gain access to outlines and potentially can hint the SQL even if you do not have access to the source. Then no matter what the vendor says you can run CBO and adjust any SQL that the CBO does not run well.

HTH -- Mark D Powell -- Received on Mon Feb 24 2003 - 08:37:36 CST