Re: EXPLAIN PLAN madness!

On Fri, 16 Nov 2001 02:47:21 GMT, Mark Townsend <markbtownsend_at_home.com> wrote:

>in article 3bf3f396.61729662_at_ausnews.austin.ibm.com, Ed Stevens at
>Ed_Stevens_at_nospam.noway.nohow wrote on 11/15/01 9:04 AM:
>
>> How about differences in some memory spec in the SGA?
>
>Sure - differences in sort_area_size can cause different plans. Why don't
>you just diff the two init.ora's and post the results. Both explain plans
>may also help, plus the relevant stats on the objects (not sure if you can
>see these in Oracle8)
>
>Re your other reply
>
>> Test database is loaded from production regularly, so key distribution
>> should be the same, even when total data volume diverges through use.
>
>Hmm - if your test has a subset of production, then key distribution could
>be a problem unless you ensure that the distributions match (but I doubt
>this is the problem).
>
>
>
>

SGA differences got it. My test init.ora was different from prod in several key areas. To pin it down, I made the following changes one at a time, bouncing the db and taking another explain after each change:

db_block_buffers from 4511 to 3200 (reduced it?!?!?!) deleted session_cached_cursors
shared pool_size from 15000000 to 26047671 sort_area_retained_size and sort_area_size from 131072 to 614400

Only after changing the two sort_area_ parameters did the plan in test match the one one production. The estimated cost was a bit different, but that can be explained by data volume, The important thing is now that the plans are the same, I have a basis to explore the impact of proposed index and SQL changes.

(As for the key distribution and test being a "subset" of production, we load test databases from a full export of production, so test is only a "subset" to the degree that production has more data over time than does test. No reason at all to belive that this small difference would skew the keys distribution.)

For additional info, see my reply to Nuno, where I'll post the plans to direct a follow-on question.

Thanks for pointing me down the right path and expanding my understanding of this beast.

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