RE: HASH_JOIN on RBO?

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Thu, 21 Sep 2000 07:13:39 -0700 (PDT)
Message-Id: <10626.117572@fatcity.com>

--0-1067854538-969545619=:11497
Content-Type: text/plain; charset=us-ascii

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-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Thursday, 21 September 2000 22:38
To: Steve Adams
Cc: main oracle list
Subject: RE: HASH_JOIN on RBO?

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?

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Thursday, 21 September 2000 3:55
To: Steve Adams
Subject: RE: HASH_JOIN on RBO?

Here it is

Steve Adams wrote:

Hi Chuck,

Sorry, I meant the raw trace file itself. Do you still have it?

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Wednesday, 20 September 2000 22:44 To: Steve Adams
Cc: main oracle list
Subject: RE: HASH_JOIN on RBO?

here are the attachments you wanted (why_cost.prf and why_cost.lst)

-----Original Message-----
From: Steve Adams [mailto:steve.adams_at_ixora.com.au] Sent: Tuesday, 19 September 2000 0:42
To: Chuck Hamilton
Cc: main oracle list
Subject: RE: HASH_JOIN on RBO?

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.

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Monday, 18 September 2000 23:01
To: Steve Adams
Cc: main oracle list
Subject: RE: HASH_JOIN on RBO?

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.

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Thursday, 14 September 2000 2:19
To: Steve Adams
Subject: RE: HASH_JOIN on RBO?

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.

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Wednesday, 13 September 2000 7:31
To: Multiple recipients of list ORACLE-L Subject: HASH_JOIN on RBO?

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. --0-1067854538-969545619=:11497
Content-Type: text/html; charset=us-ascii

