Re: CBO vrs Rule

In article <38bd39de$0$3481_at_wodc7nh0.news.uu.net>,   "Christopher Spence" <nospam_at_cspence@delphi-tech.com> wrote:
> Anyone have any experience with problems with CBO in 7.3.x?
>
> Full schema Analyze, 25% Estimate Statistics
> 3 days later
> 3 tables join queried with hint /*+ rule */
> Results: 240ms
> 3 tables join queried with no hints (use CBO as there is statistics)
> Results: 2300ms
> Manually Analyze the 3 tables
> 3 tables join queried with no hints (use CBO as there is statistics)
> Results: 220ms
>
> Comparing statistics between 3 days ago and today, the data (row_num,
> avg_row_leng, blocks and such) where around 1% or even less than what
 the
> analyze did last time, very few things if anything changed in this
 schema
> since then but performance was drastically effected. The main reason
 for
> the performance change is a use of a FTS on one of the tables prior
 to doing
> re-analyze
>

I have been using the CBO since version 7 and have seen queries go to lunch like this since day one. You can wait to see if it happens again on this query or it may be just one of those things.

We lock in the desired plan using hints on SQL statements that change to bad plans for no apparant reason more than once. I recommend the use of the ORDERED hint with USE_NL and INDEX hints over RULE since one day Oracle should be merciful and just drop the RBO.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.