Re: Horrendous Execution Plan from CBO

[Tim Gorman]

Mark,   What were the elapsed times for each run?  You show the AUTOTRACE, but did you have SET TIMING ON?   Some notes: The CBO plan isn't so horrendous (although the elapsed times would be useful to either validate or negate that statement).  The total logical reads were about 66,000 and the physical reads were 96,000.  In comparison, the RBO plan used 2.8m logical reads and 69,000 physical reads and the USE_NL plan used 3.0 logical reads and 69,000 physical reads.  So, the logical reads are roughly 2% for the CBO plan as the others, while the physical reads are roughly 50% greater... Since all hints activate the CBO, the USE_NL plan was actually using the CBO, not the RBO, so it figured out that the "CNR" tables was larger than "PUB" and performed the FULL table scan against that table, which was slightly better.  The RBO chose to lead with a full table scan on "PUB" because that table was the "right-most" in the FROM clause list (i.e. RBO reads right-to-left)... Since "CNR" and "PUB" are both "large" and both roughly the same size, then a SORT-MERGE join was a pretty good choice.  As you can see, a NESTED LOOPS join is very expensive in terms of logical reads and a HASH join always works best when one table is close in size to HASH_AREA_SIZE.  If SORT_AREA_SIZE is set very large for this database, then the CBO would obviously look favorably upon SORT-MERGE joins when deciding which join method to use;  what is the value of SORT_AREA_SIZE?  Also, what is the value of HASH_AREA_SIZE? By the default settings of OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ, the CBO has a rather dim view of the capabilities of indexes.  I usually like to set OPTIMIZER_INDEX_CACHING to "90" to better reflect the real behavior of index blocks with respect to the Buffer Cache.  What are the settings of these? The tables and indexes involved appear to be analyzed, because the AUTOTRACE output shows what looks like valid stats.   Thanks!   -Tim   ----- Original Message ----- From: "Stahlke, Mark" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, June 07, 2002 3:51 PM Subject: Horrendous Execution Plan from CBO > Greetings, > > One of our developers came to me with a fairly simple query that runs much > faster when she uses the RBO. I looked at the execution plans generated by > both the RBO and CBO and the CBO's plan is horrible. I was able to get a > reasonable plan from the CBO using a USE_NL hint. > > Do any of you SQL tuning gurus have any suggestions? I've listed all the > gory details below. > > Thanks, > Mark Stahlke > Oracle DuhBA > Denver Newspaper Agency > > The Gory Details: > Background: > Oracle 7.3.4 (I know, I know). PUB has 531324 rows. CNR has 601007 rows. > Both tables analyzed. > > The Query: >   1  SELECT >   2  c.acct_key, >   3  c.pub, >   4  c.ref_nbr, >   5  c.sls_nbr_1 cnr_sls_nbr, >   6  p.sls_eff_iss_1, >   7  p.sls_nbr_1_1, >   8  p.sls_nbr_1_2, >   9  p.sls_eff_iss_2, >  10  p.sls_nbr_2_1, >  11  p.sls_nbr_2_2, >  12  p.sls_eff_iss_3, >  13  p.sls_nbr_3_1, >  14  p.sls_nbr_3_2 >  15  FROM cnr c, pub p >  16  WHERE c.acct_key = p.acct_key >  17* AND c.pub = p.pub > > Plan and stats without hints: > Execution Plan > ---------------------------------------------------------- >    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28838 Card=597847 By >           tes=100438296) >    1    0   MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) >    2    1     SORT (JOIN) >    3    2       TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt >           es=55066856) >    4    1     SORT (JOIN) >    5    4       TABLE ACCESS (FULL) OF 'CNR' (Cost=2195 Card=598749 By >           tes=38319936) > Statistics > ---------------------------------------------------------- >         365  recursive calls >       12740  db block gets >       53167  consistent gets >       96684  physical reads >        4956  redo size >    45285104  bytes sent via SQL*Net to client >      441377  bytes received via SQL*Net from client >       40070  SQL*Net roundtrips to/from client >           0  sorts (memory) >           2  sorts (disk) >      601007  rows processed > > Plan and stats with /*+ RULE */ > Execution Plan > ---------------------------------------------------------- >    0      SELECT STATEMENT Optimizer=HINT: RULE >    1    0   NESTED LOOPS >    2    1     TABLE ACCESS (FULL) OF 'PUB' >    3    1     TABLE ACCESS (BY ROWID) OF 'CNR' >    4    3       INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) > Statistics > ---------------------------------------------------------- >           0  recursive calls >           4  db block gets >     2828280  consistent gets >       69635  physical reads >           0  redo size >    45285104  bytes sent via SQL*Net to client >      441389  bytes received via SQL*Net from client >       40070  SQL*Net roundtrips to/from client >           0  sorts (memory) >           0  sorts (disk) >      601007  rows processed > > Plan and stats with /*+ USE_NL(c p) */ > Execution Plan > ---------------------------------------------------------- >    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1204217 Card=601007 >           Bytes=100969176) >    1    0   NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) >    2    1     TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte >           s=38464448) >    3    1     TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844 Card=531324 B >           ytes=55257696) >    4    3       INDEX (UNIQUE SCAN) OF 'PK_PUB' (UNIQUE) > Statistics > ---------------------------------------------------------- >           0  recursive calls >           4  db block gets >     3062526  consistent gets >       69490  physical reads >           0  redo size >    45285104  bytes sent via SQL*Net to client >      441396  bytes received via SQL*Net from client >       40070  SQL*Net roundtrips to/from client >           0  sorts (memory) >           0  sorts (disk) >      601007  rows processed > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stahlke, Mark >   INET: [EMAIL PROTECTED] > > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 > San Diego, California        -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from).  You may > also send the HELP command for other information (like subscribing).