----------------------------------------------------------------------------------------
--
-- File name:   lock_STA_profile.sql
--
-- Purpose:     Create new SQL Profile to "lock" plan generated by STA Profile - removes opt_estimate hints
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
--
--              child_no: the child_no of the statement from v$sql
--
--              new_profile_name: the name of the new profile to be generated
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: This script pulls the hints associated with a SQL statement and creates 
--              a profile with them. It expects a SQL Tuning Advisor (STA) Profile to exist
--              on the specified statement. It changes the category of the existing SQL
--              Profile to SAVED (this can fail if there is already another profile for
--              the statement in the SAVED category. 
--
--              Bottom line, this script is intended to eliminate statistics modifying hints 
--              generated by the SQL Tuning Advisor (opt_estimate, table_stats, column_stats,
--              index_stats, etc...) Since these hints only apply to a single point in time
--              (in most cases). They tend to "sour" over time.
--
--              Based on a script by Randolf Giest.
--
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept child_no -
       prompt 'Enter value for child_no (0): ' -
       default '0'
accept new_profile_name -
       prompt 'Enter value for new_profile_name (PROF_sqlid_planhash): ' -
       default 'X0X0X0X0'
accept force_matching -
       prompt 'Enter value for force_matching (FALSE): ' -
       default 'false'

-- @rg_sqlprof1 '&&sql_id' &&child_no 'LOCKIT' '&force_matching'

set feedback off
set sqlblanklines on

declare
STA_Profile_Name varchar2(30);
l_new_profile_name varchar2(30);
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id = '&&sql_id'
and child_number = &&child_no
and other_xml is not null
)
) d;

select
sql_fulltext, sql_profile,
decode('&&new_profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&new_profile_name')
into
cl_sql_text, STA_Profile_Name, l_new_profile_name
from
v$sql
where
sql_id = '&&sql_id'
and child_number = &&child_no;


dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => 'LOCKIT',
name => l_new_profile_name,
force_match => &&force_matching,
replace => true
);

dbms_sqltune.alter_sql_profile(STA_Profile_Name, 'CATEGORY', 'SAVED');
dbms_sqltune.alter_sql_profile(l_new_profile_name, 'CATEGORY', 'DEFAULT');

  dbms_output.put_line(' ');

exception
when NO_DATA_FOUND then
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
  dbms_output.put_line(' ');

end;
/
undef sql_id
undef child_no
undef force_matching
set feedback on