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: Rule-based optimizer wins big over CBO - why?

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

From: VC <boston103_at_hotmail.com>
Date: Thu, 15 Jul 2004 22:41:27 GMT
Message-ID: <rKDJc.82471$JR4.72278@attbi_s54>


Hi,

Could you please post TKPROF output for both RBO and CBO runs ? Ocassionaly, the explain plan command may produce an execution plan different from the actual one (tkprof will show the real plan).

VC

"Jason Buchanan" <jason.buchanan_at_gmail.com> wrote in message news:5f258456.0407151408.ce26978_at_posting.google.com...
> rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message
news:<1efdad5b.0407150610.49026cb9_at_posting.google.com>...
> > you have to look at the explain plan and see how the queries are
> > different. My guess is that the CBO is doing an unnecessary full table
> > scan. This typically occurs because you are using the defaults for
> > optimizer_index_caching and optimizer_index_cost_adj and you are not
> > using system stats.
> >
> > Typically for most OLTP systems, I like to start my settings at:
> > optimizer_index_caching=90;
> > optimizer_index_cost_adj=10;
>
> I've tried these with little improvement.
>
> With settings tweaked:
>
> Plan Table
> --------------------------------------------------------------------------




> | Operation | Name | Rows | Bytes|
> Cost | Pstart| Pstop |
> --------------------------------------------------------------------------


> | SELECT STATEMENT | | 19K| 457K|
> 991 | | |
> | SORT UNIQUE | | 19K| 457K|
> 892 | | |
> | HASH JOIN | | 19K| 457K|
> 793 | | |
> | INDEX FULL SCAN |PK_ARTICLE_TAXONOMY | 19K| 192K|
> 2543 | | |
> | VIEW |index$_join$_001 | 201K| 2M|
> 493 | | |
> | HASH JOIN | | 19K| 457K|
> 793 | | |
> | INDEX RANGE SCAN |ARTICLE_PUBLISH_DAT | 201K| 2M|
> 683 | | |
> | INDEX FAST FULL SCAN |PK_ARTICLE_IDX | 201K| 2M|
> 683 | | |
> --------------------------------------------------------------------------


>
>
> Without settings tweaked:
>
> With the default values the plan is this:
>
> Plan Table
> --------------------------------------------------------------------------



> | Operation | Name | Rows | Bytes|
> Cost | Pstart| Pstop |
> --------------------------------------------------------------------------


> | SELECT STATEMENT | | 19K| 457K|
> 2516 | | |
> | SORT UNIQUE | | 19K| 457K|
> 2417 | | |
> | HASH JOIN | | 19K| 457K|
> 2318 | | |
> | TABLE ACCESS FULL |ARTICLE_TAXONOMY | 19K| 192K|
> 276 | | |
> | VIEW |index$_join$_001 | 201K| 2M|
> 1997 | | |
> | HASH JOIN | | 19K| 457K|
> 2318 | | |
> | INDEX RANGE SCAN |ARTICLE_PUBLISH_DAT | 201K| 2M|
> 3653 | | |
> | INDEX FAST FULL SCAN |PK_ARTICLE_IDX | 201K| 2M|
> 3653 | | |
> --------------------------------------------------------------------------


