January 26, 2011

Plan stability through Upgrade to 11G-Introduction

1- Version Support is ending or already ended
2- You hit a bug and support does not give you one-off patch solution and ask you to upgrade
3- There are new features, you think you may benefit from (reduced storage, new dr options, new development/management features)
4- Your management feels like they are dynamic and they should adapt new versions asap
5- You/End users are not happy with the current performance of DB and marketing of new version “guarantees” %XX better performance

Apart from the number 5, which always depends on architecture of your application, none of them is something users of your database really care. They don’t care what level of support you have,how much compression ration you have, how fast and how small your backups are, how solid your HA solution is, how easy you develop the code with new features,how easy DBAs manage the database, how shiny the management tool , how many bugs been sorted with new version or what your manager thinks, it is and it will always be the response time they experience is, what they only care. If it is fast enough they will be happy, if it is slow they are not and they won’t be happy.

When I search on web for the experiences people had after Oracle version upgrades, it was always performance what they most complained. From 8i to 9i , 8i to 10g, 9i to 10g people always been promised by marketing that the performance would be better but they usually experienced the opposite. Performance degradation was usually related with the enhancements for sql execution plan optimizations. Sometimes it was because of bugfixes,sometimes, it was plan enhancements for better optimizations, sometimes it was new features oracle intruduced (CBO,automatic statistics gatherin, system stats etc) or sometimes it was because of lack of understanding of optimizer (thanks Jonathan Lewis for his perfect book – Cost Based Oracle Fundamentals and his blog for letting readers understand how it works) there was always something and some of your plans was changed and performance of your db went down right after upgrade.

For plan stability users tried using hints which needs maintenance, high level of understanding and code changes or outlines which were a bit complex to use.
To sort this plan stability complexity, Guys at Oracle first came up with a solution called sql profiles in 10G but but it was cost option and not everybody had chance to use it.
Oracle needed a license free, easy to use and solid solution for plan stability and with 11G Oracle finally managed to give these options to users by addition of new “sql plan baselines” feature.

I recently did a major DB upgrade from 10.2.4 to 11.2.0.1 and despite all the performance problems we had during the testing phase, apart from 2 issues which are sorted in 10 minutes time we did not have any issue after the actual production upgrade. How did we managed to do this ?

1- we planned the upgrade in detail
2- we tested good and long enough
3- we caught all the problems during the test phase and sorted them all by plan baselines and with some of the techniques I will explain later.
4- We react quick enough on the first day after the upgrade. By help of plan baselines , We did not waste seconds to sort the problem.

Are sql plan baselines perfect solution ? Like all the software it is not perfect sometimes can get very hard to track down, but it is good enough to give what your end users want at first place, which is “same level of performance they experienced 1 day ago”. As I said before, they don’t care what you did that Saturday so you should put “giving them same level of performance they had and paid before upgrade” to the top of your priority list,

Using plan baselines looks like a bit like cheating for a DBA who likes to understand what the problem is, however with magical parameter optimizer_features_enable, (which is like a time machine that lets you have a journey between versions) you will always have time to understand what the problem was, so instead of becoming obsessive about sorting/understanding the actual problem at first place, it is better first creating the plan baseline to stabilize the system then start investigating.
‘
By this way you could also avoid pressure of managers hanging around your des trying to understand “what the hell is going on”. (they won’t understand what is going on when you tell them anyway, so why bother explaining. ) the only thing they care is the number of calls reporting performance issues from end-users and big bosses. if the number of calls goes down they will be happy so give them what they want to see/hear. A stabile, upgraded database.

As you already understood from the topic and reading nothing technical in the post , this post was an intruduction to plan stability through upgrade series I am starting.
During the series, I will try to explain how you can avoid upgrade related performance problems to make your dream upgrade and become hero :)

Current outline is like below. When I have time I will write them down one by one and link them here.

1-Introduction2-Building the test3-Why is my plan changed?-bugfixes : how you can find which bug fix may caused your plan change4-Why is my plan changed?-new optimizer parameters :how you can find which parameter change/addition may caused your plan change5-Why is my plan changed?–extra nested loop : what is the new nested loop step you will see after 11G upgrade6-Why is my plan changed?-Auto adjusted Dynamic Sampling : I will try to explain how auto adjusted dynamic sampling can effect stability of your parallel queries7-Why is my plan changed?-adaptive cursor sharing : I will talk a “little” about adaptive cursor sharing which may cause different plans for binded sqls after upgrade8-Opening plan change case on MOS-SQLT : I will try to save the time you spend with Oracle Support when you raise a call for post upgrade performance degredation9-Plan Baselines-Introduction : What are plan baselines they how they work10-Plan Baselines-Using SQL Tuning sets : How to create plan baselines from tuning set ?11-Plan Baselines-Using SQL Cache : How to create plan baselines from SQL Cache ?12-Plan Baselines-Moving Baselines : How to move your plan baselines between database ?13-Plan Baselines-Faking Baselines : How to fake the plan baseline?s14-Plan Baselines-Capturing Baselines : How to capture baselines?15-Plan Baselines-Management : How to manage your baselines?16-Testing Statistics with Pending Stats : I’ll go through how you can use pending statistics during upgrades17-Comparing Statistics : I’ll explain comparing the statistics18-Cardinality Feedback Feature : I’ll go through new built in cardinality feedback feature which may cause problems19-Where is the sqlid of active session ?: I’ll show you how you can find what your sql_id when it is null20-Testing hintless database : I’ll explain how you can get rid of hints21-Upgrade Day/Week : What needs to be ready for smooth upgrade ?22-Before after analysis-mining problems : How you can spot possible problems comparing tuning sets23-Before after analysis-graphs to sell : Using perfsheet to sell your work24-Further Reading : Compilation of References I used during series and some helpfull links25-Tools used : Index of the tools I used during series

I hope, I can write at least 3 post each week and finish series in 8-6 weeks. I nearly have all the material ready, which need a bit more polishing, I hope I won’t be to exhausted by lovely wordpress gui which is very annoying when it comes formatting.

Like this:

LikeLoading...

Related

I’m currently waiting to shepherd an application through a migration from 9i to 11gR2.

One of the things I’m pushing heavily (and encountering FUD resistance from management) is SQL Plan baselines to a) capture some of the 9i plans (optimizer_features_enable) and b) to ignore those single use_nl hint in those 8 table join SQL statements using optimizer_ignore_hints.

I’ve got a meeting about all this in ten minutes so if you could get all the articles done by then, I’d appreciate it ;)

a-) I would capture the plans just to be on safe side and if they cause problem on 11G then I would fix their plans
b-) this needs more testing. advised by Maria Colgan from Oracle but can have wider effects. To narrow it down maybe I would do logon trigger for services users using the sql or capture their plans during this parameter on and again fix those plans at least I feel more secure. optimizer_ignore_hints is good but developers are very keen on their hints and their knowledge and if something blows then you will be the one on fire. hard choice :)

14 over 1800 unique sqlid and 10 of them fixed 4 of them not fixed. Fixed ones are higher cost ones thats why they are fixed. Even It can bee seen like we did not used baselines heavily, it worked for us. We found 11.2.0.1 fairly good for plan generation apart from the cost plan changed caused by extra nested loop addition without changing the cost we had probably 359 plan change and very small amount of plan change for bad.plus cardinality feedback related instability on plans

Now that you have three solid tips that can help
you make the most of them, you should implement
them immediately so you can start seeing increased traffic to your blog.
In fact using plugins is one of the easiest ways to customize the blog.