Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: HASH_JOIN on RBO?

RE: HASH_JOIN on RBO?

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 22 Sep 2000 09:50:47 +1000
Message-Id: <10626.117611@fatcity.com> 





Hi Waleed,



I think this tells us that the "Optimizer=" bit at the top of the execution plan
reflect which optimizer EXPLAIN PLAN thinks you asked for, and not necessarily
the optimizer that you actually got.



@   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-----


From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
Sent: Friday, 22 September 2000 8:06


To: Multiple recipients of list ORACLE-L
Subject: RE: HASH_JOIN on RBO?




Actually the weired thing it says it's using RULE optimizer but it uses some
of the new features of cost based.



Look at this:


SQL> create table testplan ( c1 char(100));



Table created.



SQL> set autotrace on


SQL> select * from testplan;



no rows selected




Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TESTPLAN'









SQL> analyze table testplan compute statistics;



Table analyzed.



SQL> select * from testplan;



no rows selected




Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=102)
   1    0   TABLE ACCESS (FULL) OF 'TESTPLAN' (Cost=1 Card=1 Bytes=102


          )








SQL> alter session set optimizer_goal = rule;



Session altered.



SQL> select * from testplan;



no rows selected




Execution Plan




   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTPLAN'








SQL> alter table testplan parallel ( degree 2);



Table altered.



SQL> select * from testplan;



no rows selected




Execution Plan




   0      SELECT STATEMENT Optimizer=RULE (Cost=1 Card=1 Bytes=102)
   1    0   TABLE ACCESS* (FULL) OF 'TESTPLAN' (Cost=1 Card=1 Bytes=10
:Q173410

          2)                                                           00



   1 PARALLEL_TO_SERIAL            SELECT /*+ ROWID(A1) */ A1."C1" FROM
"TESTPL
                                   AN" A1 WHERE ROWID BETWEEN :B1 AND :


**************




*RULE + COSTS






Any ideas?



Waleed



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


Sent: Thursday, September 21, 2000 5:07 PM
To: Multiple recipients of list ORACLE-L




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.

_________________________________________________________________________


Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.



Share information about yourself, create your own public profile at
http://profiles.msn.com.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Madhavan Amruthur
  INET: mad012000_at_hotmail.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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


Received on Thu Sep 21 2000 - 18:50:47 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US