|
-
 Originally Posted by srsrinu
We have upgraded our databases from 9.2.0.8 to 10.2.0.3. After the upgrade we have set Optimizer_Features_Enable=10.2.0.3. We have faced the performance slow down after this setup.
Oracle has introduced a series of changes in Ora10g you want to take into consideration.
Ora9i had an I/O based cost optimazer while Ora10g has by default a CPU based cost optimazer, this means Ora10g would try to use less CPU by doing more FTS. An alternative is to set _optimizer_cost_model parameter to "io" If you consider touching this hidden parameter please check before with Oracle support.
Ora9i gathered performance statistics might not be enough for the same database after upgrading to Ora10g. You may want to gather fresh stats on Ora10g using dbms_stats. The more histograms you get, the more chances Ora10g has of deciding for a efficient execution plan.
Finally, when you set optimizer_features_enable=10.2.0.3 be sure optimizer_mode IS NOT SET to "choose", it should be usually set to first_rows for a OLTP system while set to all_rows for a DSS system. Ora10g default is all_rows.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote