How to create sql profile

 @Sqlh
 
Select sqlid from @longops and get plan hash value from @sqlh or @sqlver for particular sqlid
 
Not in red sqlid after that Plan Hash value change sqlid and plan hash value bellow pl/sql procedure
 
Copy it in notepad and change sqlid and plan hash value
 
declare
     ar_hint_table    sys.dbms_debug_vc2coll;
     ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
     cl_sql_text      clob;
     i                pls_integer;
   begin
     with a as (
                select
                     rownum as r_no
                     , a.*
                From table( dbms_xplan.display_awr('913uapvkwtmav',   3122673927, null, 'OUTLINE' )
              ) a
     ),
     b as (
     select
              min(r_no) as start_r_no
     from
              a
     where
              a.plan_table_output = 'Outline Data'
     ),
     c as (
     select
              min(r_no) as end_r_no
     from
              a
           , b
    where
             a.r_no > b.start_r_no
    and      a.plan_table_output = '  */'
    ),
    d as (
    select
             instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
     from
              a
            , b
     where
              r_no = b.start_r_no + 4
     )
     select
              substr(a.plan_table_output, d.start_col) as outline_hints
     bulk collect
     into
              ar_hint_table
     from
              a
            , b
            , c
            , d
     where
         a.r_no >= b.start_r_no + 4
    54    and      a.r_no <= c.end_r_no - 1
     order by
              a.r_no;
 
     select
              sql_text
     into
              cl_sql_text
     from
              sys.dba_hist_sqltext
     where
              sql_id = '913uapvkwtmav';
 
     -- this is only required
     -- to concatenate hints
     -- splitted across several lines
     -- and could be done in SQL, too
     i := ar_hint_table.first;
     while i is not null
     loop
       if ar_hint_table.exists(i + 1) then
         if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
           ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
           ar_hint_table.delete(i + 1);
         end if;
       end if;
       i := ar_hint_table.next(i);
     end loop;
 
     i := ar_hint_table.first;
     while i is not null
     loop
       ar_profile_hints.extend;
       ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
       i := ar_hint_table.next(i);
     end loop;
 
     dbms_sqltune.import_sql_profile(
       sql_text    => cl_sql_text
     , profile     => ar_profile_hints
     , name        => 'SQLP_913uapvkwtmav_3122673927'
     -- use force_match => true
     -- to use CURSOR_SHARING=SIMILAR
 -- behaviour, i.e. match even with
    98    -- differing literals
     , force_match => false
   );
  End;
 
  /
 
 
 
Check sql profile is create or not
 
@sql_profiles          Press Enter
Ask profile name     if don’t know sql profile name Press Enter it will display all prifile
 
 
After creating profile Gather stats of all tables which is used in that sqlid
 
EXEC DBMS_STATS.GATHER_TABLE_STATS('OLAP', '&table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 32, cascade => TRUE);
 
SQL> !cat sql_profiles.sql
 
set pages 300 lines 300;
col name for a40 ;
col created for a50 ;
col last_modified for a50 ;
 
select name , CREATED,LAST_MODIFIED,STATUS from dba_sql_profiles where name like '%&name%'
order by CREATED;
 
 
 
SQL>
SQL>
SQL> !cat disable_profile.sql
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>'&Profile_name',attribute_name =>'STATUS',value => 'DISABLED');
 
 
 
SQL>
SQL>
SQL> !cat enable_profile.sql
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>'&Profile_name',attribute_name =>'STATUS',value => 'ENABLED');
 
 
SQL>
SQL>
SQL> !cat drop_profile.sql
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('&PROFILE_NAME');
 
SQL>
 
x
x