Re: Rule-based optimizer wins big over CBO - why?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 14 Jul 2004 18:40:29 -0700
Message-ID: <1089855648.179835@yasure>

Comments in-line.

Jason Buchanan wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1089764962.962641_at_yasure>...
>

>>Jason Buchanan wrote:
>>
>>
>>>I'm faced with a problem that I cannot identify or solve.  For a
>>>mysterious reason the RBO beats the CBO in stunning ways for the query
>>>shown below.

>
>

>>Oracle version and edition? Unknown!

>
>
> Solaris 8, E4500.
> Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production
>
> I am planning to upgrade to 9.2.0.4 shortly.

Go to 9.2.0.5.

>>Current statistics? Unknown!

>
>
> Freshly gathered via:
>
> DBMS_STATS.gather_database_stats
> (gather_sys => FALSE,
> method_opt => 'FOR ALL COLUMNS AUTO',
> options => 'GATHER AUTO',
> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
> CASCADE => TRUE
> );
>
>
>
> thanks!

Just took away my number one knee-jerk answer. The one thing I noticed, if memory still serves me, is that slow equated to hash joins and fast to nested loops. Try hinting your way out of it and see what happens.

Daniel Morgan Received on Wed Jul 14 2004 - 20:40:29 CDT