Re: QUERY PERFORMANCE ISSUES AFTER DATABASE UPGRADE FROM 8174 TO 10.1.0.3

On 6/28/05, GUPTA, DEEPENDER <DGUPTA_at_entergy.com> wrote:
>
> 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

You probably already read this several times, but just for the sake of completeness:
Oracle(r) Database Upgrade Guide
10g Release 1 (10.1)
Part No. B10763-02
http://otn.oracle.com/pls/db10g/db10g.to_pdf?pathname=server.101%2Fb10763.pdf&remark=portal+%28Getting+Started%29

This may prove to be useful:
 "An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the Upgrade," by Roger Schrag (February 2005). http://www.dbspecialists.com/presentations.html

So you're gathering 10046 and 10053 traces of the problematic statements and are going to post examples shortly, right?

If you use optimizer_features_enabled=8.1.7 you are going to disable features that are included in the 9.2 and 10.1 CBO. Think of this in terms of applying a tourniquet that will be extremely difficult to remove.

hth.

Paul

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