Re: Cost based optimiser (on 8.1.7.4)

On 28 Aug 2002 07:21:14 -0700, hello_at_thirdchimp.net (Neil) wrote:

>Hi
>
>I'm having problems with the cost based optimiser and hoped someone
>could get me back on the straight and narrow.
>
>I have the following simple SQL statement:
>
>SELECT pj.div
>, pj.num
>, pj.ver
>FROM proj pj
>, sect ps
>WHERE ps.drawing LIKE :L_draw
>AND pj.num = ps.num
>AND pj.ver = ps.ver
>AND pj.div = ps.div
>/
>
>The proj table contains 200,000(ish) rows and the sect one contains 1
>million(ish), they share the num, div and ver columns (1 num, div, ver
>combination on proj could equate to 20 records on sect).
>
>Both tables have been analyzed with compute statistics. There is an
>index on sect.drawing and an index on proj.num, div and ver.
>
>The plan for this statement is:
>
> ID|Operation |Options |Object
>---|---------------------|-------------------------|-------------------------
> 1| MERGE JOIN | |
> 2| INDEX |FULL SCAN |PK_PROJ_1
> 3| SORT |JOIN |
> 4| TABLE ACCESS |BY INDEX ROWID |SECT
> 5| INDEX |RANGE SCAN |SECT_1
>
>It uses the index on sect.drawing successfully but full scans the one
>on proj even though it can use the div,num and ver columns on sect to
>be quite specific.
>
>If I make it use the RBO ("/*+ RULE */") I get the plan I want (I've
>timed them and its many times better):
>
> ID|Operation |Options |Object
>---|---------------------|-------------------------|-------------------------
> 1| NESTED LOOPS | |
> 2| TABLE ACCESS |BY INDEX ROWID |SECT
> 3| INDEX |RANGE SCAN |SECT_1
> 4| INDEX |UNIQUE SCAN |PK_PROJ_1
>
>I've tried using a hint of "/*+ INDEX(PROJ PK_PROJ_1) */" but this
>makes no difference.
>
>If I switch the "LIKE" to "=" then I get the same plan as the rule
>based optimiser. I'm guessing that the CBO is pessimistic and works
>out a plan assuming :L_draw is going to be very vague ("%") where the
>RBO has a much brighter outlook and trusts me to use reasonable values
>for :L_draw.
>
>Can anyone enlighten me as to what is going on?
>
>TIA
>
>Neil

Bind variables: this means CBO ignores histograms (but you might not have them at all you don't specify), and assumes an even distribution of the data.
RBO, of course, doesn't assume anything about distribution (so it *definitely doesn't have a much brighter outlook), it just uses an index wherever it can.
You could try playing around with optimizer_index_cost_adj and similar parameters, apparently full table scans are considered 'cheap' Generically, I would however start with the FIRST_ROWS and the ORDERED, and of course there is event 10053 which will dump the rationale of CBO in a trace file. The Jonathan Lewis book has a description of the output.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Aug 28 2002 - 15:18:08 CDT