| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Rule-based optimizer wins big over CBO - why?
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:
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 Tue Jul 13 2004 - 18:34:35 CDT
------------------------------------------------------------------------------------------
 |
 |