Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!

Re: EXPLAIN PLAN : better with RULE!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 20 Jul 2002 10:40:19 +0100
Message-ID: <1027158123.29738.1.nnrp-14.9e984b29@news.demon.co.uk>

The plans may be the same - even at

>> > db_file_multiblock_read_count: 32

and

    >ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 15; you are effectively telling Oracle that a single block read is only 7 times as fast as a 32 block read. (Actually because of a 'choke' mechanism Oracle will be using a value closer to 16.39 then 32 in the calculations).

Purely for test purposes, you might try values of 6 and then 3 for this parameter.

Bear in mind that sort_area_size and hash_area_size also affect the calculations, so if you've set these to very high values then you've encouraged Oracle to use sort/merge, or hash joins in preference to nested loop.

Rather than just fiddling with parameters though, the next thing to do is look at the indexes and columns that the RBO has used in its plan, and examine the statistics stored in user_indexes, user_tab_columns, and user_histograms to see why CBO doesn't like them. Also check the stats against the real data to see if the stats are misleading.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

Steve Mitchell wrote in message ...

>
>Thanks Lewis,
>
>I agree it seems that the multiblock read parameters are misleading the
CBO.
>The curious thing is that when I change some of these parameters:
>
>ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90;
>
>ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 15;
>
>..I still get the same plan from the CBO. I have goofed with the
>DB_FILE_MULTIBLOCK_READ_COUNT as well--trying different lower settings, and
>still get much slower results from the CBO than RBO.
>
>Thanks for any help.
>
>--steve
Received on Sat Jul 20 2002 - 04:40:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US