RE: Plan change after moving to 10g 10.2.0.3
Date: Mon, 22 Dec 2008 22:53:25 -0500 (EST)
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.