Re: QUERY PERFORMANCE ISSUES AFTER DATABASE UPGRADE FROM 8174 TO 10.1.0.3
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: DGUPTA@xxxxxxxxxxx
- Date: Tue, 28 Jun 2005 11:56:05 -0400
On 6/28/05, GUPTA, DEEPENDER <DGUPTA@xxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
