Many years ago, I used to work as an operational DBA, and I remember colleagues asking the following:
My query runs very slow. The other day it all ran fine, but now it’s hanging. What’s going on? Fix it!
They might even start the occasional rant about relational doesn’t scale and all the other blah blah blah.
Sounds familiar? What if you could solve the problem by just running:
EXEC DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE('<sql_id');
The query runs at warp speed again, and the users are happy.
In Oracle Database 19.22.0, that’s possible.
What’s Going On
The procedure uses SQL Plan Management: the best solution to ensure plan stability.
It will:
- Search all your SQL tuning sets, including automatic SQL tuning sets, AWR, and cursor cache to find other plans for that SQL.
- Test each of the plans to find the best one.
- Create a SQL plan baseline with the best plan.
During the next execution, the database will use the best plan.
Problem solved!
SQL Plan Management
I’m a huge fan of SQL Plan Management. It is one of the most underrated features in Oracle Database.
What is your biggest fear when you upgrade? Often, the answer is changing plans. SQL Plan Management effectively solves that problem, and we recommend it as part of our performance stability prescription.
Afterwords
In fact, I’ve never been a performance expert. When I was tasked with a performance issue, I knew it would take a lot of time.
Over time, I found a better way of solving these issues. I would turn my chair and look directly into the wrinkled eyes of Frank, my trusted grey-haired colleague.
Using my cutest little puppy face and soft voice, young me would ask for advice and smoothly hand over the task to him.
But not everyone has a Frank in your office, so DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE
is very useful.
Experience and grey hair rock!
Further Reading
- Blog post, What is DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE?
- Blog post, Repairing SQL Performance Regression with SQL Plan Management
- Technical brief, SQL Plan Management in Oracle Database 19c