This content has been marked as final. Show 7 replies
SBhaumik_DBA wrote:must read
In our platform we upgraded few databases to 220.127.116.11 after which we were seeing performance issues in some critical SQL’s. Because of the fact that we were hitting deadlines, we had set the value of optimizer_feature_enable (otherwise, OFE) to 10.2.0.3 in our 18.104.22.168 databases. Post this change, all queries are running fine.
Do you see any architectural/performance impact on our databases in 22.214.171.124 if we keep the OFE to 10.2.0.3? We know, we would not get the new CBO features in this case, but are there any other negative impact(s) which you foresee with this configuration? Kindly advice.
Note: Oracle suggests we may keep it to 10.2.0.3 and regular support would be available, but in case we hit any bugs w.r.t the optimizer/CBO they would not generate/deliver any fixes for us.
OS is Solaris 10.
in your case,just enable sql plan baseline feature.
after few days/week once 10g plan loaded into plan history then you can set optimizer_feature_enable =126.96.36.199,now oracle will not use the new 11g plan until it will provide more benefit from existing plan.
We have reverted the OFE to 188.8.131.52 and the issues were being resolved. However, below is the course of action(s) which took place from our end, which makes our client a bit worried:
1. We upgraded databases from 10g to 11g using the standard procedure, and no issues were reported. We even tested fallback to 10g version and it worked well.
2. Client starts the UAT testing of applicaiton after upgrade. At this time, OFE = 184.108.40.206.
3. After 50% of UAT is completed, client reports few problematic queries.
4. We change the OFE to 10.2.0.3 and see good improvements, and hence we keep OFE as 10.2.0.3 from here onwards.
5. Rest of the UAT is completed, and we change OFE back to 220.127.116.11.
Now, half of UAT is done with OFE = 11g and rest with OFE = 10g. What impact should this make on the Application?
I.e., How much change or difference in behaviour do you/all expect, based on your experiences, with above steps being done?
Note: We are aware that ideally the OFE should be equal to the DB version for getting new features of the CBO. Oracle also suggests the same, so that we get the new features, as well as we get the bug fixes for newer version. However, we are looking for a definite solution to the above, so as to determine what level of impact our changes to OFE has caused?
The front end applicaiton is PeopleSoft HRMS 8.90.000 with PeopleTools 8.49.00.
Further addition -- the 11Gr@ manual says the below about new features added in 11G.
Please advice. Thanks.
Enhanced Bind Peeking Use extended statistics to estimate selectivity Use native implementation for full outer joins Partition pruning using join filtering Group by placement optimization Null aware antijoins
If setting OFE works for you, then that's a valid short-term approach.
There are a number of alternative approaches.
If the scope of the problem is a handful of statements, then it's better to use a solution that matches the scope.
Setting OFE system-wide is going to deprive other statements of a possible performance lift as well as giving complications should you run into other bugs, etc or require patches, etc.
You could use sql plan baselines as mentioned by Kuljeet to lock in a few executions plans for the statements that are causing you problems.
You could hint OFE at a statement level.
If you can't change the source code and you don't want to lock in a full baselined plan, then you could look at using the [url http://orastory.wordpress.com/2012/03/06/sql-patch-i/]SQL Patch functionality .
I would always want to identify the SQL that has degraded and identify why it has problems or what feature is adversely affecting it.
In my experience, it is often the SQL that is poorly written/expressed that has performance issues after upgrade.
I would also advocate extending your AWR retention prior to upgrade so you can get the pre upgrade plans for your biggest hitting SQL should they degrade post upgrade, you can then use the Baseline or SQL Profile mechanisms to quickly lock in a previous plan.
To get a better list of optimizers features that have changed between 10.2.0.3 and 18.104.22.168, look at V$SYSTEM_FIX_CONTROL, e.g.:
Also see this post by Coskan about using this information to identify any specific fixes which have affected your execution plan:
select * from v$system_fix_control where optimizer_feature_enable like '11%' or optimizer_feature_enable = '10.2.0.4' order by optimizer_feature_enable, bugno;
Thanks very much for the above information! It was very helpful!
However I was trying to figure out what impact it had made when we ran UAT half way with OFE = 10g and half way with 11g.
Honestly speaking, looking at the documentation of 11gr2 from where I have given my last reply, I do not foresee much change in our PeopleSoft environment while using OFE back to 10g. However, it is always better to cross check with experts on OTN!
As of now we have reverted back to 11G and things are working fine, however above query is to look for the consistency of UAT.
Please advice .
We did the same: after an upgrade to 22.214.171.124, we had two batch jobs which decided to take forever post-upgrade. Turns out that they weren't tested by the project before the upgrade.
As a stop-gap measure, we reduced optimizer_features_enable back to 10.2.0.3, had the project re-code the two batch jobs, check them in test and then move the OFE back to 126.96.36.199.
I guess this is why Oracle don't recommend updating the compatible parameter after an upgrade until you're 100% sure all is well!