>
> I fully agree that these parameters need to be changed - my biggest
> issue is why oh why does RBO run this query in 0.25 seconds and CBO
> averages around 20 to 30 seconds?
>
> The main thing I notice with these explain plans using my own explain
> plan explainer is SORT UNIQUE TEMP 1270000 is going on with lots of
> disk writes, apparently performing the hash join against disk. If I
> disable hash joins a merge join and sort join occurs and the sort goes
> against disk again. hash_area_size is 2GB (because sort area size is
> 1G) but these tables are not that large. Do I need to explicitly
> configure hash_area_size - I thought it used the value of sort area
> size and doubled it internally.
>
> Which brings me back to the original problem, RBO rules the roost for
> this query. This is not a permanent option as we are slated to move
> to 10g and RBO is gone in 10g.
>
> I did notice, however, that the CLUF for the indexes in use is bad.
> For a table with 202,000 rows the clustering factor on its index is
> 138,299. I'm of the opinion that the table needs to be rebuilt
> considering the number of distinct values is around 1,000. I'm
> thinking that the CBO doesn't stand a chance with statistics as bad as
> these.
>
> The only thing i'm puzzled by is how the RBO is able to fetch the data
> so easily - perhaps that's the part of CBO statistics that i'm missing
> as to why it would perform so badly.
>
>
>
>
> > jason.buchanan_at_gmail.com (Jason Buchanan) wrote in message
news:<5f258456.0407131534.6bb4042d_at_posting.google.com>...
> > > 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.
> > >
> > > With the RBO the query is completed in a half second but with CBO it
> > > takes 20 seconds or more. I have gathered system statistics as well
> > > as statistics against the indexes in play to no avail.
> > >
> > > I have also tried experimenting with various values of the optimizer
> > > adj parameters but there is very little improvement (18 vs. 20
> > > seconds) with vastly different explain plans. I have also disabled
> > > hash_joins with the CBO but a lousy access path comes from it as well.
> > > Only the RBO seems to create an access path that is quick. The SQL
> > > is generated by the application - the only remedy that I have to date
> > > is to use a stored outline forcing the use of the rule based
> > > optimizer.
> > >
> > >
> > >
> > > I guess my fundamental question is "Why is the RBO winning so well"
> > > over CBO?
> > >
> > > The clustering factor on the indexes is not so good.
> > >
> > > Looking at temp_space in plan_table I see that there is a value of
> > > 1287000 there also.
> > >
> > > CLUF for PK_ARTICLE_TAXONOMY_IDX is 63640 with 815615 rows.
> > >
> > >
> > >
> > > Any advice or explanation would be greatly appreciated.
> > >
> > >
> > >
> > >
> > >
> > > The query:
> > >
> > > SELECT DISTINCT t1.art_id, t1.publish_date
> > > FROM article t1, article_taxonomy t2
> > > WHERE t2.art_id = t1.art_id
> > > AND t2.taxonomy_element_id IN
> > > (14051,
> > > 13036,
> > > 14053,
> > > 14055,
> > > 14057,
> > > 13060,
> > > 14059,
> > > 14061,
> > > 14063,
> > > 14065,
> > > 14067,
> > > 14069,
> > > 14077,
> > > 14079,
> > > 14075,
> > > 13074,
> > > 14071,
> > > 14073,
> > > 14085,
> > > 13087,
> > > 14081,
> > > 14083,
> > > 14087,
> > > 13083,
> > > 14089,
> > > 14091,
> > > 14093,
> > > 13079,
> > > 14095,
> > > 13096
> > > )
> > > AND t1.publish_date <=
> > > TO_DATE ('2004-07-12 09:00:00', 'YYYY-MM-DD
> > > HH.MI.SS')
> > > ORDER BY t1.publish_date DESC
> > > /
> > >
> > >
> > >
> > >
> > > Using CBO choose:
> > >
> > > ---------------------------------
> > > Session stats - 13-Jul 16:21:39
> > > Interval:- 21 seconds
> > > ---------------------------------
> > > Name
> > > Value
> > > ----
> > > -----
> > > opened cursors cumulative
> > > 8
> > > user calls
> > > 155
> > > recursive calls
> > > 49
> > > recursive cpu usage
> > > 1
> > > session logical reads
> > > 21,654
> > > CPU used when call started
> > > 1,853
> > > CPU used by this session
> > > 1,853
> > > session uga memory
> > > 37,504
> > > session uga memory max
> > > 701,984
> > > session pga memory
> > > 705,472
> > > session pga memory max
> > > 705,472
> > > enqueue requests
> > > 1
> > > enqueue releases
> > > 1
> > > db block gets
> > > 19
> > > consistent gets
> > > 21,635
> > > physical reads
> > > 3
> > > free buffer requested
> > > 3
> > > prefetched blocks
> > > 2
> > > calls to get snapshot scn: kcmgss
> > > 2
> > > no work - consistent read gets
> > > 21,635
> > > table scans (long tables)
> > > 1
> > > table scan rows gotten
> > > 201,095
> > > table scan blocks gotten
> > > 19,104
> > > index fast full scans (full)
> > > 1
> > > buffer is not pinned count
> > > 19,104
> > > parse time elapsed
> > > 1
> > > parse count (total)
> > > 8
> > > execute count
> > > 9
> > > bytes sent via SQL*Net to client
> > > 169,178
> > > bytes received via SQL*Net from client
> > > 29,928
> > > SQL*Net roundtrips to/from client
> > > 148
> > > sorts (memory)
> > > 1
> > > sorts (rows)
> > > 2,286
> > >
> > >
> > >
> > > alter session set optimizer_mode=choose;
> > > Plan Table
> >
> --------------------------------------------------------------------------



> > > | Operation | Name | Rows | Bytes|
> > > Cost | Pstart| Pstop |
> >
> --------------------------------------------------------------------------


> > > | SELECT STATEMENT | | 19K| 464K|
> > 7624 | | |
> > > | SORT UNIQUE | | 19K| 464K|
> > 5972 | | |
> > > | HASH JOIN | | 19K| 464K|
> > 4319 | | |
> > > | INDEX FAST FULL SCAN |PK_ARTICLE_TAXONOMY | 19K| 191K|
> > 949 | | |
> > > | TABLE ACCESS FULL |ARTICLE | 200K| 2M|
> > > 3323 | | |
> >
> --------------------------------------------------------------------------


> > >
> > >
> > >
> > >
> > >
> > > Using RBO:
> > >
> > > alter session set optimizer_mode=rule;
> > > Plan Table
> >
> --------------------------------------------------------------------------



> > > | Operation | Name | Rows | Bytes|
> > > Cost | Pstart| Pstop |
> >
> --------------------------------------------------------------------------


> > > | SELECT STATEMENT | | | |
> > > | | |
> > > | SORT UNIQUE | | | |
> > > | | |
> > > | CONCATENATION | | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> > > | NESTED LOOPS | | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE_TAXONOMY | | |
> > > | | |
> > > | INDEX RANGE SCAN |ART_TAX_TAX_ELEMENT | | |
> > > | | |
> > > | TABLE ACCESS BY INDEX |ARTICLE | | |
> > > | | |
> > > | INDEX UNIQUE SCAN |PK_ARTICLE_IDX | | |
> > > | | |
> >
> --------------------------------------------------------------------------

Received on Thu Jul 15 2004 - 17:41:27 CDT

Original text of this message

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