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

Home -> Community -> Usenet -> c.d.o.misc -> Re: problems with CBO and explain plans

Re: problems with CBO and explain plans

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 05 Jan 2004 22:09:21 -0800
Message-ID: <1073369274.768734@yasure>


Derek wrote:

> Hi all,
>
> Does anyone know if its possible to export an exact explain plan from one
> database to another database? I've tried stored outlines but the execution
> plan is not the same.
>
>
> To explain in more detail.
> I've got two databases that are the same (in terms of data structures and
> indexes and up-to-date statistics). They sit on the same server and have the
> same init parameters and basically the same environment. I can't find any
> major differences between them. However the same SELECT statement performs
> really badly on one database and excellently on the other database and I
> can't find out why. The SELECT statement is rather complicated and the
> explain plan is uninterperable (at 260 lines). So what I want to do is make
> the slow database use the explain plan off the fast database. So I created a
> stored outline from the fast database and exported and imported it into the
> slow database (as via Oracles "Using Plan Stability" instructions using the
> OL$, OL$HINTS and OL$NODES tables). However it seems that you can't get the
> ~exact~ plan by doing this. From Oracle doco:
> "An outline consists primarily of a set of hints that is equivalent to the
> optimizer's results for the execution plan generation of a particular SQL
> statement. When Oracle creates an outline, plan stability examines the
> optimization results using the same data used to generate the execution
> plan. That is, Oracle uses the input to the execution plan to generate an
> outline, and not the execution plan itself."
> When I imported the fast stored outline and set the session to use it (and I
> checked that it was used) I got a different execution plan to the fast
> execution plan I wanted, and while the results were better this time they
> are still not satisfactory and still not as fast as the original fast
> database. I can't easily find a solution to this ... anyone else had similar
> problems of the CBO mysteriously changing plans?
>
>
> Cheers,
> Derek

What are the version numbers including patch level? Is the data the same?
Are the indexes the same?

And you can not export an Explain Plan ... what you are hopefully exporting is an execution plan.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Tue Jan 06 2004 - 00:09:21 CST

Original text of this message

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