We recently upgraded some databases to 12c. They were big, RAC’d, and due to their exposure to the world, were extremely performance sensitive. I’ve found that no matter how much you test and test and test and test there will always be some SQL that the CBO will just decide to do it’s own thing. This is especially true when you move to any new release. So, being able to quickly diagnose and react to regressed SQL is critical to a successful upgrade strategy.

I’d read some whitepapers and presentations that discussed using Real Application Testing and SQL Performance Analyzer (SPA) for 12c upgrades, but this didn’t really address the problem I was trying to solve. What do you do when something happens that wasn’t caught in testing?

Enter SQL Plan Management. Nigel Bayliss’s post¹ “Upgrade to Oracle Database 12c and Avoid Query Regression” was more what I was looking for. The idea is that you capture SQL Plan Baselines for all the SQL that you care about and hold dear. I’ve used SQL Plan Baselines several times in the past, but always to address a single SQL that wasn’t able to be tuned (someone else’s application code). I’ve also Health Checked databases where optimizer_capture_sql_plan_baselines had been set to true and some have had hundreds of thousands of available baselines. But, this approach is something completely different.

I spoke with Mauro Pagnano and Carlos Sierra about doing this for our upgrades and they were both reluctant to just create all these 11g SQL Plan Baselines. First, these databases have over 200K cursors, so that is a lot of baselines that would need to be created in an otherwise happy database. Secondly, 11g SQL Baselines aren’t necessarily guaranteed to work in 12c. Third, there are bugs that would need to be patched. Before, I’d lost all hope, they suggested creating SQL Tuning Sets instead. These are safer to create, easy to export, and with them you can create 12c SQL Plan Baselines with the same execution plans from 11g.

My only problem was that I’d not really used SQL Tuning Sets outside of SPA before. And that was ages ago. I knew they were sometimes created when running Advisors (I’m not really an advisor kind of a guy) or when running SQLT. But, never intentionally!

Enter Carlos’s SPM scripts. They are a treasure trove of scripts for creating SQL Tuning Sets (STS) for cursors that are either in AWR or in the cursor cache, packing them up, exporting them, importing, unpacking, and creating SQL Plan Baselines from an STS. Thank you Carlos!

Over the next few posts I’ll cover the method we used to capture our statements of interest, determine when a SQL Plan Baseline was required, create a baselines, and verify that it is being used.