Inject hints using manual SQL profiles

This process can be used to inject individual hints into sql statements without using the tuning advisor or modifying code. 

I pull the sql text from the sql area for matching. 

Combining  dba_hist_sqltext and v$sqlarea to reduce the chances of finding the sql. 

declare
  v_sql_text clob;
  v_sql_id varchar2(30):= '<SQLID>';
begin 
 select  sql_fulltext  
    into  v_sql_text
    from ( 
      select  sql_fulltext  
      from  v$sqlarea
      where  sql_id = v_sql_id
      union all 
      select  sql_text  
      from  DBA_HIST_SQLTEXT
      where  sql_id = v_sql_id 
    ) where rownum <= 1 ;
end; 
/

Put together a list of hints.

  v_hint_1 varchar2(300):= 'full(a) '; 
  v_hint_2 varchar2(300):= 'use_nl( a b )'; 
  v_hint_3 varchar2(300):= 'index(b)'; 

Choose a meaningful name for the profile. 

  v_profile_name := 'PROFILE_'||v_sql_id||'_MANUAL';

Code to create the profile, pass in each hint separately. 

  dbms_sqltune.import_sql_profile(
    sql_text => v_sql_text, 
    profile => sqlprof_attr(v_hint_1, v_hint_2, v_hint_3 ),
    name => v_profile_name,
    force_match => true
  );

Put it all together

set serveroutput on size unlimited; 
declare 
  v_profile_name varchar2(30);
  v_sql_text clob;

  -- hints
  v_hint_1 varchar2(300):= 'full(a) '; 
  v_hint_2 varchar2(300):= 'use_nl( a b )'; 
  v_hint_3 varchar2(300):= 'index(b)'; 
begin 

  -- make sure the profile does not exist. 
  begin 
    DBMS_SQLTUNE.DROP_SQL_PROFILE ( 'PROFILE_'||v_sql_id||'_MANUAL' ); 
    exception when others then null; 
  end; 

  -- get the sql text
    select  sql_fulltext  
    into  v_sql_text
    from ( 
      select  sql_fulltext  
      from  v$sqlarea
      where  sql_id = v_sql_id
      union all 
      select  sql_text  
      from  DBA_HIST_SQLTEXT
      where  sql_id = v_sql_id 
    ) where rownum <= 1 ;

  v_profile_name := 'PROFILE_'||v_sql_id||'_MANUAL';

  dbms_sqltune.import_sql_profile(
    sql_text => v_sql_text, 
    -- adjust depending on number of hints
    profile => sqlprof_attr(v_hint_1, v_hint_2, v_hint_3 ), 
    name => v_profile_name,
    force_match => true
  );
  dbms_output.put_line('Profile '||v_profile_name||' created.');
end; 
/

See this process in action here.