RE: Plan change after moving to 10g 10.2.0.3
Date: Mon, 22 Dec 2008 22:53:25 -0500 (EST)
Message-ID: <BAY141-DAV21C4A4FBFC8DAE8409CBDA6F30@phx.gbl>
Message-ID: <F626BA5035746548AF2DF3E71FEB5257220A0EB954@MBX01.bell.corp.bce.ca>
I had a similar problem last month. Change of plans going form 9i to 10g and
the 10g plan never finishing. I had existing 9i prod server, plus 3 new ones
that I could test it on. The three new ones all had pillar storage
underneath and 2 of the 3 were on ASM the other wasn't.
After much head scratching (mostly because the ASM/non ASM setup I had going
was causing me to chase blind alleys). I did the whole trace thing and
realized it came down to stats. I analyzed the table with histograms and got
a different plan than 9i but much faster. This was for an adhoc db so a new
different plan was fine.
There was an interesting blog that I found, I think it might be from someone
on this list. Where they ran stats, had a slow 10G query, then re-ran the
same stats again. The table monitoring picked up how the data was actually
being utilized and voila new good plan.
I guess what I'm trying to say is I spent a lot of time chasing leads when
it all came down to stats. Looks like you've done your trace already. It
also made me realize that on 10G you can have nice prod queries running
fine, have someone do a massive adhoc against your data and this might cause
a plan change.
Time to start a new thread on system stats.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 22 2008 - 21:53:25 CST