Re: QUERY PERFORMANCE ISSUES AFTER DATABASE UPGRADE FROM 8174 TO

Hi All,

We recently have migrated 3 databases to 10g. The two 9i databases are having no problems, however the 8i (8.1.7.4) database seems to have several performance issues. We have several queries which take milliseconds in 8i database but in the upgraded 10G database it is taking minutes to produce the same results. We have run this same query against our data warehouse, which was migrated from 9i to 10g, and the query returns immediately.

For trial purpose we have created another 10G database and imported the data from 8i database but the query was taking same amount of time but when I deleted the statistics it came out in milliseconds. Even in the database which was upgraded from 8i - 10G, I deleted the statistics and the query performance changed from minutes to milliseconds. I computed the statistics again and it again went back to 4-5 minutes.

Oracle has suggested to set OPTIMIZER_FEATURE_ENABLED=8.1.7.and all the queries are returning in milliseconds after setting this parameter. Does anybody know that if I will use this parameter what other parameters and features of Oracle 10G will be disabled. One difference I know is that it will use the default "RULE BASE" optimizer of 8i..

Pls suggest.
Thanks
Deepender

--
http://www.freelists.org/webpage/oracle-l