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

Hi Deepender,

I would recommend creating explain plans for the query in 8i and again in 10g and then compare to two to see what has changed. Then maybe you can use hints, stored outlines, column stats (histograms), and/or some more specific init parameters* in order to force the better execution plan. This would be the preferred approach, rather than restricting the entire CBO to it's old 8.1.7 behavior and disabling newer features instance-wide. Also, RULE is not the default for 8i - according to the 8i documentation, the default for OPTIMIZER_MODE is CHOOSE, which is defined as "CHOOSE specifies cost-based optimization for a SQL statement if the data dictionary contains statistics for at least one table accessed in the statement. If no such statistics are present, the optimizer uses the rule-based approach."

• Here are some other init parameters you might want consider changing to affect the execution plan: optimizer_index_caching optimizer_index_cost_adj db_file_multiblock_read_count
  _complex_view_merging
  _parralel_broadcast_enabled
  _push_join_union_view
  _push_join_predicate
  _unnest_subquery
  _always_anti_join
  _always_semi_join
  _b_tree_bitmap_plans
  _sort_elimination_cost_ratio
  

Regards,
Brandon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of GUPTA, DEEPENDER Sent: Tuesday, June 28, 2005 8:30 AM
To: 'oracle-l'
Subject: Re: QUERY PERFORMANCE ISSUES AFTER DATABASE UPGRADE FROM 8174 TO 10.1.0.3

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

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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