In Oracle Autonomous AI Database, real-time SQL plan management prevents SQL performance regressions associated with execution plan changes even during upgrades. How can you tell if this is happening? This blog post explains.
Tucked away in the view, DBA_SQL_PLAN_BASELINES for Oracle Autonomous Database 19c onwards, there are new columns called FOREGROUND_LAST_VERIFIED and NOTES. They contain information on real-time SQL plan management (where plan performance issues are resolved by the foreground process). FOREGROUND_LAST_VERIFIED is a timestamp that tells you when the real-time SPM operation kicked in. The NOTES column is XML, and takes some decoding. To help you with that, I’ve added some scripts to GitHub.
A fantastic feature of Oracle AI Autonomous Database is that, when you upgrade, real-time SPM will protect you from execution plan changes that harm performance. It is fully automatic, and you don’t have to do anything (assuming you have not disabled real-time SPM!). Perhaps the most interesting metric in this context is the number of regressions prevented since upgrade. If you upgrade an autonomous database (or clone to a newer version), check out the regressions_prevented_since_upgrade.sql script. It will show you how many plans real-time SPM has rescued from a performance regression since you upgraded your database.