SQL Profiles-Part I
Oracle性能优化performance tuning12月 15th, 2010Oracle 11g从发布到现在，也有几个年头了。而在国内来说，Oracle 10g仍然是主流，甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性，SQL Profiles被使用得并不太多。不管是在论坛、个人的BLOG还是其他一些地方，SQL Profiles的介绍也相对较少。对我个人来说，已经在多个优化场合中使用SQL Profiles，在这里向大家介绍SQL Profiles，就是希望能够了解Oracle数据库的这一功能。
SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最重要的有2点：
- SQL Profiles更容易生成、更改和控制。
- SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。
关于这2方面的优点，我后面会详细地阐述。
现在我在使用Outlines的场合，均使用SQL Profiles来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的library cache latch的争用，不得不关闭Outline的使用，但是改用SQL Profiles不再有这个问题。这或许是个BUG，不过既然能用SQL Profiles代替，也就没再深入去研究这个问题。
使用SQL Profiles无非是两个目的：
- 锁定或者说是稳定执行计划。
- 在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。
那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。
首先建2个测试表：
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000; 表已创建。 SQL> create table t2 as select * from dba_objects; 表已创建。 SQL> create index t2_idx on t2(object_id); 索引已创建。 SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1'); PL/SQL 过程已成功完成。 SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1'); PL/SQL 过程已成功完成。
然后看看下面这一条SQL：
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
已选择29行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 99920 | 219 (4)| 00:00:03 |
|* 1 | HASH JOIN | | 2498 | 99920 | 219 (4)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 2498 | 72442 | 59 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
932 consistent gets
0 physical reads
0 redo size
1352 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
29 rows processed
这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。
首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like ‘%T1%’这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2498行，即T1表总行数的5%，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本：
SQL> explain plan for 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;
已解释。
SQL> @showplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2498 | 99920 | 5061 (1)| 00:01:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2498 | 99920 | 5061 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2498 | 72442 | 59 (6)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
从执行计划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。
但是实际的逻辑读是多少呢？
统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 290 consistent gets 0 physical reads 0 redo size 1352 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 29 rows processed
加了HINT之后实际的逻辑读只有290，低于原始SQL的932。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。
下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL：
SQL> var tuning_task varchar2(100);
SQL> DECLARE
2 l_sql_id v$session.prev_sql_id%TYPE;
3 l_tuning_task VARCHAR2(30);
4 BEGIN
5 l_sql_id:='4zbqykx89yc8v';
6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
7 :tuning_task:=l_tuning_task;
8 dbms_sqltune.execute_tuning_task(l_tuning_task);
9 dbms_output.put_line(l_tuning_task);
10 END;
11 /
任务_74
PL/SQL 过程已成功完成。
SQL> print tuning_task;
TUNING_TASK
---------------------------------------------------------------------------------------------------------
任务_74
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 任务_74
Tuning Task Owner : TEST1
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 12/15/2010 09:56:02
Completed at : 12/15/2010 09:56:03
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: TEST1
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)
--------------------------------------------------------
为此语句找到了性能
Recommendation (estimated benefit: 46.62%)
------------------------------------------
-考虑接受推荐的 SQL
executedbms_sqltune.accept_sql_profile(task_name => '任务_74', replace =
TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 1160 | 219 (4)| 00:00:03 |
|* 1 | HASH JOIN | | 29 | 1160 | 219 (4)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 29 | 841 | 59 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
2- Using SQL Profile
--------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 1160 | 117 (3)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 29 | 1160 | 117 (3)| 00:00:02
|
|* 3 | TABLE ACCESS FULL | T1 | 29 | 841 | 59 (6)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。
结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为29，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。
SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true); PL/SQL 过程已成功完成。
那么我们再执行其他的类似SQL看看：
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;
已选择77行。
执行计划
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 1160 | 117 (3)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 29 | 1160 | 117 (3)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 29 | 841 | 59 (6)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T2%')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_014b39f084c88000" used for this statement
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
343 consistent gets
0 physical reads
0 redo size
2840 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77 rows processed
这一次，尽管我们更改了LIKE 后面的值，但是执行计划与SQL Tuning Advisor产生的执行计划完全一样。从执行计划的”Note“一节也可以看到，SQL Profile起作用了。SQL Profile的名字为”SYS_SQLPROF_014b39f084c88000″。
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles; NAME CATEGORY SIGNATURE TYPE STATUS FOR ------------------------------ ------------------------------ --------------------- --------- ---------- --- SYS_SQLPROF_014b39f084c88000 DEFAULT 3960696072677096522 MANUAL ENABLED YES
一些复杂的SQL，我经常会先通过SQL Tuning Advisor来分析一下，看能不能让Oracle自已找出一个更好的执行计划。
我们来看看，SQL Profiles实际上是些什么：
SQL< select * from sys.sqlprof$attr; SIGNATURE CATEGORY ATTR# ATTR_VAL --------------------- ------------------------------ ---------- ---------------------------------------- 3960696072677096522 DEFAULT 1 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$ 1", SCALE_ROWS=0.01161091426)
从sys.sqlprof$attr这个数字字典里面，我们可以看到两样东西：signature和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; ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT ---------- ------------------------ ------------------------ --------------- -------------------------------------------------- 1 3939730931515200254 17443893418101517951 3617692013 select /* xjs */ object_name from T1 where obje ct_name='t1' 2 10964210455693560558 11097449316038436385 3836375644 select /* xjs */ object_name from T1 where rown um<=3 3 10964210455693560558 11097449316038436385 3836375644 select /* xjs */ object_name from T1 where ro wnum<=3 4 11217690300719901571 354482119692997204 3836375644 select /* xjs */ 2 from t1 where rownum<=1 5 11974975582747367847 354482119692997204 3836375644 select /* xjs */ 1 from t1 where rownum<=1 6 12941882703651921406 17443893418101517951 3617692013 select /* xjs */ object_name from T1 where obje ct_name='T1' 7 17986178357953662359 11097449316038436385 3836375644 select /* xjs */ object_name from T1 where rown um<=1 8 17986178357953662359 11097449316038436385 3836375644 select /* xjs */ OBJECT_NAME from T1 where rownum< =1 9 17986178357953662359 11097449316038436385 3836375644 SELECT /* xjs */ object_name from T1 where rown um<=1 10 17986178357953662359 11097449316038436385 3836375644 select /* xjs */ object_name from t1 where rownum< =1
从上面的数据可以看出：
- 第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; ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT ---------- ------------------------ ------------------------ --------------- -------------------------------------------------- 1 5363536431331905229 5363536431331905229 3836375644 select /* xjs2 */ object_name from T1 where obj ect_name='T1' and rownum<=:rn 2 5363536431331905229 5363536431331905229 3836375644 select /* xjs2 */ object_name from t1 where obj ect_name='T1' and rownum<=:rn 3 12992689086515482106 12992689086515482106 3836375644 select /* xjs2 */ object_name from t1 where obj ect_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。大家从attr_val的结果发现了什么？
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.01161091426)
可以看到，SQL Profiles的attr_val实际上就是一些Hints，这跟Outlines没有本质上的区别。只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2498*0.01161091426正好为29。这里就是告诉Oracle优化器，T1表经过谓语过滤后返回行数应该为评估的0.01161091426倍。从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器。看下面的测试：
SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000);
PL/SQL 过程已成功完成。
SQL> explain plan for select t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
4 and t1.object_id=t2.object_id;
已解释。
SQL> @showplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2903 | 113K| 448 (53)| 00:00:06 |
|* 1 | HASH JOIN | | 2903 | 113K| 448 (53)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T1 | 2903 | 84187 | 288 (81)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
Note
-----
- SQL profile "SYS_SQLPROF_014b39f084c88000" used for this statement
将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回5000000*5%*0.01161091426=2903行。这里执行计划又变回为full scan+hash join。可以看到，虽然SQL Profile起作用了，但是并没有锁定执行计划。
小结：本文简单介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。
下一篇将会介绍如何手工来为创建、生成SQL Profile，以及如何让SQL Profile也能像Outlines一样锁定SQL的执行计划，以保持SQL执行计划的稳定性。
12月 15th, 2010 at 9:11 下午
沙发，顶熊哥
好东西啊
12月 17th, 2010 at 4:28 下午
即T1表总行数的5%?这个5% 是怎么来的呢?
老熊 Reply:
12月 17th, 2010 at 6:02 下午
@wangliang, 这是Oracle在类似于like ‘%xxx’这样的谓词的默认选择率。
12月 17th, 2010 at 6:18 下午
这个东东在生产环境用过，那个dbms_sqltune.execute_tuning_task有时候运行时间很长，而且运行期间对生产库影响较大，有时候不能生成正确的profile，老熊有啥好建议没？
老熊 Reply:
12月 17th, 2010 at 6:20 下午
@帅小伙, 如果遇到这样的情况，我就会自己来优化SQL而不要依赖于SQL Tuning Advisor了。
12月 19th, 2010 at 11:34 上午
dbms_sqltune我也用过 感觉不太靠谱。。。
熊哥文章出的太慢了 最好是隔几天就来一篇噻
老熊 Reply:
12月 19th, 2010 at 11:45 上午
@roger, 我也希望能够多写点，呵呵。只是与文章需要静心，现在手上事情太多，也不容易静下心来。
12月 20th, 2010 at 12:39 下午
熊哥 你每天不要想歪的噻。。。。哈哈
12月 21st, 2010 at 6:32 下午
用sql profile主要是有时不能得到自己想要的执行计划，而用outline还可以加下HINT。之前测过SQL Profile用在Bind Peeking时，没能固化住，期待下一篇
12月 21st, 2010 at 6:36 下午
outline产生library cache latch，我猜熊哥估计是用了ALTER SYSTEM SET USE_STORED_OUTLINES。。。。。其实如果之前已经有category,只要outline change category,就可以用上OUTLINE，而且不会产生竞争。
老熊 Reply:
12月 21st, 2010 at 9:16 下午
@OoNiceDream, 当时是在那个系统上第1次用OUTLINE，之前没有启用过OUTLINE
12月 22nd, 2010 at 2:05 下午
之前也一直为sql profiles的执行计划稳固性伤脑筋，《Pro Oracle SQL》的作者给出了用dbms_sqltune.import_sql_profile的方法进行手工创建sql profiles并lock sql plan的方法，既具备了outline稳固计划的效果，又起了非绑定变量环境outline所不能，前几次在项目中调优过，效果不错
老熊 Reply:
12月 23rd, 2010 at 11:49 上午
@easyora, 的确如此。这也是下一篇我着重要介绍的。
12月 25th, 2010 at 2:33 下午
哈哈，easyora提供了好东东
12月 30th, 2010 at 3:50 下午
[...] 在上一篇《SQL Profiles-Part I》，我向大家介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天，将向大家介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)来达成2个目的： [...]
2月 3rd, 2011 at 6:00 下午
老熊前辈
我按照您的步骤在运行到
SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;的时候
结果为空。大概是什么情况呢？
2月 3rd, 2011 at 6:57 下午
但是在相应的$ADVISOR_PROGRESS的METRIC1_DESC却找到了这样一句话
“Benefit so far (processed statements only” 这个是不是说明不需要优化呢？
5月 4th, 2011 at 11:23 上午
你这个实验我觉得有2点问题要请教下，首先你用LIKE去查询的话，执行计划估算出来时2498行，所以执行计划才用HJ去连接，所以我觉得PROFILE是没有建议的，不知道你这个建议怎么生成的！只是个人认为!
老熊 Reply:
5月 4th, 2011 at 12:01 下午
@被人遗忘, 不太明白”PROFILE是没有建议的”是什么意思。优化建议是由SQL Tuning Advisor给出，从给出的建议看是用了NL的连接。
5月 4th, 2011 at 3:04 下午
不好意思，是我没有说清楚,请看我的疑惑的地方！
表都和你一样，执行计划如下：
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like ‘%T1%’ and t1.object_id=t2.object_id;
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 97K| 220 (3)| 00:00:03 |
|* 1 | HASH JOIN | | 2500 | 97K| 220 (3)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 72500 | 58 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 50418 | 541K| 160 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(”T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 - filter(”T1″.”OBJECT_NAME” LIKE ‘%T1%’)
Statistics
----------------------------------------------------------
160 recursive calls
0 db block gets
959 consistent gets
0 physical reads
0 redo size
1402 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
26 rows processed
然后加HINT的操作
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
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 97K| 5064 (1)| 00:01:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2500 | 97K| 5064 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 72500 | 58 (4)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(”T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 - access(”T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
Statistics
----------------------------------------------------------
71 recursive calls
0 db block gets
294 consistent gets
0 physical reads
0 redo size
1402 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
26 rows processed
SQL> var tuning_task varchar2(100);
SQL> DECLARE
2 l_sql_id v$session.prev_sql_id%TYPE;
3 l_tuning_task VARCHAR2(30);
4 BEGIN
5 l_sql_id:=’4y91nvb5b0a95′;
6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
7 :tuning_task:=l_tuning_task;
8 dbms_sqltune.execute_tuning_task(l_tuning_task);
9 dbms_output.put_line(l_tuning_task);
10 END;
11 /
SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual；
PL/SQL procedure successfully completed.
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
我认为这里SQL Tuning Advisor不会给出建议，我有下面的理由，因为执行计划里面发现T1有2500条数据，这个时候用NL连接的话，执行计划认为并不是最优的，所以SQL Tuning Advisor没有给出建议，我理解是这样，不知道对不对，请指教
5月 4th, 2011 at 6:26 下午
不明白你这里的REPORT为啥没有输出完。在输出REPORT前，请执行一下：
set long 1000000
runtime optimizer 认为T1有2500条数据，所以不会走NL。关键是这里2500是过高地估计了cardinality。
但是SQL Tuning Advisor用的是automatic tuning optimizer，会发现2500估计过高，所以会进行修正。
10月 20th, 2011 at 8:35 下午
高手啊 学习了
11月 9th, 2011 at 4:50 下午
老熊，我看了新出版的 《DBA手记 3》，书里的案例和你web页面上都是用到
dbms_sqltune.create_tuning_task(sql_id =>…)，
但是我在10g/11g调用，只有sql_text选项，没有sql_id啊？
dbms_sqltune.create_tuning_task(sql_text =>
老熊 Reply:
11月 9th, 2011 at 5:24 下午
你在sqlplus里面desc dbms_sqltune可以看到，这个包下面的create_tuning_task过程有多个重载的版本。
4月 20th, 2012 at 5:04 下午
老熊写的很详细，很通俗易懂，学习了。。。