使用
SQL Profiles主要解决2个棘手的问题：
在不让应用修改SQL的前提下，解决SQL的执行计划差的问题。
1、对于执行计划变差的SQL，根据历史的执行计划，固定好的执行计划，从而达到优化的目的（ dbms_sqltune.accept_sql_profile ）
2、应用的SQL因为错加了hint导致走了差的执行计划， 在不能修改应用中的SQL的情况下，构造好的执行计划， 使SQL语句按指定的执行计划运行。（ dbms_sqltune.import_sql_profile ）
该文章主要 介绍：
★什么是SQL Profiles及其作用
★ 如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。 同时也介绍了SQL的signature。
一、初步了解 SQL Profiles
试验过程:
1、 创建测试表及其索引
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
2、查看当前的统计信息
SQL>select * from dba_tab_statistics where table_name=upper('&table_name');--新建的表当然没统计信息
3、对新建的表收集统计信息
SQL> exec dbms_stats.gather_table_stats('zhoushuai','t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('zhoushuai','t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
--此时已经有统计信息了
4、执行一条SQL，查看并分析其执行计划
SQL>select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
...................---这里结果省略
SQL> set autotrace trace
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| 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 2500 | 100K| 418 (1)| 00:00:06 | 《《《《《《《《《《《表链接为hash join
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 70 (0)| 00:00:01 | 《《《《《《《《《《《两表均为全表扫
| 3 | TABLE ACCESS FULL| T2 | 87274 | 937K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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)
Statistics
----------------------------------------------------------
38 recursive calls
0 db block gets
1544 consistent gets 《《《《注意逻辑读
1248 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> set autotrace off
PS：我们只注意关注返回的结果行数、执行计划以及逻辑读这些信息
分析：2个表都是全表扫，看过滤条件，T1有LIKE '%T1%'，显然不能走索引正常，T2也是全表，所以这里有优化空间
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2500行，即T1表总行数的5%，
如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join
5、修改SQL，分析Oracle优化器评估的index range_scan+nested loop的成本：
SQL> set autotrace trace
SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id; 2 3 4
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")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
290 consistent gets
14 physical reads
0 redo size
1777 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> set autotrace off
分析：可以看到Oracle优化器评估的成本为5071，远远高于原来的418。
但是加了HINT之后实际的逻辑读只有290，低于原始SQL的1544。
所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，
也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。
6、下面我们用Oracle的SQL Tuning Advisor来尝试优化这条SQL：
SQL> select sql_id,sql_text from v$sql where sql_text like 'select t1.*,t2.owner from t1,t2 where%'; ----找到SQL_ID
SQL_ID SQL_TEXT
--------------------------------------- ----------------------------------------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
SQL> var tuning_task varchar2(100);
SQL> DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id:='4zbqykx89yc8v';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task:=l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
END;
/
PL/SQL procedure successfully completed.
SQL> print tuning_task;
TUNING_TASK
------------------------
TASK_1833
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual; 《《《《《《《《《《《查看建议
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
----------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_1833
Tuning Task Owner : ZHOUSHUAI
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 04/05/2016 04:17:54
Completed at : 04/05/2016 04:17:59
-------------------------------------------------------------------------------
Schema Name: ZHOUSHUAI
SQL ID : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 79.99%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1833',
task_owner => 'ZHOUSHUAI', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .050657 .039519 21.98 %
CPU Time (s): .049892 .038494 22.84 %
User I/O Time (s): 0 0
Buffer Gets: 1494 296 80.18 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 36 36
Fetches: 36 36
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 36 | 1476 | 418 (1)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 36 | 1080 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 937K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
| 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 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")
-------------------------------------------------------------------------------
分析：上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。
结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为36， 相当地精确。
我们要做的就是Accept SQL Profile，接受这个SQL Profile。
7、使用这和profile
PS：这里重点强调，下面执行的这个比上边Recommendation的要多一个参数,force_match=>true，因为加上这个参数后，
功能类似于的cursor_sharing起到force效果，即使系统的是EXACT模式。
但是，注意，强烈建议该参数仅用于故障救急，稳定的生产环境最好不要加force_match=>true这个参数，否则会有意想不到的问题出现。
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1833',task_owner => 'ZHOUSHUAI', replace => TRUE, force_match=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace trace
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: 1022743391
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
| 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 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 "SYS_SQLPROF_0153e2f3b7de0000" used for this statement 》》》》》》》这个名字是自动生成的
Statistics
----------------------------------------------------------
122 recursive calls
0 db block gets
477 consistent gets
3 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
36 rows processed
分析：Note中果然使用了profile SYS_SQLPROF_0153e2f3b7de0000
那我们在执行其他类似SQL：
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;
57 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 2337 | 184 (0)| 00:00:03 |
| 1 | NESTED LOOPS | | 57 | 2337 | 184 (0)| 00:00:03 |
| 2 | NESTED LOOPS | | 57 | 2337 | 184 (0)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 57 | 1710 | 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 '%T2%' AND "T1"."OBJECT_NAME" IS NOT NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_0153e2f3b7de0000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
301 consistent gets
0 physical reads
0 redo size
2361 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
57 rows processed
分析：这里可以看到T2条件也使用的该profile，但是如果Recommendation不加force_match=>true的话，就不会参考使用该profile。
8、查看profile相关内容
上面生成的profile名字为：SYS_SQLPROF_0153e2f3b7de0000
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
NAME CATEGORY SIGNATURE TYPE STATUS FORCE_MAT
-------------------------------------------------------- ------------------------------------------------------------
SYS_SQLPROF_0153e2f3b7de0000 DEFAULT 8.9755E+18 MANUAL ENABLED YES 《《《《FORCE_MAT 当不采用force_match=>true参数的时候，为NO
备注：查看相关profile内容可在sys.sqlobj$和sys.sqlobj$data视图中看到（11G）
总之， 一些复杂的SQL，我经常会先通过SQL Tuning Advisor来分析一下，看能不能让Oracle自已找出一个更好的执行计划。
我们来看看，SQL Profiles实际上是些什么：
SQL>col SIGNATURE for 999,999,999,999,999,999,999
SQL> select a.signature,category,name,hint,sql_text
from (
2 3 SELECT extractValue(value(h),'.') AS hint,
4 od.signature signature
5 FROM sys.sqlobj$data od, sys.sqlobj$ so,
6 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
7 WHERE so.signature = od.signature
8 AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id
) a,
dba_sql_profiles b
where a.signature=b.signature; 9 10 11 12 13
SIGNATURE CATEGORY NAME HINT SQL_TEXT
---------------------------- ---------- ---------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
8,975,541,025,552,400,288 DEFAULT SYS_SQLPROF_0153e2f3b7de0000 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
8,975,541,025,552,400,288 DEFAULT SYS_SQLPROF_0153e2f3b7de0000 OPTIMIZER_FEATURES_ENABLE(default) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
补充：10G可用下边SQL查看：
--select name,category,signature,type,status,force_matching from dba_sql_profiles;
--select * from sys.sqlprof$attr;
分析：signature可以理解为与sql_id、sql_hash_value类似的值，用来标识SQL。在10g以上的版本中，
查看v$sql的定义就可以发现2列：exact_matching_signature、force_matching_signature。
SQL> select rownum,a.* from
(select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text
from v$sql where sql_text like '%/*%xjs%' and sql_text not like '%v$sql%' order by 1) a; 2 3
ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT
---------- ---------------------------- ---------------------------- --------------- ----------------------------------------------------------
1 3,939,730,931,515,200,254 17,443,893,418,101,517,951 3617692013 select /* xjs */ object_name from T1 where object_name='t1'
2 10,964,210,455,693,560,558 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from T1 where rownum<=3
3 10,964,210,455,693,560,558 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from T1 where rownum<=3
4 11,217,690,300,719,901,571 354,482,119,692,997,204 3836375644 select /* xjs */ 2 from t1 where rownum<=1
5 11,974,975,582,747,367,847 354,482,119,692,997,204 3836375644 select /* xjs */ 1 from t1 where rownum<=1
6 12,941,882,703,651,921,406 17,443,893,418,101,517,951 3617692013 select /* xjs */ object_name from T1 where object_name='T1'
7 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from T1 where rownum<=1
8 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 select /* xjs */ OBJECT_NAME from T1 where rownum<=1
9 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 SELECT /* xjs */ object_name from T1 where rownum<=1
10 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from t1 where rownum<=1
10 rows selected.
分析：
第2、3条SQL的exact_matching_signature相同，第7、8、9、10条SQL的exact_matching_signature相同。
第2、3条SQL的force_matching_signature相同，第4、5条SQL的force_matching_signature相同，
第7、8、9、10条的SQL的force_matching_signature相同。第1、6条SQL的force_matching_signature相同
结论：
对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。
对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。
但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。
SQL> select rownum,a.* from
(select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text
from v$sql where sql_text like '%/*%xjs2%' and sql_text not like '%v$sql%' order by 1) a; 2 3
ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT
---------- ---------------------------- ---------------------------- --------------- ----------------------------------------------------------------
1 5,363,536,431,331,905,229 5,363,536,431,331,905,229 3836375644 select /* xjs2 */ object_name from T1 where object_name='T1' and rownum<=:rn
2 5,363,536,431,331,905,229 5,363,536,431,331,905,229 3836375644 select /* xjs2 */ object_name from t1 where object_name='T1' and rownum<=:rn
3 12,992,689,086,515,482,106 12,992,689,086,515,482,106 3836375644 select /* xjs2 */ object_name from t1 where object_name='T2' and rownum<=:rn
可以看到，现在exact_matching_signature与force_matching_signature完全一样了。
从force_matching_signature的特性，我们可以想到一个用途：
用于查找没有使用绑定变量的SQL语句，类似于使用plan_hash_value来查找。
回到前面，accept_sql_profile这个过程，force_match参数设为TRUE，那么dba_sql_profiles中的signature则是由SQL的force_matching_signature而来， 否则便是exact_matching_signature。
对于Outlines来说，则只能是exact_matching_signature。从这个角度上讲，Sql Profiles比Outlines的使用范围更广，
因为Sql profiles对没有使用绑定变量的SQL也支持得很好。值得注意的是，Sql profiles的force_match属性是不能更改的，只能在创建时指定，如果要更改，则只能重新创建改Sql Profile。
下面来看看sys.sqlprof$attr数据字典。这里面没有SQL Profile的名字，而是用的sql的signature。
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
可以看到，SQL Profiles的attr_val实际上就是一些Hints，这跟Outlines没有本质上的区别。只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。
这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2500*0.0144刚好等于36。这里就是告诉Oracle优化器，
T1表经过谓语过滤后返回行数应该为评估的0.0144倍。
从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器。
看下面的测试：
SQL> exec dbms_stats.set_table_stats('ZHOUSHUAI','T1',numrows=>5000000);
PL/SQL procedure successfully completed.
SQL> @showplan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3600 | 144K| 455 (9)| 00:00:06 |
|* 1 | HASH JOIN | | 3600 | 144K| 455 (9)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 3600 | 105K| 107 (35)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 937K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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 "SYS_SQLPROF_0153e2f3b7de0000" used for this statement
21 rows selected.
将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回5000000*5%*0.0144=3600行。这里执行计划又变回为full scan+hash join。
可以看到，虽然SQL Profile起作用了，但是并没有锁定执行计划。
二、如何手工来为创建、生成SQL Profile，以及如何让SQL Profile也能像Outlines一样锁定SQL的执行计划，以保持SQL执行计划的稳定性。
1、 如何来手工创建SQL Profiles?
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
这个过程其并不仅仅IMPORT，根据REPLACE的选项可以实现 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)
2、下面我们就用这个过程来创建SQL PROFILE：
首先为避免干扰，前边测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0153e2f3b7de0000');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats('ZHOUSHUAI','T1',numrows=>49953);
PL/SQL procedure successfully completed.
完成后查看该表的信息：
SQL> @tabstat.sql
Please enter Name of Table Owner: zhoushuai
Please enter Table Name : t1
**********************************************************
Table Level
**********************************************************
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
T1 49,953 2,52 0 0 30 YES 50,000 04-13-2016
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OBJECT_ID 50,000 .00002000 1 0 50,000 04-05-2016
OBJECT_NAME 28,836 .00003468 1 0 50,000 04-05-2016
**********************************************************
Partition Level
**********************************************************
现在手工创建一个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 a.signature,category,name,hint,sql_text
2 from (
3 SELECT extractValue(value(h),'.') AS hint,
4 od.signature signature
5 FROM sys.sqlobj$data od, sys.sqlobj$ so,
6 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
7 WHERE so.signature = od.signature
8 AND so.category = od.category
9 AND so.obj_type = od.obj_type
10 AND so.plan_id = od.plan_id
11 ) a,
12 dba_sql_profiles b
13 where a.signature=b.signature;
SIGNATURE CATEGORY NAME HINT SQL_TEXT
---------------------- ------------ ----------------- ----------------------------------------------------------------------------------------------------------------------
3960696072677096522 DEFAULT SQLPROFILE_NAME1 USE_NL(T1 T2) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
3960696072677096522 DEFAULT SQLPROFILE_NAME1 INDEX(T2) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
分析：可以看到，该SQL已经配置了我们想要的hint的profile
3、查看执行计划
SQL> set autotrace trace
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 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 | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 2498 | 74940 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 767K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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
----------------------------------------------------------
0 recursive calls
0 db block gets
1493 consistent gets
1247 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
可以看到，SQL使用了SQL Profile SQLPROFILE_NAME1，不过没有达到我们预期的效果
分析： 看起来是SQL Profile使用的Hints有问题。
4.我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name”。
这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile
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 /
查看profile：
PL/SQL procedure successfully completed.
SQL> select a.signature,category,name,hint,sql_text
2 from (
3 SELECT extractValue(value(h),'.') AS hint,
4 od.signature signature
5 FROM sys.sqlobj$data od, sys.sqlobj$ so,
6 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
7 WHERE so.signature = od.signature
8 AND so.category = od.category
9 AND so.obj_type = od.obj_type
10 AND so.plan_id = od.plan_id
11 ) a,
12 dba_sql_profiles b
13 where a.signature=b.signature;
SIGNATURE CATEGORY NAME HINT SQL_TEXT
---------------------- ---------- ----------------- ------------------------------------------------------------------------------------------------------------------------------
3960696072677096522 DEFAULT SQLPROFILE_NAME1 USE_NL(T1@SEL$1 T2@SEL$1) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
3960696072677096522 DEFAULT SQLPROFILE_NAME1 INDEX(T2@SEL$1) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
6、执行该sql查看执行计划：
SQL> set autotrace trace
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 | 9 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
298 consistent gets
0 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
分析：这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
7、修改统计信息测试：
那么我们再一次手工修改T1表的统计信息，看看结果如何：
SQL> exec dbms_stats.set_table_stats('ZHOUSHUAI','T1',numrows=>5000000);
PL/SQL procedure successfully completed.
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 9521K| 500K (1)| 01:40:03 |
| 1 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 9 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
298 consistent gets
0 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
分析：可以看到，Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大，
但是这里的执行计划仍然与未修改统计信息之前一样，使用range scan+ nested loop join。
通过以上的测试，我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用，同时也验证了这种方式的有效性，
SQL Profiles能够像Outlines一样，能够稳定SQL的执行计划。
三、接下来我们需要完成两个任务
任务一：对现有的SQL稳定其执行计划（针对执行计划不稳定的SQL）
这里的问题是：
稳定一条SQL语句的Hints从哪里来？
简单的sql，没问题，我们可以手工构造，但是复杂的SQL，手工构造相对比较复杂，同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。
从10g开始，v$sql_plan中就包括了SQL语句OUTLINE数据，也就是稳定执行计划的Hints。
从下面可以看到：
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
执行完上边SQL后执行下边
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 77bdyugrbkj0y, 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 | | | | 500K(100)| |
| 1 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 9 | 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脚本，来自MOS。
”SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]“，
在这篇文章中点击 [10.2, 11.1, 11.2, 12.1 download] 可以下载到sqlt.zip，这个压缩文件内有一个文件：coe_xfr_sql_profile.sql。
这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。
下面是示例：
QL> select s.SQL_ID,s.PLAN_HASH_VALUE,s.SQL_FULLTEXT from v$sql s where sql_text like 'select t1.*,t2.owner%';
SQL_ID PLAN_HASH_VALUE
--------------------------------------- ---------------
SQL_FULLTEXT
---------------------------------------------------------------
77bdyugrbkj0y 1022743391 ---这是要找的SQL_ID和PLAN_HASH_VALUE
---注意的是生产环境要选择你认为最优的那个作为下边脚本的输入值
select t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
或用下边这个命令查更准确确定哪个hash的执行计划最好，因为执行计划不稳定的会有多个PLAN_HASH_VALUE
/*
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='77bdyugrbkj0y' order by TIMESTAMP;
col options for a15
col operation for a20
col object_name for a20
select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
from DBA_HIST_SQL_PLAN
where sql_id ='77bdyugrbkj0y'
and plan_hash_value in (1022743391)
order by ID,TIMESTAMP;
*/
找到对应的SQL_ID和PLAN_HASH_VALUE
SQL> @coe_xfr_sql_profile.sql 77bdyugrbkj0y 1022743391
Parameter 1:
SQL_ID (required)
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "77bdyugrbkj0y"
PLAN_HASH_VALUE: "1022743391"
........................中间部分省略（会提示执行下边的内容）
SQL>@coe_xfr_sql_profile_77bdyugrbkj0y_1022743391.sql
执行完成后，查看profile大概内容为（可以用上边查看profile的脚本查看）：
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
c oe_xfr_sql_profile.sql这个脚本首先要求输入sql_id，然后从shared pool、awr中获取sql执行的各个执行计划的统计信息
（执行计划不稳定的SQL通常会有多个不同的执行计划），然后输入你认为是正确的、需要稳定的执行计划的hash value，
脚本就会生成另一个脚本，这里为coe_xfr_sql_profile_77bdyugrbkj0y_1022743391.sql，
然后运行这个脚本，就会创建出稳定执行计划所需要的SQL Profile，
SQL Profile的名字为：coe+sql_id+plan_hash_value，这里为coe_77bdyugrbkj0y_1022743391。
注意，这里创建的SQL Profile，force match默认为FALSE，我们可以手工修改脚本将其改为TRUE，
同时我们也可以按意愿来修改生成的脚本的其他内容。
任务二：不改sql，即使SQL已经使用hints。固定执行计划
常常遇到这样的情况，SQL语句其执行计划有问题，或者是SQL使用了错误的Hints（比如 /*+ RULE */）导致SQL性能较差，
但是应用又不能修改或者时间内不能修改，那么我们怎么来改变SQL的执行计划呢。
有3种办法，
一种是调整统计信息，这个不建议使用，因为比较复杂、不稳定可靠（统计信息可能会重新收集），影响面广（会影响其他访问此对象的SQL）。
二种是使用OUTLINE，这种方法比较复杂。
三种就是我们今天要介绍的使用SQL Profiles了。
使用SQL Profiles来改变SQL的执行计划，其本质上就是使用Hints来改变SQL的执行计划。
1、简单的SQL，我们同样可以像前面一样手工构造Hints然后再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE来实现。（但是这种方法还是略显烦琐）
2、取得原始SQL的文本（如有可能还包括sql id)
构造一个与原始SQL在逻辑上、结构上完全相同的SQL。
这里强制逻辑上和结构上相同，SQL解析的用户名、SQL中引用对象的用户名甚至是一些predicate条件都可以不同。（当然能够与原始SQL完全一样就更省事。）
执行我们构造的SQL，并取得构造的SQL的Outline Data。
使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile。
下面我们来演示一下整个过程。我们这里要修改执行计划的SQL是：
select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id;
SQL> select s.SQL_ID,s.PLAN_HASH_VALUE,s.SQL_FULLTEXT from v$sql s where sql_text like 'select /*+ orig_sql%';
SQL_ID PLAN_HASH_VALUE
--------------------------------------- ---------------
SQL_FULLTEXT
----------------------------------------------------------------------
gmvb9bp7f9kqd 1838229974
select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 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 | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 2498 | 74940 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 767K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1493 consistent gets
1247 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 | 9 | 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")
Statistics
----------------------------------------------------------
36 recursive calls
0 db block gets
336 consistent gets
0 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> select s.SQL_ID,s.PLAN_HASH_VALUE,s.SQL_FULLTEXT from v$sql s where sql_text like 'select /*+ modify_sql index(t1)%';
SQL_ID PLAN_HASH_VALUE
--------------------------------------- ---------------
SQL_FULLTEXT
--------------------------------------------------------
0vdkn8rysa3j1 1022743391
select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
1 row selected.
然后使用coe_xfr_sql_profile.sql脚本来提取我们 构造 的SQL的Outline Data，
生成的结果为coe_xfr_sql_profile_0vdkn8rysa3j1_1022743391.sql，打开结果文件，
可以看到有这么一段
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'[FULL(@"SEL$1" "T1"@"SEL$1")]',
q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
再针对gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql，生成的结果文件为coe_xfr_sql_profile_gmvb9bp7f9kqd_1838229974.sql。手工修改这个文件，将里面h := SYS.SQLPROF_ATTR…那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force_match从FALSE改成TRUE。
最后我们运行coe_xfr_sql_profile_gmvb9bp7f9kqd_1838229974.sql这个脚本文件：
SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;
SIGNATURE
---------------------
15409905709853673912
SIGNATUREF
---------------------
9964126883768520950
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_gmvb9bp7f9kqd_1838229974 completed
样就完成了我们所需要的SQL Profile的创建。
下面再看看原来的SQL执行情况（这里将like条件改了一下，以查看force match是否起作用）：
SQL> set autotrace trace
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T2%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 | 9 | 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 "coe_gmvb9bp7f9kqd_1838229974" used for this statement
Statistics
----------------------------------------------------------
420 recursive calls
0 db block gets
713 consistent gets
1 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
51 sorts (memory)
0 sorts (disk)
36 rows processed
可以看到SQL Profile起作用了。
对于SQL Profiles来说，不属于任何一个用户，比Outlines更具有操控性灵活性。
--补充知识
DBMS_SQLTUNE.DROP_SQL_PROFILE( name =>'my_sql_profile' );
通过select * fromdba_sql_profiles a;查询出PROFILE 的NAME,
然后执行下面的可以删除对应的sql_profile
execdbms_sqltune.drop_sql_profile('coe_br1mv51g21cxw_3677037978');
该文章实验参考：http://blog.itpub.net/25380220/viewspace-697573/
http://blo g.itpub.net/25380220/viewspace-697583/
在不让应用修改SQL的前提下，解决SQL的执行计划差的问题。
1、对于执行计划变差的SQL，根据历史的执行计划，固定好的执行计划，从而达到优化的目的（ dbms_sqltune.accept_sql_profile ）
2、应用的SQL因为错加了hint导致走了差的执行计划， 在不能修改应用中的SQL的情况下，构造好的执行计划， 使SQL语句按指定的执行计划运行。（ dbms_sqltune.import_sql_profile ）
该文章主要 介绍：
★什么是SQL Profiles及其作用
★ 如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。 同时也介绍了SQL的signature。
一、初步了解 SQL Profiles
试验过程:
1、 创建测试表及其索引
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
2、查看当前的统计信息
SQL>select * from dba_tab_statistics where table_name=upper('&table_name');--新建的表当然没统计信息
3、对新建的表收集统计信息
SQL> exec dbms_stats.gather_table_stats('zhoushuai','t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('zhoushuai','t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
--此时已经有统计信息了
4、执行一条SQL，查看并分析其执行计划
SQL>select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
...................---这里结果省略
SQL> set autotrace trace
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| 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 2500 | 100K| 418 (1)| 00:00:06 | 《《《《《《《《《《《表链接为hash join
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 70 (0)| 00:00:01 | 《《《《《《《《《《《两表均为全表扫
| 3 | TABLE ACCESS FULL| T2 | 87274 | 937K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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)
Statistics
----------------------------------------------------------
38 recursive calls
0 db block gets
1544 consistent gets 《《《《注意逻辑读
1248 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> set autotrace off
PS：我们只注意关注返回的结果行数、执行计划以及逻辑读这些信息
分析：2个表都是全表扫，看过滤条件，T1有LIKE '%T1%'，显然不能走索引正常，T2也是全表，所以这里有优化空间
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2500行，即T1表总行数的5%，
如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join
5、修改SQL，分析Oracle优化器评估的index range_scan+nested loop的成本：
SQL> set autotrace trace
SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id; 2 3 4
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")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
290 consistent gets
14 physical reads
0 redo size
1777 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> set autotrace off
分析：可以看到Oracle优化器评估的成本为5071，远远高于原来的418。
但是加了HINT之后实际的逻辑读只有290，低于原始SQL的1544。
所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，
也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。
6、下面我们用Oracle的SQL Tuning Advisor来尝试优化这条SQL：
SQL> select sql_id,sql_text from v$sql where sql_text like 'select t1.*,t2.owner from t1,t2 where%'; ----找到SQL_ID
SQL_ID SQL_TEXT
--------------------------------------- ----------------------------------------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
SQL> var tuning_task varchar2(100);
SQL> DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id:='4zbqykx89yc8v';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task:=l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
END;
/
PL/SQL procedure successfully completed.
SQL> print tuning_task;
TUNING_TASK
------------------------
TASK_1833
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual; 《《《《《《《《《《《查看建议
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
----------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_1833
Tuning Task Owner : ZHOUSHUAI
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 04/05/2016 04:17:54
Completed at : 04/05/2016 04:17:59
-------------------------------------------------------------------------------
Schema Name: ZHOUSHUAI
SQL ID : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 79.99%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1833',
task_owner => 'ZHOUSHUAI', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .050657 .039519 21.98 %
CPU Time (s): .049892 .038494 22.84 %
User I/O Time (s): 0 0
Buffer Gets: 1494 296 80.18 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 36 36
Fetches: 36 36
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 36 | 1476 | 418 (1)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 36 | 1080 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 937K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
| 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 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")
-------------------------------------------------------------------------------
分析：上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。
结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为36， 相当地精确。
我们要做的就是Accept SQL Profile，接受这个SQL Profile。
7、使用这和profile
PS：这里重点强调，下面执行的这个比上边Recommendation的要多一个参数,force_match=>true，因为加上这个参数后，
功能类似于的cursor_sharing起到force效果，即使系统的是EXACT模式。
但是，注意，强烈建议该参数仅用于故障救急，稳定的生产环境最好不要加force_match=>true这个参数，否则会有意想不到的问题出现。
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1833',task_owner => 'ZHOUSHUAI', replace => TRUE, force_match=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace trace
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: 1022743391
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 142 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
| 2 | NESTED LOOPS | | 36 | 1476 | 142 (0)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 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 "SYS_SQLPROF_0153e2f3b7de0000" used for this statement 》》》》》》》这个名字是自动生成的
Statistics
----------------------------------------------------------
122 recursive calls
0 db block gets
477 consistent gets
3 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
36 rows processed
分析：Note中果然使用了profile SYS_SQLPROF_0153e2f3b7de0000
那我们在执行其他类似SQL：
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;
57 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 2337 | 184 (0)| 00:00:03 |
| 1 | NESTED LOOPS | | 57 | 2337 | 184 (0)| 00:00:03 |
| 2 | NESTED LOOPS | | 57 | 2337 | 184 (0)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 57 | 1710 | 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 '%T2%' AND "T1"."OBJECT_NAME" IS NOT NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_0153e2f3b7de0000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
301 consistent gets
0 physical reads
0 redo size
2361 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
57 rows processed
分析：这里可以看到T2条件也使用的该profile，但是如果Recommendation不加force_match=>true的话，就不会参考使用该profile。
8、查看profile相关内容
上面生成的profile名字为：SYS_SQLPROF_0153e2f3b7de0000
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
NAME CATEGORY SIGNATURE TYPE STATUS FORCE_MAT
-------------------------------------------------------- ------------------------------------------------------------
SYS_SQLPROF_0153e2f3b7de0000 DEFAULT 8.9755E+18 MANUAL ENABLED YES 《《《《FORCE_MAT 当不采用force_match=>true参数的时候，为NO
备注：查看相关profile内容可在sys.sqlobj$和sys.sqlobj$data视图中看到（11G）
总之， 一些复杂的SQL，我经常会先通过SQL Tuning Advisor来分析一下，看能不能让Oracle自已找出一个更好的执行计划。
我们来看看，SQL Profiles实际上是些什么：
SQL>col SIGNATURE for 999,999,999,999,999,999,999
SQL> select a.signature,category,name,hint,sql_text
from (
2 3 SELECT extractValue(value(h),'.') AS hint,
4 od.signature signature
5 FROM sys.sqlobj$data od, sys.sqlobj$ so,
6 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
7 WHERE so.signature = od.signature
8 AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id
) a,
dba_sql_profiles b
where a.signature=b.signature; 9 10 11 12 13
SIGNATURE CATEGORY NAME HINT SQL_TEXT
---------------------------- ---------- ---------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
8,975,541,025,552,400,288 DEFAULT SYS_SQLPROF_0153e2f3b7de0000 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
8,975,541,025,552,400,288 DEFAULT SYS_SQLPROF_0153e2f3b7de0000 OPTIMIZER_FEATURES_ENABLE(default) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
补充：10G可用下边SQL查看：
--select name,category,signature,type,status,force_matching from dba_sql_profiles;
--select * from sys.sqlprof$attr;
分析：signature可以理解为与sql_id、sql_hash_value类似的值，用来标识SQL。在10g以上的版本中，
查看v$sql的定义就可以发现2列：exact_matching_signature、force_matching_signature。
SQL> select rownum,a.* from
(select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text
from v$sql where sql_text like '%/*%xjs%' and sql_text not like '%v$sql%' order by 1) a; 2 3
ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT
---------- ---------------------------- ---------------------------- --------------- ----------------------------------------------------------
1 3,939,730,931,515,200,254 17,443,893,418,101,517,951 3617692013 select /* xjs */ object_name from T1 where object_name='t1'
2 10,964,210,455,693,560,558 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from T1 where rownum<=3
3 10,964,210,455,693,560,558 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from T1 where rownum<=3
4 11,217,690,300,719,901,571 354,482,119,692,997,204 3836375644 select /* xjs */ 2 from t1 where rownum<=1
5 11,974,975,582,747,367,847 354,482,119,692,997,204 3836375644 select /* xjs */ 1 from t1 where rownum<=1
6 12,941,882,703,651,921,406 17,443,893,418,101,517,951 3617692013 select /* xjs */ object_name from T1 where object_name='T1'
7 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from T1 where rownum<=1
8 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 select /* xjs */ OBJECT_NAME from T1 where rownum<=1
9 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 SELECT /* xjs */ object_name from T1 where rownum<=1
10 17,986,178,357,953,662,359 11,097,449,316,038,436,385 3836375644 select /* xjs */ object_name from t1 where rownum<=1
10 rows selected.
分析：
第2、3条SQL的exact_matching_signature相同，第7、8、9、10条SQL的exact_matching_signature相同。
第2、3条SQL的force_matching_signature相同，第4、5条SQL的force_matching_signature相同，
第7、8、9、10条的SQL的force_matching_signature相同。第1、6条SQL的force_matching_signature相同
结论：
对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。
对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。
但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。
SQL> select rownum,a.* from
(select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text
from v$sql where sql_text like '%/*%xjs2%' and sql_text not like '%v$sql%' order by 1) a; 2 3
ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT
---------- ---------------------------- ---------------------------- --------------- ----------------------------------------------------------------
1 5,363,536,431,331,905,229 5,363,536,431,331,905,229 3836375644 select /* xjs2 */ object_name from T1 where object_name='T1' and rownum<=:rn
2 5,363,536,431,331,905,229 5,363,536,431,331,905,229 3836375644 select /* xjs2 */ object_name from t1 where object_name='T1' and rownum<=:rn
3 12,992,689,086,515,482,106 12,992,689,086,515,482,106 3836375644 select /* xjs2 */ object_name from t1 where object_name='T2' and rownum<=:rn
可以看到，现在exact_matching_signature与force_matching_signature完全一样了。
从force_matching_signature的特性，我们可以想到一个用途：
用于查找没有使用绑定变量的SQL语句，类似于使用plan_hash_value来查找。
回到前面，accept_sql_profile这个过程，force_match参数设为TRUE，那么dba_sql_profiles中的signature则是由SQL的force_matching_signature而来， 否则便是exact_matching_signature。
对于Outlines来说，则只能是exact_matching_signature。从这个角度上讲，Sql Profiles比Outlines的使用范围更广，
因为Sql profiles对没有使用绑定变量的SQL也支持得很好。值得注意的是，Sql profiles的force_match属性是不能更改的，只能在创建时指定，如果要更改，则只能重新创建改Sql Profile。
下面来看看sys.sqlprof$attr数据字典。这里面没有SQL Profile的名字，而是用的sql的signature。
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
可以看到，SQL Profiles的attr_val实际上就是一些Hints，这跟Outlines没有本质上的区别。只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。
这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2500*0.0144刚好等于36。这里就是告诉Oracle优化器，
T1表经过谓语过滤后返回行数应该为评估的0.0144倍。
从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器。
看下面的测试：
SQL> exec dbms_stats.set_table_stats('ZHOUSHUAI','T1',numrows=>5000000);
PL/SQL procedure successfully completed.
SQL> @showplan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3600 | 144K| 455 (9)| 00:00:06 |
|* 1 | HASH JOIN | | 3600 | 144K| 455 (9)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 3600 | 105K| 107 (35)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 937K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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 "SYS_SQLPROF_0153e2f3b7de0000" used for this statement
21 rows selected.
将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回5000000*5%*0.0144=3600行。这里执行计划又变回为full scan+hash join。
可以看到，虽然SQL Profile起作用了，但是并没有锁定执行计划。
二、如何手工来为创建、生成SQL Profile，以及如何让SQL Profile也能像Outlines一样锁定SQL的执行计划，以保持SQL执行计划的稳定性。
1、 如何来手工创建SQL Profiles?
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
这个过程其并不仅仅IMPORT，根据REPLACE的选项可以实现 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)
2、下面我们就用这个过程来创建SQL PROFILE：
首先为避免干扰，前边测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0153e2f3b7de0000');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats('ZHOUSHUAI','T1',numrows=>49953);
PL/SQL procedure successfully completed.
完成后查看该表的信息：
SQL> @tabstat.sql
Please enter Name of Table Owner: zhoushuai
Please enter Table Name : t1
**********************************************************
Table Level
**********************************************************
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
T1 49,953 2,52 0 0 30 YES 50,000 04-13-2016
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OBJECT_ID 50,000 .00002000 1 0 50,000 04-05-2016
OBJECT_NAME 28,836 .00003468 1 0 50,000 04-05-2016
**********************************************************
Partition Level
**********************************************************
现在手工创建一个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 a.signature,category,name,hint,sql_text
2 from (
3 SELECT extractValue(value(h),'.') AS hint,
4 od.signature signature
5 FROM sys.sqlobj$data od, sys.sqlobj$ so,
6 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
7 WHERE so.signature = od.signature
8 AND so.category = od.category
9 AND so.obj_type = od.obj_type
10 AND so.plan_id = od.plan_id
11 ) a,
12 dba_sql_profiles b
13 where a.signature=b.signature;
SIGNATURE CATEGORY NAME HINT SQL_TEXT
---------------------- ------------ ----------------- ----------------------------------------------------------------------------------------------------------------------
3960696072677096522 DEFAULT SQLPROFILE_NAME1 USE_NL(T1 T2) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
3960696072677096522 DEFAULT SQLPROFILE_NAME1 INDEX(T2) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
分析：可以看到，该SQL已经配置了我们想要的hint的profile
3、查看执行计划
SQL> set autotrace trace
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 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 | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 2498 | 74940 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 767K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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
----------------------------------------------------------
0 recursive calls
0 db block gets
1493 consistent gets
1247 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
可以看到，SQL使用了SQL Profile SQLPROFILE_NAME1，不过没有达到我们预期的效果
分析： 看起来是SQL Profile使用的Hints有问题。
4.我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name”。
这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile
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 /
查看profile：
PL/SQL procedure successfully completed.
SQL> select a.signature,category,name,hint,sql_text
2 from (
3 SELECT extractValue(value(h),'.') AS hint,
4 od.signature signature
5 FROM sys.sqlobj$data od, sys.sqlobj$ so,
6 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
7 WHERE so.signature = od.signature
8 AND so.category = od.category
9 AND so.obj_type = od.obj_type
10 AND so.plan_id = od.plan_id
11 ) a,
12 dba_sql_profiles b
13 where a.signature=b.signature;
SIGNATURE CATEGORY NAME HINT SQL_TEXT
---------------------- ---------- ----------------- ------------------------------------------------------------------------------------------------------------------------------
3960696072677096522 DEFAULT SQLPROFILE_NAME1 USE_NL(T1@SEL$1 T2@SEL$1) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
3960696072677096522 DEFAULT SQLPROFILE_NAME1 INDEX(T2@SEL$1) select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
6、执行该sql查看执行计划：
SQL> set autotrace trace
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 | 9 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
298 consistent gets
0 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
分析：这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
7、修改统计信息测试：
那么我们再一次手工修改T1表的统计信息，看看结果如何：
SQL> exec dbms_stats.set_table_stats('ZHOUSHUAI','T1',numrows=>5000000);
PL/SQL procedure successfully completed.
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 9521K| 500K (1)| 01:40:03 |
| 1 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 9 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
298 consistent gets
0 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
分析：可以看到，Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大，
但是这里的执行计划仍然与未修改统计信息之前一样，使用range scan+ nested loop join。
通过以上的测试，我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用，同时也验证了这种方式的有效性，
SQL Profiles能够像Outlines一样，能够稳定SQL的执行计划。
三、接下来我们需要完成两个任务
任务一：对现有的SQL稳定其执行计划（针对执行计划不稳定的SQL）
这里的问题是：
稳定一条SQL语句的Hints从哪里来？
简单的sql，没问题，我们可以手工构造，但是复杂的SQL，手工构造相对比较复杂，同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。
从10g开始，v$sql_plan中就包括了SQL语句OUTLINE数据，也就是稳定执行计划的Hints。
从下面可以看到：
SQL> select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
执行完上边SQL后执行下边
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 77bdyugrbkj0y, 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 | | | | 500K(100)| |
| 1 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
| 2 | NESTED LOOPS | | 250K| 9521K| 500K (1)| 01:40:03 |
|* 3 | TABLE ACCESS FULL | T1 | 250K| 7324K| 107 (35)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 9 | 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脚本，来自MOS。
”SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]“，
在这篇文章中点击 [10.2, 11.1, 11.2, 12.1 download] 可以下载到sqlt.zip，这个压缩文件内有一个文件：coe_xfr_sql_profile.sql。
这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。
下面是示例：
QL> select s.SQL_ID,s.PLAN_HASH_VALUE,s.SQL_FULLTEXT from v$sql s where sql_text like 'select t1.*,t2.owner%';
SQL_ID PLAN_HASH_VALUE
--------------------------------------- ---------------
SQL_FULLTEXT
---------------------------------------------------------------
77bdyugrbkj0y 1022743391 ---这是要找的SQL_ID和PLAN_HASH_VALUE
---注意的是生产环境要选择你认为最优的那个作为下边脚本的输入值
select t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
或用下边这个命令查更准确确定哪个hash的执行计划最好，因为执行计划不稳定的会有多个PLAN_HASH_VALUE
/*
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='77bdyugrbkj0y' order by TIMESTAMP;
col options for a15
col operation for a20
col object_name for a20
select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
from DBA_HIST_SQL_PLAN
where sql_id ='77bdyugrbkj0y'
and plan_hash_value in (1022743391)
order by ID,TIMESTAMP;
*/
找到对应的SQL_ID和PLAN_HASH_VALUE
SQL> @coe_xfr_sql_profile.sql 77bdyugrbkj0y 1022743391
Parameter 1:
SQL_ID (required)
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "77bdyugrbkj0y"
PLAN_HASH_VALUE: "1022743391"
........................中间部分省略（会提示执行下边的内容）
SQL>@coe_xfr_sql_profile_77bdyugrbkj0y_1022743391.sql
执行完成后，查看profile大概内容为（可以用上边查看profile的脚本查看）：
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
c oe_xfr_sql_profile.sql这个脚本首先要求输入sql_id，然后从shared pool、awr中获取sql执行的各个执行计划的统计信息
（执行计划不稳定的SQL通常会有多个不同的执行计划），然后输入你认为是正确的、需要稳定的执行计划的hash value，
脚本就会生成另一个脚本，这里为coe_xfr_sql_profile_77bdyugrbkj0y_1022743391.sql，
然后运行这个脚本，就会创建出稳定执行计划所需要的SQL Profile，
SQL Profile的名字为：coe+sql_id+plan_hash_value，这里为coe_77bdyugrbkj0y_1022743391。
注意，这里创建的SQL Profile，force match默认为FALSE，我们可以手工修改脚本将其改为TRUE，
同时我们也可以按意愿来修改生成的脚本的其他内容。
任务二：不改sql，即使SQL已经使用hints。固定执行计划
常常遇到这样的情况，SQL语句其执行计划有问题，或者是SQL使用了错误的Hints（比如 /*+ RULE */）导致SQL性能较差，
但是应用又不能修改或者时间内不能修改，那么我们怎么来改变SQL的执行计划呢。
有3种办法，
一种是调整统计信息，这个不建议使用，因为比较复杂、不稳定可靠（统计信息可能会重新收集），影响面广（会影响其他访问此对象的SQL）。
二种是使用OUTLINE，这种方法比较复杂。
三种就是我们今天要介绍的使用SQL Profiles了。
使用SQL Profiles来改变SQL的执行计划，其本质上就是使用Hints来改变SQL的执行计划。
1、简单的SQL，我们同样可以像前面一样手工构造Hints然后再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE来实现。（但是这种方法还是略显烦琐）
2、取得原始SQL的文本（如有可能还包括sql id)
构造一个与原始SQL在逻辑上、结构上完全相同的SQL。
这里强制逻辑上和结构上相同，SQL解析的用户名、SQL中引用对象的用户名甚至是一些predicate条件都可以不同。（当然能够与原始SQL完全一样就更省事。）
执行我们构造的SQL，并取得构造的SQL的Outline Data。
使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile。
下面我们来演示一下整个过程。我们这里要修改执行计划的SQL是：
select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id;
SQL> select s.SQL_ID,s.PLAN_HASH_VALUE,s.SQL_FULLTEXT from v$sql s where sql_text like 'select /*+ orig_sql%';
SQL_ID PLAN_HASH_VALUE
--------------------------------------- ---------------
SQL_FULLTEXT
----------------------------------------------------------------------
gmvb9bp7f9kqd 1838229974
select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 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 | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 2498 | 97422 | 418 (1)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 2498 | 74940 | 70 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 87274 | 767K| 348 (1)| 00:00:05 |
---------------------------------------------------------------------------
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)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1493 consistent gets
1247 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 | 9 | 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")
Statistics
----------------------------------------------------------
36 recursive calls
0 db block gets
336 consistent gets
0 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
36 rows processed
SQL> select s.SQL_ID,s.PLAN_HASH_VALUE,s.SQL_FULLTEXT from v$sql s where sql_text like 'select /*+ modify_sql index(t1)%';
SQL_ID PLAN_HASH_VALUE
--------------------------------------- ---------------
SQL_FULLTEXT
--------------------------------------------------------
0vdkn8rysa3j1 1022743391
select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
1 row selected.
然后使用coe_xfr_sql_profile.sql脚本来提取我们 构造 的SQL的Outline Data，
生成的结果为coe_xfr_sql_profile_0vdkn8rysa3j1_1022743391.sql，打开结果文件，
可以看到有这么一段
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'[FULL(@"SEL$1" "T1"@"SEL$1")]',
q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
再针对gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql，生成的结果文件为coe_xfr_sql_profile_gmvb9bp7f9kqd_1838229974.sql。手工修改这个文件，将里面h := SYS.SQLPROF_ATTR…那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force_match从FALSE改成TRUE。
最后我们运行coe_xfr_sql_profile_gmvb9bp7f9kqd_1838229974.sql这个脚本文件：
SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;
SIGNATURE
---------------------
15409905709853673912
SIGNATUREF
---------------------
9964126883768520950
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_gmvb9bp7f9kqd_1838229974 completed
样就完成了我们所需要的SQL Profile的创建。
下面再看看原来的SQL执行情况（这里将like条件改了一下，以查看force match是否起作用）：
SQL> set autotrace trace
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T2%'
4 and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2498 | 97422 | 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 74940 | 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 | 9 | 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 "coe_gmvb9bp7f9kqd_1838229974" used for this statement
Statistics
----------------------------------------------------------
420 recursive calls
0 db block gets
713 consistent gets
1 physical reads
0 redo size
1766 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
51 sorts (memory)
0 sorts (disk)
36 rows processed
可以看到SQL Profile起作用了。
对于SQL Profiles来说，不属于任何一个用户，比Outlines更具有操控性灵活性。
--补充知识
DBMS_SQLTUNE.DROP_SQL_PROFILE( name =>'my_sql_profile' );
通过select * fromdba_sql_profiles a;查询出PROFILE 的NAME,
然后执行下面的可以删除对应的sql_profile
execdbms_sqltune.drop_sql_profile('coe_br1mv51g21cxw_3677037978');
该文章实验参考：http://blog.itpub.net/25380220/viewspace-697573/
http://blo g.itpub.net/25380220/viewspace-697583/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31043804/viewspace-2081512/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31043804/viewspace-2081512/
376
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
