Oracle-L: Horrendous Execution Plan from CBO

From: Stahlke, Mark <mstahlke_at_denvernewspaperagency.com>
Date: Fri, 07 Jun 2002 13:51:54 -0800
Message-ID: <F001.00477FDA.20020607135154@fatcity.com>

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: mstahlke_at_denvernewspaperagency.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Jun 07 2002 - 16:51:54 CDT