Re: EXPLAIN PLAN differences

Differences in data will cause differences in execution plans, in general, if you are using CBO. At what "amount of difference" the change takes place is hard to say.

You reported the cost numbers on each plan, also take a look at the cardinality values on each step of the plan. This is the expected number of rows the optimizer thinks it will process on each step. Also - is the structure of the plan different ie. on the lower volume of data, is there a full table scan vs. an index scan (or vice versa), hash join vs. nested loops, etc..?

What about performance? What are the timings on each SQL (cost 26 vs 158)? Do the timings scale linearly when compared to differences in data? (ie. if the cost26 tables have 1000 rows, and the cost 158 tables have 10000 rows, what is the ratio in timings?)

Plan stability via stored outlines may help because you are freezing the execution plan at a point in time. However, this could mean that sometime down the road, as the db environment changes, you could be forgoing a better dynamic plan from the optimizer. With a stored outline, you are basically telling oracle to always build this specific execution plan. However, that was based on the environment/stats, etc when you stored the outline. It may be possible to have the optimizer pick up a better plan in the future.

Regards,
-Darren
www.leccotech.com

"traceable1" <traceable1_at_hotmail.com> wrote in message news:8551d8c9.0206040837.364b51f7_at_posting.google.com...
> Please Help!!
>
> If I do an execution plan on the same query on 2 different boxes, I
> get different results (Cost 26 vs 158). What does this mean? I'm
> trying to make the two databases behave the same, but they will not.
> One box has a lot more data than the other, but statistics have been
> run. I've checked the OPTIMIZER_MODE, what else could be causing the
> difference?
> They are both Oracle 8.1.7 on W2K.
>
> Any thoughts are very much appreciated!!
>
> Trace
Received on Wed Jun 05 2002 - 09:06:25 CDT