sqlt

https://www.sohu.com/a/397946746_411876?trans=000014_bdss_dkmwzacjP3p:CP=

https://blog.csdn.net/qq_41490913/article/details/105002574

https://developer.aliyun.com/article/985143

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1677588.1

SQLT快速绑定执行计划

SQL PROFILE可以使用SQLT工具快速绑定，SQL PROFILE就是对SQL增加了一系列HINTS，好处是不需要改写SQL，可以在数据库里直接管理。

对于COE工具SQL PROFILE绑定有两类：

直接绑定：针对执行计划经常突变的，历史中有好的执行计划，当前走的执行计划差，直接绑定即可。
替换绑定：针对执行计划一直较差，没有好的执行计划作为参考，可通过添加hints让其走好的执行计划，然后通过coe工具手动修改文件或coe_load_sql_profile或者编写存储过程绑定到好的执行计划上。
注意：如果SQL没有绑定变量，则通过coe_xfr_sql_profile生成的文件需要修改force_match=>true,手动编写存储过程或者coe_load_sql_profile做替换绑定的也需要修改force_match=>true,以让所有SQL结构相同（字面量条件不同）的SQL都绑定上好的执行计划。

下面分别说说这两种绑定方式：
1）使用coe_xfr_sql_profile脚本直接绑定
SQL> @./sqlt/utl/coe_xfr_sql_profile.sql

2）使用coe_load_sql_profile做替换绑定
SQL> @./sqlt/utl/coe_load_sql_profile.sql

不同sqlid之间替换执行计划，原sql_id:74mj7z7rgg6v9 新sql_id:0z3jnsu3ks2dc plan_hash_value:2211254330

imp SYSTEM/<system_passwordd>@UAT file=STGTAB_SQLPROF_74mj7z7rgg6v9.dmp tables=STGTAB_SQLPROF_74mj7z7rgg6v9 ignore=Y

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '74MJ7Z7RGG6V9_2211254330',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_74mj7z7rgg6v9',
staging_schema_owner => 'SYSTEM' );
END;
/

updating: coe_load_sql_profile_74mj7z7rgg6v9.log (deflated 85%)
updating: STGTAB_SQLPROF_74mj7z7rgg6v9.dmp (deflated 91%)
  adding: coe_load_sql_profile.log (deflated 62%)

force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

force_match的默认值为false，表示只有在sql文本完全一致的情况下才会应用sql_profile，这种情况下只要目标sql的sql文本发生一点改动，原来的profile将失去作用
force_match的改为true，就相当于目标sql的where条件中的具体的输入值用绑定变量替换了
启用force_match=>true选项,需要先删除原来的profile

NAME	DESCRIPTION	TYPE	STATUS	FORCE_MATCHING
coe_0z3jnsu3ks2dc_2211254330	coe 0z3jnsu3ks2dc 2211254330 14921512880661520025 14513873519789741543	MANUAL	ENABLED	YES
74MJ7Z7RGG6V9_2211254330	ORIGINAL:74MJ7Z7RGG6V9 MODIFIED:0Z3JNSU3KS2DC PHV:2211254330 SIGNATURE:5419765659455457243 CREATED BY COE_LOAD_SQL_PROFILE.SQL	MANUAL	ENABLED	NO

74MJ7Z7RGG6V9_2211254330
sql_profile_74mj7z7rgg6v9

exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'sql_profile_74mj7z7rgg6v9'); 

ORIGINAL:74MJ7Z7RGG6V9 MODIFIED:0Z3JNSU3KS2DC PHV:2211254330 SIGNATURE:5419765659455457243 CREATED BY COE_LOAD_SQL_PROFILE.SQL

select sql_id,sql_profile,plan_hash_value from gv$sql where sql_id='0z3jnsu3ks2dc';

select * from dba_sql_profiles where name like '%0z3jnsu3ks2dc%';

select sql_id,sql_profile,plan_hash_value from gv$sql where sql_id='74mj7z7rgg6v9';

select * from dba_sql_profiles where name like '%74mj7z7rgg6v9%';

declare
  v_hint     sys.sqlprof_attr;
  v_sql_text clob;
begin
  select sql_fulltext
    into v_sql_text
    from gv$sql
   where sql_id = '74mj7z7rgg6v9'
     and rownum = 1;
  select extractvalue(value(d), '/hint') as outline_hints bulk collect into v_hint
  from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval
from gv$sql_plan where sql_id ='0z3jnsu3ks2dc' and plan_hash_value = 2211254330
 and other_xml is not null)) d;
  dbms_sqltune.import_sql_profile(v_sql_text,
                                  v_hint,
                                  'sql_profile_74mj7z7rgg6v91',
                                  force_match                => true,
                                  replace                    => true);
end;

declare
  v_hint     sys.sqlprof_attr;
  v_sql_text clob;
begin
  select sql_fulltext
    into v_sql_text
    from gv$sql
   where sql_id = '74mj7z7rgg6v9'
     and rownum = 1;
  select extractvalue(value(d), '/hint') as outline_hints bulk collect into v_hint
  from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval
from gv$sql_plan where sql_id ='f93tbqjhjqhz5' and plan_hash_value = 2211254330
 and other_xml is not null)) d;
  dbms_sqltune.import_sql_profile(v_sql_text,
                                  v_hint,
                                  'sql_profile_74mj7z7rgg6v91',
                                  force_match                => true,
                                  replace                    => true);
end;

2494034627