TIP 51# : Performance issue after upgrade to 10g.
I faced with a performance issue for a client right after upgrade from 9i to 10g.
We all know that optimizer in 10g is different animal which is supposed to work better and more inteligent.This is almost true but not always.
Some queries after upgrade from 9i to 10g may have performance issue which means they may run slower in 10g.
For figuring out whether or not performance issue is because of new enhanced features in 10g please turn off the following options and run query in 10g.
alter session set "_optimizer_cost_based_transformation" =off; (Disable subquery unnesting and view merging -- New 10g optimizer feature).
alter session set "_gby_hash_aggregation_enabled" = FALSE; (Disable Hash group by aggregation -- New 10g optimizer feature).
If disabling above options resolve the situation, the performance issue is definitely because of new features in 10g optimizer. Otherwise, optimizer would not be culprit.
Subscribe to:
Post Comments (Atom)
6 comments:
Shervin,
Isn't it a typical example of pure guesswork? BAAG.
Thanks for comment.
Sorry I should disagree with you.
I did not mention that this definitely resolve any performance problem after 10g upgrade.
As we know, basic strategy for solving a problem would be removing probable culprit.
This issue is a typical example of applying this strategy. After upgrade to 10g, the optimizer is one of many elements which may cause performance problem.
With applying steps which I mentioned in this post, you can determine whether or not optimizer is culprit which was in my case.
There are many possible issues and addressing one of possible causes without even proving it first is pure guesswork. I'm not pointing out on whether this will definitely resolve the issue or not. I'm pointing out that this troubleshooting method is flawed.
As we know, basic strategy for solving a problem would be removing probable culprit.
This is indeed very popular concept but it's usually inappropriate problem-solving technique to say the least. Especially in such cases, when there are other, more deterministic, troubleshooting methods available.
Now, if you want to completely disable new features of the optimizer to make sure it's impact of CBO - you can go with optimizer_features_enable.
Thanks for comment.
There are many possible issues and addressing one of possible causes without even proving it first is pure guesswork
I do not want toprove anything as weel as I do not want to guess anything.I just wanted to share my experience with others.I faced with this problem and we all know optimizer enhancements were designed to improve performance but in some cases can cause some queries to perform no better or worse than before. This post shows how to test whether or not the optimizer is culprit.
if you want to completely disable new features of the optimizer to make sure it's impact of CBO - you can go with optimizer_features_enable .
As you may know there are 2 enhancements in 10g optimizer in comparison with 9i.
1.Subquery unnesting and View merging
2. Hash Group by aggregation
The first feature can be disabled with disabling "_optimizer_cost_based_transformation" and second is disabled with setting "_gby_hash_aggregation_enabled" to FALSE, while OPTIMIZER_FEATURES_ENABLE disables both features.
I tried to disable these 2 parameters in separate steps to find out which probable enhancement would cause the performance issue.At the same time, other queries would still take advantage of active enhancement.
Hope that makes sense to you.
By the way, guessing your identity from your signature (oracloid) which has not valid profile is guess work !!!!
Didn't mean to be anonymous - my profile was screwed up. Fixed now. An you can just shot while you are in the office - I'll hear you. ;-) So I'd rather take this discussion offline.
We upgraded from 10.2.0.2 to 10.2.0.4 and are having very poor performance. Query time changed from 1 sec to 5 mins.
What we tried :
1. optimizer_features_enable = 10.2.0.2 _optimizer_cost_based_transformation = on
_gby_hash_aggregation_enabled =TRUE
DID NOT HELP
2.
optimizer_features_enable = 10.2.0.4 _optimizer_cost_based_transformation = on
_gby_hash_aggregation_enabled=FALSE
Query takes 30 secs
3
optimizer_features_enable = 10.2.0.4 _optimizer_cost_based_transformation = off
_gby_hash_aggregation_enabled=TRUE
Query takes 1 sec
4.
optimizer_features_enable = 10.2.0.4 _optimizer_cost_based_transformation = off
_gby_hash_aggregation_enabled=FALSEQuery takes 1 sec
So it looks like _optimizer_cost_based_transformation = off is what matters in our case. It did not look like optimizer_features_enable disabled both features though...?
Thanks,
Anne
Post a Comment