Oracle Database 11g -- How to Bind an Execution Plan for an SQL Statement

Problem

You want to fix an execution plan for a given SQL statement. The plan to be used from the AWR

Solution

1) Identify sql id and the hash value of the execution plan you want to fix for the given sql id. AWR SQL Report is useful to accomplish this task once you have got the sql id

2) Optionally, verify the current sql plan baselines:

SELECT * FROM dba_sql_plan_baselines;

3)  Use the below script to fix the execution plan for the sql statement:

SET SERVEROUTPUT ON
DECLARE
    cur          sys_refcursor;
    loaded_plans pls_integer;
BEGIN
    DBMS_SQLTUNE.CREATE_SQLSET(
        sqlset_name  => '<sql_tuning_set_name>',
        description  => 'Some SQL Tuning set description');
     
     OPEN cur FOR
        select value(p) from table (
            DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
              begin_snap        => <begin_snap>,
              end_snap          => <end_snap>,
              basic_filter      => q'{ sql_id='<sql_id>' and plan_hash_value=<plan_hash_value> }')
            ) p;   

    DBMS_SQLTUNE.LOAD_SQLSET(
        sqlset_name     => '<sql_tuning_set_name>',
        populate_cursor => cur);
             
    loaded_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
        sqlset_name      => '<sql_tuning_set_name>',
        basic_filter     => q'{ sql_id='<sql_id>' and plan_hash_value=<plan_hash_value> }',
        fixed            => 'YES',
        enabled          => 'YES');
        
    dbms_output.put_line('Plans loaded: ' || loaded_plans);
        
    close cur;
END;
/

4) Verify the loaded sql plan baseline:

SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
        sql_handle=>'<sql handle>',
        format=>'basic'));

5) To match a baseline to a SQL cursor use:

DBA_SQL_PLAN_BASELINES.SIGNATURE = V$SQL.EXACT_MATCHING_SIGNATURE