problems with CBO and explain plans

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 Received on Mon Jan 05 2004 - 22:20:19 CST