使用coe_load_sql_profile脚本通过sql_profile绑定执行计划

本文链接：https://blog.csdn.net/royzhang7/article/details/119924350

##使用情景：
在部分spm不能使用的情况下，如sql中含有远程表，spm绑定不生效。
使用sql_profile绑定内存中已有的正确执行计划。

##使用方式：
1、上传脚本 coe_load_sql_profile.sql

https://download.csdn.net/download/royzhang7/21387886

2、登录数据库，要用有DBA权限的非sys用户（system或其他DBA权限用户）

3、执行
@coe_load_sql_profile.sql

4、输入参数(这里如果是内存中正确的执行计划，两个sql_id输入同一个即可）
ORIGINAL_SQL_ID ：329d885bxvrcr
MODIFIED_SQL_ID ：329d885bxvrcr
PLAN_HASH_VALUE ：2872589290

5、再次输入登录用户密码（system或其他DBA权限用户）

+++++++++++++++++
提示：coe_load_sql_profile.sql 在SQLT的目录下面
All About the SQLT Diagnostic Tool ( Doc ID 215187.1 )
---------------
目录：sqlt_10g_11g_12c_18c_19c_5th_June_2020\sqlt\utl\coe_load_sql_profile.sql

##实施步骤
---------------
B: Using coe_load_sql_profile.sql
Both plans need to be in cache or in AWR
Connect as user with DBA privilege, for example SYSTEM
NOTES:

Do not connect as SYS as the staging table cannot be created in SYS schema and you will receive an error: ORA-19381: cannot create staging table in SYS schema

Licensing: The coe_load_sql_profile.sql script calls DBMS_SQLTUNE (which is a Licensed Command-Line API) requiring the Tuning Pack license (for which Oracle Diagnostics Pack is a prerequisite).
connect system/password
SQL> @coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 329d885bxvrcr

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 329d885bxvrcr

PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
2872589290 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 2872589290

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "329d885bxvrcr"
PLAN_HASH_VALUE: "2872589290"

.
.
.

ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5Y PHV:2872589290 SIGNATURE:15822026218863957422 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;

****************************************************************************
* Enter password to export staging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************

Export: Release 11.2.0.3.0 - Production on Sun Mar 11 14:45:47 2012

Password:password
.
.
.

coe_load_sql_profile completed.
Run original query

##结果查询
再次执行sql，查询对应sql_id的sql_proflie字段是否有值，有说明绑定关联成功

SELECT sql_profile FROM v$sql WHERE sql_id='329d885bxvrcr'