Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Horrendous Execution Plan from CBO
I'm going along with John. Try the hash join.
CBO was less mature in v7, so it may need a little 'help' to get the plan you want. Such as a 'hash' hint, or the use_nl hint if you don't use the hash.
Jared
"Stahlke, Mark" <mstahlke_at_denvernewspaperagency.com>
Sent by: root_at_fatcity.com
06/07/2002 03:43 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: Subject: 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 Planfrom 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 fromCBO
Greetings, One of our developers came to me with a fairly simplequery that
faster when she uses the RBO. I looked at the executionplans
both the RBO and CBO and the CBO's plan is horrible. Iwas able to
reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I'velisted all
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. CNRhas 601007
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 PlanCard=597847
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28838
tes=100438296) 1 0 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'PUB' (Cost=841Card=529489
es=55066856) 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'CNR' (Cost=2195Card=598749
tes=38319936) StatisticsCard=601007
----------------------------------------------------------
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
s=38464448) 3 1 TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844Card=531324
ytes=55257696) 4 3 INDEX (UNIQUE SCAN) OF 'PK_PUB' (UNIQUE) StatisticsMailing
----------------------------------------------------------
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 /
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 (likesubscribing).
-- 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: INET: Jared.Still_at_radisys.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 - 18:31:07 CDT
![]() |
![]() |