"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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
