Plan stability through Upgrade to 11G-Introduction

What are the common reasons to upgrade a database ?

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-Introduction
2-Building the test
3-Why is my plan changed?-bugfixes : how you can find which bug fix may caused your plan change
4-Why is my plan changed?-new optimizer parameters : how you can find which parameter change/addition may caused your plan change
5-Why is my plan changed?–extra nested loop : what is the new nested loop step you will see after 11G upgrade
6-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 queries
7-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 upgrade
8-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 degredation
9-Plan Baselines-Introduction : What are plan baselines they how they work
10-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?s
14-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 upgrades
17-Comparing Statistics : I’ll explain comparing the statistics
18-Cardinality Feedback Feature : I’ll go through new built in cardinality feedback feature which may cause problems
19-Where is the sqlid of active session ? : I’ll show you how you can find what your sql_id when it is null
20-Testing hintless database : I’ll explain how you can get rid of hints
21-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 sets
23-Before after analysis-graphs to sell : Using perfsheet to sell your work
24-Further Reading : Compilation of References I used during series and some helpfull links
25-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.

Advertisement