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: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 15 Jul 2004 07:10:14 -0700
Message-ID: <1efdad5b.0407150610.49026cb9@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;

Change these at the session level. Then do:

alter system flush shared_pool; -- you need to do this or oracle will not re-parse your execution plan.

Re-run the query. Look at the plans. To get a plan go to: $ORACLE_HOME/sqlplus/admin/plustrc.sql and run that as sysdba $ORACLE_HOME/rdbms/admin/utlxplan.sql and run it as your user

Do 'set autotrace on'

run the queries. The above two settings are 'seed' data for the CBO. If they are not set properly, the CBO will make bad decisions. Contrary to popular belief, occasionally you still have to hint with the CBO and not always with a complex query..., but its less than 1/2 of 1 percent of the time unless you are doing alot of 15-20 table joins.

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 - 09:10:14 CDT

Original text of this message

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