答案是取决于你在profile中使用的hint组合是什么。使用完整的outline data内容就可以固定；使用不完整的，带有启发性的hint，就不会固定。
使用 coe_sql_load_profile.sql 脚本会固定你的SQL的执行计划（因为它写入profile中的内容的一个sql完整的outline data执行计划执行路径），
除非遇到一些特殊情况，如索引不可用、并行资源不够、数据库相关参数变更等。
可以使用 coe_load_sql_profile.sql 脚本直接固定执行计划，该脚本也可以实现直接把 sql profile 直接迁移到其它库中。
SQLT工具包含一个脚本，SQLT可以在Doc ID 215187.1 中下载。
很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定SQL执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间SQL执行计划的固定。最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。
需要注意的是，该脚本不能以SYS用户执行，否则会报如下的错误：
DECLARE
*
ERROR at line 1:
ORA-19381: cannot create staging table in SYS schema
ORA-06512: at "SYS.DBMS_SMB", line 366
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SMB", line 283
ORA-06512: at "SYS.DBMS_SQLTUNE", line 9138
ORA-06512: at line 64
1.1. 建立测试表和数据
conn nick/nick
select * from v$version;
drop table test purge;
create table nick.test as select * from dba_objects;
create index nick.idx_test_01 on nick.test(object_id);
收集统计信息
exec dbms_stats.gather_table_stats('nick','test',cascade=>true);
update nick.test set object_id=10 where object_id>10;
commit;
select object_id ,count(1) from nick.test group by object_id;
OBJECT_ID COUNT(1)
---------- ----------
6 1
7 1
2 1
8 1
10 77215
5 1
4 1
2
3 1
9 1
可以看到object_id=10的值有 77215 记录，远远大于该列总记录数的5%
1.2. 执行查询语句
执行原有的查询语句，查看执行计划发现走索引，实际上这时表中大部分行的OBJECT_ID都已经被更新为10，所以走索引是不合理的。
至于为什么优化器没有选择正确的执行计划是因为执行完"update scott.test set object_id=10 where object_id>10;"后会导致统计信息过期，
而又没有重新收集统计信息，优化器并不知道数据发生了变化（未重新收集统计信息），所以仍然走了索引。
不显示记录集，只显示 expain+statistics 与 set autotrace traceonly 等价
set autot traceonly explain stat
set line 122
select * from nick.test where object_id=10;
77215 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3569288492
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 124 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11865 consistent gets
0 physical reads
0 redo size
4692652 bytes sent via SQL*Net to client
57021 bytes received via SQL*Net from client
5149 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77215 rows processed
********************* add hint *********************
select /*+ full(test)*/* from nick.test where object_id=10;
77215 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 | 393 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 124 | 393 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6460 consistent gets
0 physical reads
0 redo size
4692559 bytes sent via SQL*Net to client
57037 bytes received via SQL*Net from client
5149 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77215 rows processed
1.3. 查询上面两个语句的SQL_ID、PLAN_HASH_VALUE
set autot off
col sql_text format a70
select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select * from nick.test where object_id=10%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
---------------------------------------------------------------------- ------------- ---------------
select * from nick.test where object_id=10 1prn3gvxxnt96 3569288492
select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ full(test)*/* from nick.test where object_id=10%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
---------------------------------------------------------------------- ------------- ---------------
select /*+ full(test)*/* from nick.test where object_id=10 1xt8s2shn9427 1357081020
1prn3gvxxnt96 - 这是原始语句的SQL_ID
1xt8s2shn9427 - 这是使用hint的SQL_ID
1357081020 - 这是需要替换的 plan hash value.
1.4. 把coe_load_sql_profile.sql放在/home/oracle/下
1.5. 使用coe_load_sql_profile.sql脚本
这两个计划都需要在缓存或AWR中
需要以具有DBA权限的用户身份连接，例如SYSTEM
sqlplus system/oracle
@/home/oracle/coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 1prn3gvxxnt96
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 1xt8s2shn9427
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
1357081020 .064
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 1357081020
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "1prn3gvxxnt96"
MODIFIED_SQL_ID: "1xt8s2shn9427"
PLAN_HASH_VALUE: "1357081020"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>
SQL>SET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE('19.1.0')
0004 DB_VERSION('19.1.0')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$1")
0007 FULL(@"SEL$1" "TEST"@"SEL$1")
0008 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_1PRN3GVXXNT96"
staging table "STGTAB_SQLPROF_1PRN3GVXXNT96" did not exist
creating staging table "STGTAB_SQLPROF_1PRN3GVXXNT96"
packaging new sql profile into staging table "STGTAB_SQLPROF_1PRN3GVXXNT96"
PROFILE_NAME
------------------------------
1PRN3GVXXNT96_1357081020
SQL>REM
SQL>REM SQL Profile
SQL>REM ~~~~~~~~~~~
SQL>REM
SQL>SELECT signature, name, category, type, status
2 FROM dba_sql_profiles WHERE name = :name;
SIGNATURE NAME CATEGORY TYPE STATUS
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --------
7446245191398256931 1PRN3GVXXNT96_1357081020 DEFAULT MANUAL ENABLED
SQL>SELECT description
2 FROM dba_sql_profiles WHERE name = :name;
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:1PRN3GVXXNT96 MODIFIED:1XT8S2SHN9427 PHV:1357081020 SIGNATURE:7446245191398256931 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
****************************************************************************
* Enter SYSTEM password to export staging table STGTAB_SQLPROF_1prn3gvxxnt96
****************************************************************************
Export: Release 19.0.0.0.0 - Production on Fri Jul 7 11:27:32 2023
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table STGTAB_SQLPROF_1PRN3GVXXNT96 1 rows exported
Export terminated successfully without warnings.
If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:
imp SYSTEM file=STGTAB_SQLPROF_1prn3gvxxnt96.dmp tables=STGTAB_SQLPROF_1prn3gvxxnt96 ignore=Y
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '1PRN3GVXXNT96_1357081020',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_1prn3gvxxnt96',
staging_schema_owner => 'SYSTEM' );
END;
/
adding: coe_load_sql_profile_1prn3gvxxnt96.log (deflated 76%)
adding: STGTAB_SQLPROF_1prn3gvxxnt96.dmp (deflated 86%)
adding: coe_load_sql_profile.log (deflated 62%)
deleting: coe_load_sql_profile.log
coe_load_sql_profile completed.
1.6. 查看产生的sql profile，此时原语句在不加hint的情况下也走全表扫了
set line 122
col name for a25
col CATEGORY for a10
col sql_text format a70
select name,CATEGORY,SQL_TEXT from dba_sql_profiles;
NAME CATEGORY SQL_TEXT
------------------------- ---------- ----------------------------------------------------------------------
1PRN3GVXXNT96_1357081020 DEFAULT select * from nick.test where object_id=10
set line 155
col name for a25
col sql_text format a66
col hints format a40
SELECT b.name,
to_char(d.sql_text) sql_text,
extractvalue(value(h), '.') as hints
FROM dba_sql_profiles d,
SYS.SQLOBJ$DATA A,
SYS.SQLOBJ$ B,
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA), '/outline_data/hint'))) h
where a.signature = b.signature
and a.category = b.category
and a.obj_type = b.obj_type
and a.plan_id = b.plan_id
and D.name = '1PRN3GVXXNT96_1357081020';
NAME SQL_TEXT HINTS
------------------------- ---------------------------------------------------------------------- --------------------------------------
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 BEGIN_OUTLINE_DATA
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 IGNORE_OPTIM_EMBEDDED_HINTS
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 OPTIMIZER_FEATURES_ENABLE('19.1.0')
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 DB_VERSION('19.1.0')
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 ALL_ROWS
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 OUTLINE_LEAF(@"SEL$1")
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 FULL(@"SEL$1" "TEST"@"SEL$1")
1PRN3GVXXNT96_1357081020 select * from nick.test where object_id=10 END_OUTLINE_DATA
1.7 验证SQL Profile是否生效
set autot traceonly explain stat
select * from nick.test where object_id=10;
77215 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 | 393 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 124 | 393 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- SQL profile "1PRN3GVXXNT96_1357081020" used for this statement
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
6463 consistent gets
1 physical reads
0 redo size
4692559 bytes sent via SQL*Net to client
57021 bytes received via SQL*Net from client
5149 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77215 rows processed
1.8 删除sql profile方法
exec dbms_sqltune.drop_sql_profile(name => '&sql_profile');
begin
dbms_sqltune.drop_sql_profile(name => '&sql_profile');
end;
/「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
