Oracle Database 19c -- How to Bind an Execution Plan from the AWR for an SQL Statement

 Problem

You want to fix an execution plan for a given SQL statement

Solution

First identify the execution plan and ensure the plan is available in the AWR

Identify the begin and end snapshots to use to fetch the plan from

Use the below statement to load the execution plan into SQL Plan Baseline:

set serveroutput on size unlimited
begin
    dbms_output.put_line(
        DBMS_SPM.LOAD_PLANS_FROM_AWR(
            begin_snap   => 43510,
            end_snap     => 45237,
            basic_filter => q'{sql_id='<sql_id>' and plan_hash_value=<plan_hash_value>}',
            fixed        => 'YES',
            enabled      =>'YES')
    );
end;

Verify with:

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES;

To verify what is the sql_id that the baseline created for, use:

select DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id, sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where plan_name = '<some plan_name>';

Optionally, for the baselines to take effect, kill the cursors, makings queries to get reparsed:

select address, hash_value 
from v$sqlarea 
where sql_id in ('<sql_id>'); 

exec dbms_shared_pool.purge('<address>, <hash_value>','C');