Oracle database queries
Those of you that made it to the demo grounds at Oracle Open World this year (it’s still just about 2015) will have had the chance to chat with the Oracle developers and throw lots of difficult questions at them! For everyone in the Optimizer development team it was a great opportunity to get lots of interaction and feedback, so thanks to all of you that took the time to come along and talk to us. We’re not all lucky enough to get a trip out to San Francisco, so I’ve been collating the main topics that came up to steer the subject matter of the next few blog posts. In this way I hope that we can all benefit from these interactions.
I can tell you right away that the number one demo ground question at OOW 2015 was …drum roll… “How can I reduce the risk of query regression when I upgrade to Oracle Database 12c?”. Well, maybe not those words exactly, but words to that effect. There is quite a lot of information out there on this topic, but people seem to struggle to find it… so we put our heads together and we realized that we should publish a 20, 000ft view of this topic with pointers down into the detail. That’s the purpose of this post and, for the sake of brevity, I’m going to make the assumption that you are upgrading your Enterprise Edition database from Oracle Database 11g Release 2 to Oracle Database 12c.
The bottom line is this: if you want to mitigate the risk of query regression when you upgrade to Oracle Database 12c, then use SQL Plan Management (SPM). This is the recommended approach for the vast majority of systems out there, where the most critical SQL statements are reusable or, in other words, they are executed more than once.
Here are a couple of common scenarios:
| Scenario 1 | You want to use all Oracle Database 12c Optimizer features right away, but you need to “repair” any regressed queries quickly and with minimum effort. |
| Scenario 2 | You’re upgrading and want to keep your “tried-and-tested”, Oracle Database 11g execution plans. Nevertheless, you do not want your application to be frozen in time: you want to evolve and use improved execution plans that are available in the new release, and you need to do this in a proven and controlled way. |
Scenario 1
This is something you’ll want to think about before your production system goes live, particularly if you have not been able to test a realistic workload on all of your production data. It’s also very relevant if you are running a benchmark or proof of concept, where the time that’s available to resolve problem queries can be pretty limited (I’m using some understatement there!).