Message-Id: <10626.117598@fatcity.com> From: "Madhavan Amruthur" Date: Thu, 21 Sep 2000 13:01:41 PDT Subject: RE: HASH_JOIN on RBO? Hi Chuck, Unfortunately setting the parallel degree > 1 always makes use of the CBO and the parallel degree has to be 1 for RBO to be used. Hope this helps. Regards, Madhavan IBm Corporation >From: Chuck Hamilton >Reply-To: ORACLE-L@fatcity.com >To: Multiple recipients of list ORACLE-L >Subject: RE: HASH_JOIN on RBO? >Date: Thu, 21 Sep 2000 07:15:31 -0800 > > >That did it. I queried dba_tables and found some of the tables had parallel >set to "default" and some set to "1". I ran an "alter table ... noparallel" >on all of the tables which set them all to 1, re-explained the plan, and >oila, no more hash joins. > >Thanks for the help. I didn't know that having degree set to "default" >would override a rule hint and cause the CBO to be used. > > Steve Adams wrote: >Hi Chuck, > >Sorry, I should have looked more closely. The problem is that several of >the >tables have their degree of parallelism set to 24! > >@ Regards, >@ Steve Adams >@ http://www.ixora.com.au/ >@ http://www.christianity.net.au/ >@ >@ Going to OpenWorld? >@ Catch the Ixora performance tuning seminar too! >@ See http://www.ixora.com.au/seminars/ for details. > > >-----Original Message----- >Sent: Thursday, 21 September 2000 22:38 >To: Steve Adams >Cc: main oracle list > > >Nope. There are no IOTs, no stored outlines, or function based indexes. >It's got >me puzzled. Has Oracle documented anywhere that there were changes to the >RBO in >8i to allow it to use hash joins? >The tables have stats because it's a db that gets refreshed every night via >exp/imp. We never disables stats on the export. We figured setting the >optimizer_mode to rule would be sufficient to make it ignore them. 99% of >the >stuff running on it runs better with RBO and for the few things that run >better >with CBO we could just hint it with /*choose*/. > >Steve Adams wrote: > >Hi Chuck, > >The trace files show that some of the tables do in fact have statistics, >whereas >most don't. That explains the poor execution plan, but it does not explain >the >use of the cost based optimizer. Other things that could cause these >symptoms >are if one of the tables were an IOT or had function based indexes, or if >there >were a query outline that matched the text but was invalid. I've had a >quick >look at the trace file, but do not see any relevant evidence. Do any of >these >ideas help? > >@ Regards, >@ Steve Adams >@ http://www.ixora.com.au/ >@ http://www.christianity.net.au/ >@ >@ Going to OpenWorld? >@ Catch the Ixora performance tuning seminar too! >@ See http://www.ixora.com.au/seminars/ for details. > > >-----Original Message----- >Sent: Thursday, 21 September 2000 3:55 >To: Steve Adams > > >Here it is > >Steve Adams wrote: > >Hi Chuck, > >Sorry, I meant the raw trace file itself. Do you still have it? > >@ Regards, >@ Steve Adams >@ http://www.ixora.com.au/ >@ http://www.christianity.net.au/ >@ >@ Going to OpenWorld? >@ Catch the Ixora performance tuning seminar too! >@ See http://www.ixora.com.au/seminars/ for details. > > >-----Original Message----- >Sent: Wednesday, 20 September 2000 22:44 >To: Steve Adams >Cc: main oracle list > > >here are the attachments you wanted (why_cost.prf and why_cost.lst) > >-----Original Message----- >Sent: Tuesday, 19 September 2000 0:42 >To: Chuck Hamilton >Cc: main oracle list > > >Hi Chuck, > >In that case, either one of the other pieces of information that you've >given us >is incorrect, or this is a bug. Could you please try it as follows > >spool why_cost >set long 1000 >column text format a80 word >select text from dba_views where view_name = 'PS_QVC_PERS_SRCH_VW'; >alter session set events '10053 trace name context forever'; >alter session set sql_trace = true; >set autotrace traceonly explain >SELECT --+ RULE >... >spool off >exit > >Please note the extra space before the hint comment to force a reparse. If >you >need to do it more than once, insert one more space each time, or flush the >shared pool first. Please post the spool file and the trace file. > >@ Regards, >@ Steve Adams >@ http://www.ixora.com.au/ >@ http://www.christianity.net.au/ >@ >@ Going to OpenWorld? >@ Catch the Ixora performance tuning seminar too! >@ See http://www.ixora.com.au/seminars/ for details. > > >-----Original Message----- >Sent: Monday, 18 September 2000 23:01 >To: Steve Adams >Cc: main oracle list > > >Moving the hint didn't change the explain plan. I puti t right after the >INSERT. >I also tried removing the insert and just tuning the SELECT portion,putting >the >hint right after the select.... >SELECT --+ RULE >A.* >FROM PS_QVC_PERS_SRCH_VW A, >PS_QVC_QUERY_USERS B >WHERE A.OPRID=B.OPRCLASS > >It still chooses a hash join. Only with a first_rows hint does it use all >nested >loops joins. >Steve Adams wrote: > >Hi Chuck, > >Your hint is in the wrong position. It should come after the INSERT, not >the >SELECT. Although the insert and the select are optimized separately, they >are >treated as a single statement block so far as determining an optimization >approach is concerned. Therefore a RULE hint needs to be placed after the >first >keyword in that statement to be effective. > >I have just checked the documentation on this, and unfortunately it is not >very >helpful. The Oracle8i Tuning guide even says, "A statement block can have >only >one comment containing hints. This comment can only follow the SELECT, >UPDATE, >or DELETE keyword." which suggests that you cannot put a hint after an >INSERT >keyword at all. Of course, you can and you sometimes must. > >@ Regards, >@ Steve Adams >@ http://www.ixora.com.au/ >@ http://www.christianity.net.au/ >@ >@ Going to OpenWorld? >@ Catch the Ixora performance tuning seminar too! >@ See http://www.ixora.com.au/seminars/ for details. > >-----Original Message----- >Sent: Thursday, 14 September 2000 2:19 >To: Steve Adams > > >The really odd thing is that the init.ora file has optimizer_mode = RULE. >Here's the SQL. It's pretty simple... > >INSERT INTO PS_QVC_FAST_SEC >SELECT --+ RULE >A.* >FROM PS_QVC_PERS_SRCH_VW A, >PS_QVC_QUERY_USERS B >WHERE A.OPRID=B.OPRCLASS; > >The view it references (PA_QVC_PERS_SRCH_VW) has no hints at all in it's >definition. I'm still perplexed by this one. > > >Steve Adams wrote: > >Hi Chuck, > >It sounds like there is some syntax error in the hint so that it just has >the >effect of forcing CBO to be used despite the absence of statistics. > >@ Regards, >@ Steve Adams >@ http://www.ixora.com.au/ >@ http://www.christianity.net.au/ >@ >@ Going to OpenWorld? >@ Catch the Ixora performance tuning seminar too! >@ See http://www.ixora.com.au/seminars/ for details. > > >-----Original Message----- >Sent: Wednesday, 13 September 2000 7:31 >To: Multiple recipients of list ORACLE-L > > >I have a query with a RULE hint, but the execution plan is show hash joins. >Is >this something new in the 8.1.5 optimizer? It used to be that you could >only get >HASH joins with CBO. Also, each of the operations is showing a cost and >cardinality even though none of the tables have stats. The net result is >I'm >getting a horrible execution plan that includes Cartesian merges and runs >for >hours when previously this query ran in minutes. Any ideas? > > > > > >Do You Yahoo!? >Send instant messages & get email alerts with Yahoo! Messenger. > > > >--------------------------------- >Do You Yahoo!? >Send instant messages & get email alerts with Yahoo! Messenger.