Re: Rule-based optimizer wins big over CBO

From: Jason Buchanan <jason.buchanan_at_gmail.com>
Date: 15 Jul 2004 15:08:34 -0700
Message-ID: <5f258456.0407151408.ce26978@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:08:34 CDT

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