oracle固定执行计划2-sql profile
上篇介绍了如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。本篇将介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)。那么，这里最关键的一点是，如何来手工创建SQL Profiles?
使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。
mauanl类型的sql profile
绑定已经存在的执行计划
其实就是加hint，为了方便，其实就是拿脚本绑定，有3个脚本：
1、极简版
2、coe_xfr_sql_profile
3、coe_load_sql_profile
coe_xfr_sql_profile.sql脚本来固定sql 执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间sql执行计划的固定。
最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。
但是也分为2中情况，一是1个sql，有多个执行计划，即sql_id相同，但是plan hash value不同，拿好的plan hash value去绑定坏的plan hash value；
二是，为了生成最优的执行计划，人为给原sql加了hint，生成新的sql，生成新的执行计划，拿好的sql_id和plan hash value去绑定坏的sql_id
这3个脚本的使用前提都是有更优的执行计划存在内存中
- hint必须加正确，手动加hint绑定不容易
SQL> desc dbms_sqltune
。。
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
PROFILE SQLPROF_ATTR IN
NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
CATEGORY VARCHAR2 IN DEFAULT
VALIDATE BOOLEAN IN DEFAULT
REPLACE BOOLEAN IN DEFAULT
FORCE_MATCH BOOLEAN IN DEFAULT
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
PROFILE_XML CLOB IN
NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
CATEGORY VARCHAR2 IN DEFAULT
VALIDATE BOOLEAN IN DEFAULT
REPLACE BOOLEAN IN DEFAULT
FORCE_MATCH BOOLEAN IN DEFAULT
。。。
这个过程其名字与实际功能有所差异，其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR，这种类型其实就是VARCHAR2的集合类型(COLLECTION)：
SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS';
TEXT
----------------------------------------------------------------------------------------------------
TYPE sqlprof_attr
AS VARRAY(2000) of VARCHAR2(500)
为避免干扰，将上一篇测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：
exec dbms_stats.set_table_stats('SCOTT','T1',numrows=>5000000);
session 1:
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0177dc7534bb0000');
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0177dc7ca26c0001');
PL/SQL procedure successfully completed.
现在我们手工创建一个SQL Profile：
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)');
5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
6 v_hints,'SQLPROFILE_NAME1',force_match=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select COMP_DATA from dba_sql_profiles a, SQLOBJ$DATA b
2 where a.signature = b.signature
3 and a.name='SQLPROFILE_NAME1';
COMP_DATA
----------------------------------------------------------------------------------------------------
<outline_data><hint><![CDATA[USE_NL(T1 T2)]]></hint><hint><![CDATA[INDEX(T2)]]></hint></outline_data>
下面执行SQL Profiles对应的SQL：
session 2:
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 391 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 2500 | 100K| 391 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 80073 | 860K| 321 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- SQL profile "SQLPROFILE_NAME1" used for this statement
Statistics
----------------------------------------------------------
45 recursive calls
0 db block gets
1444 consistent gets
1 physical reads
0 redo size
2132 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
36 rows processed
–虽然用了sqlprofile，但是没生效。
虽然执行计划的输出Note部分显示已经使用到了SQL Profile，但是执行计划并没有如我们预期一样被改变，依然是全表扫描，查看存储hint的基表也显示索引扫描的hint已经被绑定到了这个SQL上，那么问题出哪了？
这是由于SQL Profile对于hint是非常挑剔的，SQL Profile里接受的hint需要提供Query Block Name(初始化参数类的hint不需要提供Query Block Name），否则优化器会忽略掉这些hint，我们重新设置SQL Profile的Hints，在Hints中加上Query Block Name看看。
看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name"。这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile：
session 1：
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');
5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
6 v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select COMP_DATA from dba_sql_profiles a, SQLOBJ$DATA b
2 where a.signature = b.signature
3 and a.name='SQLPROFILE_NAME1';
COMP_DATA
----------------------------------------------------------------------------------------------------
<outline_data><hint><![CDATA[USE_NL(T1@SEL$1 T2@SEL$1)]]></hint><hint><![CDATA[INDEX(T2@SEL$1)]]></h
int></outline_data>
再次执行下面的SQL：
session 2：
SQL> /
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 5071 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 70 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SQLPROFILE_NAME1" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
306 consistent gets
0 physical reads
0 redo size
2132 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
已生效。这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
那么我们再一次手工修改T1表的统计信息，看看结果如何：
session 1：
修改统计信息：
SQL> exec dbms_stats.set_table_stats('SCOTT','T1',numrows=>5000000);
PL/SQL procedure successfully completed.
session 2：
SQL> /
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 9M| 500K (1)| 01:40:03 |
| 1 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9M| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 99 (30)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SQLPROFILE_NAME1" used for this statement
Statistics
----------------------------------------------------------
226 recursive calls
0 db block gets
434 consistent gets
1 physical reads
0 redo size
2132 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
36 rows processed
可以看到，Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大，但是这里的执行计划仍然与未修改统计信息之前一样，使用range scan+ nested loop join。
Manual类型的sql profile可以起到很好的稳定目标SQL的执行计划的作用，不依赖统计信息，这一点是auto类型的sql profile所不具备的。
通过以上的测试，我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用，同时也验证了这种方式的有效性，SQL Profiles能够像Outlines一样，能够稳定SQL的执行计划。
这里的问题是：稳定一条SQL语句的Hints从哪里来？简单的sql，没问题，我们可以手工构造，但是复杂的SQL，手工构造相对比较复杂，同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。从10g开始，v$sql_plan中就包括了SQL语句OUTLINE数据，也就是稳定执行计划的Hints。从下面可以看到：
SQL> set autot off
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
36 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4zbqykx89yc8v, child number 1
-------------------------------------
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and
t1.object_id=t2.object_id
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5071 (100)| |
| 1 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 70 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL))
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile SQLPROFILE_NAME1 used for this statement
47 rows selected.
上面所显示的“Outline Data”即是我们稳定SQL执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的SQL中）。对需要稳定执行计划的SQL，我们所要做的就是如前面所示，将Hints与SQL文本一起创建一个SQL Profile。这里不得不提到一个SQL脚本，coe_xfr_sql_profile.sql。这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。后面演示。
我们可以对比下：
删除profile，走原来的执行计划，查看outline data信息，如下：
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
不同的执行计划，outline data信息是不同的，它是这条sql走这个执行计划，所隐藏的hint，所以不相同。
- coe_xfr_sql_profile脚本
1)构造2条sql
SQL> set pages 1000 lines 1000
SQL> select * from t2 where object_id=2; --模拟的good sql，走索引
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ------------ ------------ ------------------- ------- - - - ---------- ------------------------------
SYS C_OBJ# 2 CLUSTER 21-NOV-19 21-NOV-19 2019-11-21:11:44:16 VALID N N N 5
SQL> select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2; --模拟的bad sql，全表扫描
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ------------ ------------ ------------------- ------- - - - ---------- ------------------------------
SYS C_OBJ# 2 CLUSTER 21-NOV-19 21-NOV-19 2019-11-21:11:44:16 VALID N N N 5
SQL> set autot trace
SQL> select * from t2 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 4244861920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 321 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 98 | 321 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1151 consistent gets
0 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--绑定必须依赖sqlid和plan hash value，可以如下查询：
SQL>select SQL_ID,PLAN_HASH_VALUE,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t2%' and sql_text not like '%v$sql%';
SQL>col SQL_TEXT for a60
SQL>/
SQL_ID PLAN_HASH_VALUE SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------- ---------- ----------
24328pmmhh5tp 1513984157 EXPLAIN PLAN SET STATEMENT_ID='PLUS490003' FOR select /*+ no 1 1 1
_index(t2 T2_IDX)*/ * from t2 where object_id=2
5u3pq1u6qj21n 4244861920 EXPLAIN PLAN SET STATEMENT_ID='PLUS490003' FOR select * from 1 1 1
t2 where object_id=2
189a578w09x47 4244861920 select * from t2 where object_id=2 4 1 4
18z48fk2jy688 1513984157 select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2 3 1 3
2）现在的任务就是把good sql的执行计划绑定到bad sql上面。
步骤如下：
我们已经模拟出来了good sql，也就是错误执行计划的sql加了hint后改写的。
SQL>@coe_xfr_sql_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: 189a578w09x47
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
4244861920 .001
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 4244861920
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "189a578w09x47"
PLAN_HASH_VALUE: "4244861920"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF;
Execute coe_xfr_sql_profile_189a578w09x47_4244861920.sql
on TARGET system in order to create a custom SQL Profile
with plan 4244861920 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>
SQL>@coe_xfr_sql_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: 18z48fk2jy688
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1513984157 .028
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1513984157
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "18z48fk2jy688"
PLAN_HASH_VALUE: "1513984157"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF;
Execute coe_xfr_sql_profile_18z48fk2jy688_1513984157.sql
on TARGET system in order to create a custom SQL Profile
with plan 1513984157 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
每条sql执行完脚本，都会生成一个文件，一个是目标sql的（good），一个是原sql的（bad）
编辑原sql的（bad）的生成文件，用目标sql的（good）的hint内容替换原sql的（bad）的内容，大概如下：
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');
然后执行此文件：
SQL>@coe_xfr_sql_profile_18z48fk2jy688_1513984157.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_18z48fk2jy688_1513984157.sql 11.4.4.4 2021/02/26 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_18z48fk2jy688_1513984157.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 18z48fk2jy688 based on plan hash
SQL>REM value 1513984157.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_18z48fk2jy688_1513984157.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_18z48fk2jy688_1513984157');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2]');
15 DBMS_LOB.CLOSE(sql_txt);
16 h := SYS.SQLPROF_ATTR(
17 q'[BEGIN_OUTLINE_DATA]',
18 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
19 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
20 q'[DB_VERSION('11.2.0.4')]',
21 q'[ALL_ROWS]',
22 q'[OUTLINE_LEAF(@"SEL$1")]',
23 q'[INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
24 q'[END_OUTLINE_DATA]');
25 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
26 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
27 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
28 sql_text => sql_txt,
29 profile => h,
30 name => 'coe_18z48fk2jy688_1513984157',
31 description => 'coe 18z48fk2jy688 1513984157 '||:signature||' '||:signaturef||'',
32 category => 'DEFAULT',
33 validate => TRUE,
34 replace => TRUE,
35 force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
36 DBMS_LOB.FREETEMPORARY(sql_txt);
37 END;
38 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
8178536432892493039
SIGNATUREF
---------------------
10922292449146850743
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_18z48fk2jy688_1513984157 completed
验证：
SQL> select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 4244861920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Note
-----
- SQL profile "coe_18z48fk2jy688_1513984157" used for this statement
Statistics
----------------------------------------------------------
256 recursive calls
2 db block gets
249 consistent gets
1 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
从上述显示内容中可以看到，新生成的manual类型的sql profile（名为coe_18z48fk2jy688_1513984157）已经生效了，原目标SQL的执行计划也从原先的对T2的全表扫描变为了对索引T2_IDX 的
索引范围扫描，这也同时说明Manual类型的sql profile确实可以在不改变目标SQL的SQL文本的情况下更改其执行计划。
- 脚本coe_load_sql_profile
为了避免影响，先删除原来的sql profile
session 2：
SQL> exec dbms_sqltune.drop_sql_profile('coe_18z48fk2jy688_1513984157');
PL/SQL procedure successfully completed.
查询sql id
SQL> select SQL_ID,PLAN_HASH_VALUE,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t2%' and sql_text not like '%v$sql%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------- ---------- ----------
189a578w09x47 4244861920 select * from t2 where object_id=2 1 1 1
18z48fk2jy688 1513984157 select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2 1 2 1
执行脚本：
SQL> @coe_load_sql_profile
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 18z48fk2jy688 --bad plan
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 189a578w09x47 --good plan
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
4244861920 .003
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 4244861920 --good plan hash value
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "18z48fk2jy688"
MODIFIED_SQL_ID: "189a578w09x47"
PLAN_HASH_VALUE: "4244861920"
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('11.2.0.4')
0004 DB_VERSION('11.2.0.4')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$1")
0007 INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
0008 END_OUTLINE_DATA
验证：
session 1：
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4244861920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Note
-----
- SQL profile "18Z48FK2JY688_4244861920" used for this statement
Statistics
----------------------------------------------------------
1048 recursive calls
0 db block gets
519 consistent gets
1 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
20 sorts (memory)
0 sorts (disk)
1 rows processed
- 极简版
如果客户是内网环境，不让传输脚本，脚本内容很多，手写不现实，下面就是上面脚本的核心内容，比较短小精悍，可以手写，直接使用。
– 给特定SQL绑上改写后的特定执行计划，需要事先查询得到对应的sql_id 和 plan_hash_value, COE_profile.sql的极简版
declare
v_hint SYS.SQLPROF_ATTR;
v_sql_text clob;
begin
select sql_fulltext into v_sql_text from vKaTeX parse error: Expected 'EOF', got '&' at position 21: …here sql_id = '&̲bad_sqlid' and …sql_plan
where sql_id = ‘&good_sqlid’
and other_xml is not null)) d;
dbms_sqltune.import_sql_profile(
v_sql_text,
v_hint,
‘sql_profile_&bad_sqlid’,
force_match=>true,replace=>true
);
end;
/
删除原先的
SQL> exec dbms_sqltune.drop_sql_profile('18Z48FK2JY688_4244861920');
PL/SQL procedure successfully completed.
查询sql id：
SQL> select SQL_ID,PLAN_HASH_VALUE,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t2%' and sql_text not like '%v$sql%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------- ---------- ----------
4y10hb0u8tpft 1513984157 EXPLAIN PLAN SET STATEMENT_ID='PLUS580003' FOR select /*+ no 1 2 1
_index(t2 T2_IDX)*/ * from t2 where object_id=2
189a578w09x47 4244861920 select * from t2 where object_id=2 1 2 1
18z48fk2jy688 1513984157 select /*+ no_index(t2 T2_IDX)*/ * from t2 where object_id=2 2 3 2
执行脚本：
SQL> declare
2 v_hint SYS.SQLPROF_ATTR;
3 v_sql_text clob;
4 begin
5 select sql_fulltext into v_sql_text from v$sql where sql_id = '&bad_sqlid' and rownum=1;
6 select extractvalue(value(d), '/hint') as outline_hints bulk collect
7 into v_hint
8 from xmltable('/*/outline_data/hint' passing
9 (select xmltype(other_xml) as xmlval
10 from v$sql_plan
11 where sql_id = '&good_sqlid'
12 and other_xml is not null)) d;
13 dbms_sqltune.import_sql_profile(
14 v_sql_text,
15 v_hint,
16 'sql_profile_&bad_sqlid',
17 force_match=>true,replace=>true
18 );
19 end;
20 /
Enter value for bad_sqlid: 18z48fk2jy688
old 5: select sql_fulltext into v_sql_text from v$sql where sql_id = '&bad_sqlid' and rownum=1;
new 5: select sql_fulltext into v_sql_text from v$sql where sql_id = '18z48fk2jy688' and rownum=1;
Enter value for good_sqlid: 189a578w09x47
old 11: where sql_id = '&good_sqlid'
new 11: where sql_id = '189a578w09x47'
Enter value for bad_sqlid: 18z48fk2jy688
old 16: 'sql_profile_&bad_sqlid',
new 16: 'sql_profile_18z48fk2jy688',
PL/SQL procedure successfully completed.
验证：
session 1：
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4244861920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Note
-----
- SQL profile "sql_profile_18z48fk2jy688" used for this statement --已变化
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
manual绑定，其实就是加hint，为了方便，其实就是拿脚本绑定，有3个脚本：
1、极简版
2、coe_xfr_sql_profile
3、coe_load_sql_profile
coe_xfr_sql_profile.sql脚本来固定sql 执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间sql执行计划的固定。
最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。
但是也分为2中情况，一是1个sql，有多个执行计划，即sql_id相同，但是plan hash value不同，拿好的plan hash value去绑定坏的plan hash value；
二是，为了生成最优的执行计划，人为给原sql加了hint，生成新的sql，生成新的执行计划，拿好的sql_id和plan hash value去绑定坏的sql_id
绑定不存在的执行计划–绑定加hint执行计划
declare
v_hints sys.sqlprof_attr;
hint varchar2(100);
v_sql_id varchar2(100);
v_sql_fulltext varchar2(32767);
begin
v_sql_id := ‘&请输入SQL_ID’;
hint := ‘&请输入HINT’;
select sql_fulltext
into v_sql_fulltext
from v$sql
where sql_id = v_sql_id
and rownum = 1;
v_hints := sys.sqlprof_attr(hint);
dbms_sqltune.import_sql_profile(v_sql_fulltext,
v_hints,
v_sql_id, —SQL PROFILE 名字，这里我用sql_id来命名
force_match => true);
end;
/
session 2：
SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t1%' and sql_text not like '%v$sql%';
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
------------- ------------------------------------------------------------ ----------- ---------- ----------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '% 3 3 3
T1%' and t1.object_id=t2.object_id
SQL> declare
2 v_hints sys.sqlprof_attr;
3 hint varchar2(100);
4 v_sql_id varchar2(100);
5 v_sql_fulltext varchar2(32767);
6 begin
7 v_sql_id := '&请输入SQL_ID';
8 hint := '&请输入HINT';
9 select sql_fulltext
10 into v_sql_fulltext
11 from v$sql
12 where sql_id = v_sql_id
13 and rownum = 1;
14 v_hints := sys.sqlprof_attr(hint);
15 dbms_sqltune.import_sql_profile(v_sql_fulltext,
16 v_hints,
17 v_sql_id, ---SQL PROFILE 名字，这里我用sql_id来命名
18 force_match => true);
19 end;
20 /
Enter value for 请输入sql_id: 4zbqykx89yc8v
old 7: v_sql_id := '&请输入SQL_ID';
new 7: v_sql_id := '4zbqykx89yc8v';
Enter value for 请输入hint: USE_NL(T1@SEL$1 T2@SEL$1) INDEX(T2@SEL$1)
old 8: hint := '&请输入HINT';
new 8: hint := 'USE_NL(T1@SEL$1 T2@SEL$1) INDEX(T2@SEL$1)';
PL/SQL procedure successfully completed.
SQL> select COMP_DATA from dba_sql_profiles a, SQLOBJ$DATA b where a.signature = b.signature and a.name='SQLPROFILE_NAME1';
no rows selected
SQL> select COMP_DATA from dba_sql_profiles a, SQLOBJ$DATA b where a.signature = b.signature and a.name='4zbqykx89yc8v';
COMP_DATA
--------------------------------------------------------------------------------
<outline_data><hint><![CDATA[USE_NL(T1@SEL$1 T2@SEL$1) INDEX(T2@SEL$1)]]></hint>
session 1：
SQL> set autot off
SQL> set heading off
SQL> set echo off
SQL> select * from table(dbms_xplan.display_cursor('4zbqykx89yc8v',null,'OUTLINE'));
SQL_ID 4zbqykx89yc8v, child number 1
-------------------------------------
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and
t1.object_id=t2.object_id
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5071 (100)| |
| 1 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 70 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL))
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile 4zbqykx89yc8v used for this statement
47 rows selected.
可以看出来已经生效，但是此hint要自己判断，自己书写，难度较大。
总结
- manual类型的sql profile本质上就是一堆hint的组合，这一堆hint的组合实际上来源于执行计划中outline data部分的hint组合。manual类型的sql profile同样可以在不更改目标sql的sql文本的情况下调整其执行计划，而且更为重要的是，manual类型的sql profile可以起到很好的稳定目标sql的执行计划的作用，这一点是automatic类型的sql profile所不具备的。
- 最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。原理就是从v$sql_plan中的other_xml中提取出profile用到的属性，并使用dbms_sqltune.import_sql_profile导入但是也分为2中情况：
一是1个sql，有多个执行计划，即sql_id相同，但是plan hash value不同，拿好的plan hash value去绑定坏的plan hash value；
二是，为了生成最优的执行计划，人为给原sql加了hint，生成新的sql，生成新的执行计划，拿好的sql_id和plan hash value去绑定坏的sql_id。 - 各种方案适用的场景不同，如下：
4. 看来SQL Profile可以接受常规的hint ,只不过这些hint要包含Query Block Name，如果SQL Profile发现指定的hint无效，会简单的忽略掉这些hint，不会报任何的错误，也不会做任何的校验。既然常规的hint可以对SQL Profile起作用，那么我们也可以用SQL Profile来锁定执行计划了。从上面的执行计划输出也可以看到由于我们使用了常规的hint，因此执行计划的基数信息并没有得到纠正，仅仅是通过这种暴力的hint把执行计划强制修正为索引扫描了。
1319
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