<P>That did it. I queried&nbsp;dba_tables&nbsp;and found some of the tables&nbsp;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&nbsp;joins.</P>
<P>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.</P>
<P>&nbsp; <B><I>Steve Adams &lt;steve.adams_at_ixora.com.au&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Hi Chuck,<BR><BR>Sorry, I should have looked more closely. The problem is that several of the<BR>tables have their degree of parallelism set to 24!<BR><BR>@ Regards,<BR>@ Steve Adams<BR>@ http://www.ixora.com.au/<BR>@ http://www.christianity.net.au/<BR>@<BR>@ Going to OpenWorld?<BR>@ Catch the Ixora performance tuning seminar too!<BR>@ See http://www.ixora.com.au/seminars/ for details.<BR><BR><BR>-----Original Message-----<BR>From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com]<BR>Sent: Thursday, 21 September 2000 22:38<BR>To: Steve Adams<BR>Cc: main oracle list<BR>Subject: RE: HASH_JOIN on RBO?<BR><BR><BR>Nope. There are no IOTs, no stored outlines, or function based indexes. It's got<BR>me puzzled. Has Oracle documented anywhere that there were changes to the RBO in<BR>8i to allow it to use hash joins?<BR>The tables have stats because it's a db that gets refreshed every night via<BR>exp!
/imp. We never disables stats on the export. We figured setting the<BR>optimizer_mode to rule would be sufficient to make it ignore them. 99% of the<BR>stuff running on it runs better with RBO and for the few things that run better<BR>with CBO we could just hint it with /*choose*/.<BR><BR>Steve Adams <STEVE.ADAMS@IXORA.COM.AU>wrote:<BR><BR>Hi Chuck,<BR><BR>The trace files show that some of the tables do in fact have statistics, whereas<BR>most don't. That explains the poor execution plan, but it does not explain the<BR>use of the cost based optimizer. Other things that could cause these symptoms<BR>are if one of the tables were an IOT or had function based indexes, or if there<BR>were a query outline that matched the text but was invalid. I've had a quick<BR>look at the trace file, but do not see any relevant evidence. Do any of these<BR>ideas help?<BR><BR>@ Regards,<BR>@ Steve Adams<BR>@ http://www.ixora.com.au/<BR>@ http://www.christianity.net.au/<BR>@<BR>@ Going to OpenWorl!
d?<BR>@ Catch the Ixora performance tuning seminar too!<BR>@ See http://www.ixora.com.au/seminars/ for details.<BR><BR><BR>-----Original Message-----<BR>From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com]<BR>Sent: Thursday, 21 September 2000 3:55<BR>To: Steve Adams<BR>Subject: RE: HASH_JOIN on RBO?<BR><BR><BR>Here it is<BR><BR>Steve Adams wrote:<BR><BR>Hi Chuck,<BR><BR>Sorry, I meant the raw trace file itself. Do you still have it?<BR><BR>@ Regards,<BR>@ Steve Adams<BR>@ http://www.ixora.com.au/<BR>@ http://www.christianity.net.au/<BR>@<BR>@ Going to OpenWorld?<BR>@ Catch the Ixora performance tuning seminar too!<BR>@ See http://www.ixora.com.au/seminars/ for details.<BR><BR><BR>-----Original Message-----<BR>From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com]<BR>Sent: Wednesday, 20 September 2000 22:44<BR>To: Steve Adams<BR>Cc: main oracle list<BR>Subject: RE: HASH_JOIN on RBO?<BR><BR><BR>here are the attachments you wanted (why_cost.prf and why_cost.lst)<BR><BR>-----Ori!
ginal Message-----<BR>From: Steve Adams [mailto:steve.adams@ixora.com.au]<BR>Sent: Tuesday, 19 September 2000 0:42<BR>To: Chuck Hamilton<BR>Cc: main oracle list<BR>Subject: RE: HASH_JOIN on RBO?<BR><BR><BR>Hi Chuck,<BR><BR>In that case, either one of the other pieces of information that you've given us<BR>is incorrect, or this is a bug. Could you please try it as follows<BR><BR>spool why_cost<BR>set long 1000<BR>column text format a80 word<BR>select text from dba_views where view_name = 'PS_QVC_PERS_SRCH_VW';<BR>alter session set events '10053 trace name context forever';<BR>alter session set sql_trace = true;<BR>set autotrace traceonly explain<BR>SELECT --+ RULE<BR>...<BR>spool off<BR>exit<BR><BR>Please note the extra space before the hint comment to force a reparse. If you<BR>need to do it more than once, insert one more space each time, or flush the<BR>shared pool first. Please post the spool file and the trace file.<BR><BR>@ Regards,<BR>@ Steve Adams<BR>@ http://www.ixora.!
com.au/<BR>@ http://www.christianity.net.au/<BR>@<BR>@ Going to OpenWorld?<BR>@ Catch the Ixora performance tuning seminar too!<BR>@ See http://www.ixora.com.au/seminars/ for details.<BR><BR><BR>-----Original Message-----<BR>From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com]<BR>Sent: Monday, 18 September 2000 23:01<BR>To: Steve Adams<BR>Cc: main oracle list<BR>Subject: RE: HASH_JOIN on RBO?<BR><BR><BR>Moving the hint didn't change the explain plan. I puti t right after the INSERT.<BR>I also tried removing the insert and just tuning the SELECT portion,putting the<BR>hint right after the select....<BR>SELECT --+ RULE<BR>A.*<BR>FROM PS_QVC_PERS_SRCH_VW A,<BR>PS_QVC_QUERY_USERS B<BR>WHERE A.OPRID=B.OPRCLASS<BR><BR>It still chooses a hash join. Only with a first_rows hint does it use all nested<BR>loops joins.<BR>Steve Adams wrote:<BR><BR>Hi Chuck,<BR><BR>Your hint is in the wrong position. It should come after the INSERT, not the<BR>SELECT. Although the insert and the select a!

re 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----- From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com] Sent: Thursday, 14 Se! ptember 2000 2:19 To: Steve Adams Subject: RE: HASH_JOIN on RBO? 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----- From: Chuck Hamilton [mailto:chuck_h! amilton_at_yahoo.com] Sent: Wednesday, 13 September 2000 7:31 To: Multiple recipients of list ORACLE-L Subject: HASH_JOIN on RBO? 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. </BLOCKQUOTE> <hr size=1>Do You Yahoo!? Received on Thu Sep 21 2000 - 09:13:39 CDT