Re: EXPLAIN PLAN madness!

On Sun, 18 Nov 2001 01:27:53 GMT, nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote:

>In a sudden spasm of awareness,
>Ed Stevens doodled thusly:
>
>>OUCH!!! I didn't know that. That puts a real crimp in things. I am looking at
>>some additional indexes, was planning on using the plan cost as an assesment of
>>their impact . . . . I know that plan cost is a bit of a synthetic number, but
>>if you can't use relative values (plan A with a cost of 250000 vs plan B with a
>>cost of 250) to select the best course of action . . . . where does that leave
>>us?
>
>Woah, calm down! :-)
>

Sorry. I didn't mean to come across as "agitated". Just very surprised. Other surprises have come out of this as well. See my reply to fredericm.

>You can, in the same database, with the same settings, using the same
>data, rely on the costs put out by EXPLAIN PLAN as a _relative_
>indicator of how fast things will go.
>
>That allows you to do what-if tests for a specific SQL to see what is
>the best solution, without having to retrieve all data and timing
>that.
>
>What you shouldn't do is pick up a cost in one database and compare it
>to a cost obtained in another database and establish any relationship
>between the two based on their absolute value. That simply doesn't
>work. At least not reliably.

As a rule, yes. In my case, the test database is loaded from an export of all application data in the production database. Given that, and given that once the init.ora files are synced up a given query gives yeilds the same plan in both DB's, I would expect that impact of modification in test to be a reasonable predictor of what will happen when those changes are put into production.

But . . . . . my expectations HAVE proven wrong before!

>
>
>Cheers
>Nuno Souto
>nsouto_at_optushome.com.au.nospam

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)