Re: Horrendous Execution Plan from CBO
Stahlke, Mark wrote: 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 Mark, You have by now been pointed to the hash join hint, but what I'd like to underline is that the beauty or ugliness of a plan is not exactly where you should start from. Look at your stats: Without hints : 12740 db block gets 53167 consistent gets so about 66,000 logical reads (but 2 disk sorts, which probably hurt) With /*+ RULE */ : 4 db block gets 2828280 consistent gets that's 2,830,000 logical reads (but no sort) With /*+ USE_NL(c p) */ 4 db block gets 3062526 consistent gets or 3,060,000 logical reads (no sort) With the hash hint : 8 db block gets 58649 consistent gets which is 58,650 or about - once again, no sort. In other words, even in its primitive 7.3.4 incarnation, the CBO didn't, in fact, totally botch up the job. Actually, as I tend to think that real beauty lies in logical reads much more than in the plan, for those unfortunate disk sorts the 'no hint' version still is not far beyond the hash join version. Perhaps that simply altering SORT_AREA_SIZE could have done much to help, in terms of elapsed time. For this type of query (join with no other condition than the join condition and similar-sized tables) there is nothing better than the plain old full scan (especially when parallelism kicks in), as your nested loops attempt proves. If I were you, and if creating another index is a bearable nuisance, I would create a concatenated index on the four columns from CNR in your query. If CNR has much more many columns, this would allow Oracle to do a fast full scan of the index (rather than a full scan of the table) - which is likely to mean much fewer blocks to wade through. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
Re: Horrendous Execution Plan from CBO
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
Re: Horrendous Execution Plan from CBO
When were statistics last generated? Any significant DML since then? Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 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 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 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) 10 NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844 Card=531324 B ytes=55257696) 43 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
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: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 3:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject:Re: Horrendous Execution Plan from CBO When were statistics last generated? Any significant DML since then? Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 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 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 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) 10 NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844 Card=531324 B ytes=55257696) 43 INDEX (UNIQUE SCAN) OF 'PK_PUB' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 3062526 consistent gets
RE: Horrendous Execution Plan from CBO
Mark, Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. I am not a SQL tuning guru, but it looks like this is an ideal example where Hash joins would be of immense help. You could set a largish value for HASH_AREA_SIZE (defaults to twice SORT_AREA_SIZE) to reduce visits to TEMP to write out hash tables... It would be nice if you could post the explain/costs for all three methods at the end of your tests. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 03:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 3:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject:Re: Horrendous Execution Plan from CBO When were statistics last generated? Any significant DML since then? Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 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 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) Statistics -- 0 recursive calls
RE: Horrendous Execution Plan from CBO
Thanks for the tips. The hash join looks like the best bet. Someday we'll upgrade to 8.1.7. Someday... Here is the plan and stats using /*+ USE_HASH(c p) */ Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=104706 Card=601007 B ytes=100969176) 10 HASH JOIN (Cost=104706 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (FULL) OF 'PUB' (Cost=844 Card=531324 Bytes =55257696) Statistics -- 0 recursive calls 8 db block gets 58649 consistent gets 91957 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441398 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed -Original Message- From: John Kanagaraj [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject:RE: Horrendous Execution Plan from CBO Mark, Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. I am not a SQL tuning guru, but it looks like this is an ideal example where Hash joins would be of immense help. You could set a largish value for HASH_AREA_SIZE (defaults to twice SORT_AREA_SIZE) to reduce visits to TEMP to write out hash tables... It would be nice if you could post the explain/costs for all three methods at the end of your tests. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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). -- 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).
Re: Horrendous Execution Plan from CBO
7.3.4? You're brave... Try to analyze with the following syntax: ANALYZE TABLE tname COMPUTE STATISTICS FOR ALL INDEXED COLUMNS; There was a funny with CBO that it ignored index stats completely even in some very simple joins. With 7.3.4. Usually this fixed the problem. Can't remember which patch level fixed the problem, try this syntax and see if it resolves this issue. Hint to try: FIRST_ROWS (no need to specify table aliases) Since both tables are of nearly the same number of rows hash joins would be a bad idea, particularly at 7.3.4. Stick to either merge (if you want the lot selected) or nested loops (if you want subset of all joined rows). Make sure those join columns are of the SAME data type, it may be suffering from implicit conversion. HTH Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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).
