RE: Horrendous Execution Plan from CBO

Thanks for the quick responses.

I analyzed both tables immediately before I started testing.

The USE_MERGE hint gives me the same execution plan I get without hints.

Mark Stahlke
Oracle DuhBA
Denver Newspaper Agency

-----Original Message-----

	From:	Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]
	Sent:	Friday, June 07, 2002 3:29 PM
	To:	ORACLE-L_at_fatcity.com
	Cc:	mstahlke_at_denvernewspaperagency.com
	Subject:	Re: Horrendous Execution Plan from CBO

	When were statistics last generated?

	Any significant DML since then?

	Jared





	"Stahlke, Mark" <mstahlke_at_denvernewspaperagency.com>
	Sent by: root_at_fatcity.com
	06/07/2002 02:51 PM
	Please respond to ORACLE-L

	 
	        To:     Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
	        cc: 
	        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: 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).

	

-- 
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).