When you say "We ran statistics the same way we ran them in Oracle9" what does that mean? Did you use the same dbms_stats.gather_command(s) ? You realize that the defaults have changed, do you. Unless you change the defaults "back" to how they were in 9i the same command will gather statistics differently. Most importantly, 10g gathers histograms by default which 9i didn't do. And then, of course, there is the automatic stats gathering job unless you disabled that.
At 06:05 PM 3/19/2009, Jack van Zanen wrote:
We have just upgraded our development environment from 9.2 to 10.2Can someone can help us identify why 10.2.0.4 optimizer taking double the time to execute the same statement for exact same number of rows ( 3803685 )We can see, it is picking up a different plan, NL is 9.2 against HJ in 10.2. We ran statistics the same way we ran them in Oracle9
Regards Wolfgang Breitling Centrex Consulting Corporationhttp://www.centrexcc.com
-- //www.freelists.org/webpage/oracle-l
