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 <chuck_hamilton_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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 <steve.adams_at_ixora.com.au> 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.
Received on Thu Sep 21 2000 - 15:01:41 CDT