A new DBMS_SPM function has been backported to Oracle Database 19c from Oracle Database 23c. It’s called ADD_VERIFIED_SQL_PLAN_BASELINE.
This allows you to automate the task of locating previous execution plans for a SQL statement, establish which is the best, and create a SQL plan baseline to enforce it. It’s most effective if you’re using the automatic SQL tuning set, but it will look in AWR and the cursor cache too. The idea is that if you have a SQL performance regression, you can execute the new function and it will verify alternative plans using SPM evolve’s test execution. If a better plan is found, it will be enforced using a SQL plan baseline – all in one step.
For Oracle Database 19c you can get one-offs for many RUs via patch number 34534504, and it is now included in Oracle Database 19c RU19.22 and Oracle Autonomous Database 19c (note that some versions of Firefox break when you click the patch number link to MOS).
It is documented in the 23c PL/SQL Packages and Types Reference Guide, but it will appear in the 19c docs soon.
In SQL Plus, you can use it like this:
set long 100000
set tab off
set linesize 200
column report format a150
var report clob
exec :report := dbms_spm.add_verified_sql_plan_baseline('<your_sql_id>');
select :report report from dual;
This is, of course, very much a “one-shot-does-everything” approach, but if you are a fan of fine adjustments (or perhaps you want to generate a report without implementing the recommended SQL plan baselines), check out this post.