RE: Changes to RULE based optimizer between Oracle8 and 9i

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 30 Jan 2004 09:09:26 -0800
Message-ID: <F001.005DE763.20040130090926@fatcity.com>

Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Keith,
=20
To my knowledge, RBO has not changed. That includes NOT dealing w/ IOT. So, I think if you execute a query that references an IOT, you'll implicitly invoke the CBO. That would explain why the plan is changing. So, did you change a table from heap to IOT when you moved to 9i? If so, that's what's invoking CBO.
=20
-Mark
=20
PS fatcity is going away, use freelists.org! =20
=20

Mark J. Bobak=20
Oracle DBA=20
ProQuest Company=20
Ann Arbor, MI=20
"Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole

	-----Original Message-----
	From: Keith Moore [mailto:kmoore7_at_jcpenney.com]=20
	Sent: Friday, January 30, 2004 11:54 AM
	To: Multiple recipients of list ORACLE-L
	Subject: Changes to RULE based optimizer between Oracle8 and 9i

=09
=09

We move an application that uses OPTIMIZER_MODE=3DRULE from Oracle8 to 9i. Most of it is fine, but there are two queries that have a very different execution plan. In one case, the execution time increases from less than a minute to more than an hour. Neither query uses any of the new Oracle 9i features.

	=20
	My understanding is that the Rule optimizer code has not

changed, except to account for new features like IOT's. Has anyone else seen this type of behavior?

	=20
	Keith Moore
	Oracle Certified Professional
	972-431-5126
	kmoore7_at_jcpenney.com

------_=_NextPart_001_01C3E753.A877B4A8
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2713.1100" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2>Keith,</FONT></SPAN></DIV>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =
size=3D2>To my=20
knowledge, RBO has not changed.  That includes NOT dealing w/ = IOT. =20
So, I think if you execute a query that references an IOT, you'll = implicitly=20
invoke the CBO.  That would explain why the plan is changing.  = So, did=20
you change a table from heap to IOT when you moved to 9i?  If so, = that's=20
what's invoking CBO.</FONT></SPAN></DIV>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2>-Mark</FONT></SPAN></DIV>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D184460517-30012004><FONT face=3DArial color=3D#0000ff =

size=3D2>PS  fatcity is going away, use = freelists.org!</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV> </DIV>
<P><SPAN lang=3Den-us><FONT face=3D"Comic Sans MS" color=3D#008000 =
size=3D2>Mark J.=20
Bobak</FONT></SPAN> <BR><SPAN lang=3Den-us><FONT face=3D"Comic Sans MS"=20 color=3D#008000 size=3D2>Oracle DBA</FONT></SPAN> <BR><SPAN = lang=3Den-us><FONT=20
face=3D"Comic Sans MS" color=3D#008000 size=3D2>ProQuest = Company</FONT></SPAN>=20
<BR><SPAN lang=3Den-us><FONT face=3D"Comic Sans MS" color=3D#008000 =

size=3D2>Ann Arbor,=20
MI</FONT></SPAN> <BR><SPAN lang=3Den-us><B><FONT face=3DArial =
size=3D2>"Imagination=20

was given to man to compensate him for what he is not, and a sense of = humor was=20
provided to console him for what he is."  --Horace=20 Walpole</FONT></B></SPAN></P>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV></DIV>
  <DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr = align=3Dleft><FONT=20
  face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B> = Keith Moore=20
  [mailto:kmoore7_at_jcpenney.com] <BR><B>Sent:</B> Friday, January 30, = 2004 11:54=20
  AM<BR><B>To:</B> Multiple recipients of list = ORACLE-L<BR><B>Subject:</B>=20
  Changes to RULE based optimizer between Oracle8 and = 9i<BR><BR></FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>We move an application that uses=20   OPTIMIZER_MODE=3DRULE from Oracle8 to 9i. Most of it is fine, but = there are two=20
  queries that have a very different execution plan. In one case, the = execution=20
  time increases from less than a minute to more than an hour. Neither = query=20
  uses any of the new Oracle 9i features.</FONT></DIV>   <DIV><FONT face=3DArial size=3D2></FONT> </DIV>   <DIV><FONT face=3DArial size=3D2>My understanding is that the Rule = optimizer code=20
  has not changed, except to account for new features like IOT's. Has = anyone=20
  else seen this type of behavior?</FONT></DIV>   <DIV><FONT face=3DArial size=3D2></FONT> </DIV>   <DIV><FONT face=3DArial size=3D2>Keith Moore<BR>Oracle Certified=20   Professional<BR>972-431-5126<BR><A=20
  =
href=3D"mailto:kmoore7_at_jcpenney.com">kmoore7_at_jcpenney.com</A></FONT></DIV= ></BLOCKQUOTE></BODY></HTML>
=00
------_=_NextPart_001_01C3E753.A877B4A8--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: Mark.Bobak_at_il.proquest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Fri Jan 30 2004 - 11:09:26 CST