Re: Horrendous Execution Plan from CBO

"Stahlke, Mark" wrote:
>
> Greetings,
>
> One of our developers came to me with a fairly simple query that runs much
> faster when she uses the RBO. I looked at the execution plans generated by
> both the RBO and CBO and the CBO's plan is horrible. I was able to get a
> reasonable plan from the CBO using a USE_NL hint.
>
> Do any of you SQL tuning gurus have any suggestions? I've listed all the
> gory details below.
>
> Thanks,
> Mark Stahlke
> Oracle DuhBA
> Denver Newspaper Agency
>

Mark,

   You have by now been pointed to the hash join hint, but what I'd like to underline is that the beauty or ugliness of a plan is not exactly where you should start from. Look at your stats:

Without hints :

> 12740 db block gets
> 53167 consistent gets

so about 66,000 logical reads (but 2 disk sorts, which probably hurt)

With /*+ RULE */ :
>
> 4 db block gets
> 2828280 consistent gets

 that's 2,830,000 logical reads (but no sort)

With /*+ USE_NL(c p) */

> 4 db block gets
> 3062526 consistent gets

    or 3,060,000 logical reads (no sort)

With the hash hint :
> 8 db block gets
> 58649 consistent gets

which is 58,650 or about - once again, no sort.

In other words, even in its primitive 7.3.4 incarnation, the CBO didn't, in fact, totally botch up the job. Actually, as I tend to think that real beauty lies in logical reads much more than in the plan, for those unfortunate disk sorts the 'no hint' version still is not far beyond the hash join version. Perhaps that simply altering SORT_AREA_SIZE could have done much to help, in terms of elapsed time. For this type of query (join with no other condition than the join condition and similar-sized tables) there is nothing better than the plain old full scan (especially when parallelism kicks in), as your nested loops attempt proves. If I were you, and if creating another index is a bearable nuisance, I would create a concatenated index on the four columns from CNR in your query. If CNR has much more many columns, this would allow Oracle to do a fast full scan of the index (rather than a full scan of the table) - which is likely to mean much fewer blocks to wade through.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).