Oracle 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执行计划的稳定性。

,
Trackback

32 comments untill now

  1. 沙发,顶熊哥

    好东西啊

  2. wangliang @ 2010-12-17 16:28

    即T1表总行数的5%?这个5% 是怎么来的呢?

    老熊 Reply:

    @wangliang, 这是Oracle在类似于like ‘%xxx’这样的谓词的默认选择率。

  3. 帅小伙 @ 2010-12-17 18:18

    这个东东在生产环境用过,那个dbms_sqltune.execute_tuning_task有时候运行时间很长,而且运行期间对生产库影响较大,有时候不能生成正确的profile,老熊有啥好建议没?

    老熊 Reply:

    @帅小伙, 如果遇到这样的情况,我就会自己来优化SQL而不要依赖于SQL Tuning Advisor了。

  4. dbms_sqltune我也用过 感觉不太靠谱。。。

    熊哥文章出的太慢了 最好是隔几天就来一篇噻

    老熊 Reply:

    @roger, 我也希望能够多写点,呵呵。只是与文章需要静心,现在手上事情太多,也不容易静下心来。

  5. 熊哥 你每天不要想歪的噻。。。。哈哈

  6. 用sql profile主要是有时不能得到自己想要的执行计划,而用outline还可以加下HINT。之前测过SQL Profile用在Bind Peeking时,没能固化住,期待下一篇

  7. outline产生library cache latch,我猜熊哥估计是用了ALTER SYSTEM SET USE_STORED_OUTLINES。。。。。其实如果之前已经有category,只要outline change category,就可以用上OUTLINE,而且不会产生竞争。

    老熊 Reply:

    @OoNiceDream, 当时是在那个系统上第1次用OUTLINE,之前没有启用过OUTLINE

  8. 之前也一直为sql profiles的执行计划稳固性伤脑筋,《Pro Oracle SQL》的作者给出了用dbms_sqltune.import_sql_profile的方法进行手工创建sql profiles并lock sql plan的方法,既具备了outline稳固计划的效果,又起了非绑定变量环境outline所不能,前几次在项目中调优过,效果不错

    老熊 Reply:

    @easyora, 的确如此。这也是下一篇我着重要介绍的。

  9. 哈哈,easyora提供了好东东

  10. […] 在上一篇《SQL Profiles-Part I》,我向大家介绍了什么是SQL Profiles及其作用,如何使用SQL Tuning Advisor来生成SQL Profile,以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天,将向大家介绍如何手工创建SQL Profiles(即不通过SQL Tuning Advisor)来达成2个目的: […]

  11. jiangxiaoyu @ 2011-02-03 18:00

    老熊前辈
    我按照您的步骤在运行到
    SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;的时候
    结果为空。大概是什么情况呢?

  12. jiangxiaoyu @ 2011-02-03 18:57

    但是在相应的$ADVISOR_PROGRESS的METRIC1_DESC却找到了这样一句话
    “Benefit so far (processed statements only” 这个是不是说明不需要优化呢?

  13. 被人遗忘 @ 2011-05-04 11:23

    你这个实验我觉得有2点问题要请教下,首先你用LIKE去查询的话,执行计划估算出来时2498行,所以执行计划才用HJ去连接,所以我觉得PROFILE是没有建议的,不知道你这个建议怎么生成的!只是个人认为!

    老熊 Reply:

    @被人遗忘, 不太明白”PROFILE是没有建议的”是什么意思。优化建议是由SQL Tuning Advisor给出,从给出的建议看是用了NL的连接。

  14. 被人遗忘 @ 2011-05-04 15: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没有给出建议,我理解是这样,不知道对不对,请指教

  15. 不明白你这里的REPORT为啥没有输出完。在输出REPORT前,请执行一下:
    set long 1000000

    runtime optimizer 认为T1有2500条数据,所以不会走NL。关键是这里2500是过高地估计了cardinality。
    但是SQL Tuning Advisor用的是automatic tuning optimizer,会发现2500估计过高,所以会进行修正。

  16. 高手啊 学习了

  17. myownstars @ 2011-11-09 16: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:

    你在sqlplus里面desc dbms_sqltune可以看到,这个包下面的create_tuning_task过程有多个重载的版本。

  18. 老熊写的很详细,很通俗易懂,学习了。。。

  19. Hi 老熊,

    最近也在測試這一段,但我執行到@showplan,會找不到showplan.sql這個檔案,可否告知一下,TKS

    Tom

    老熊 Reply:

    showplan是一个简单的SQL文件,内容为:
    select * from table(dbms_xplan.display);

  20. 系统升级到11g后,执行计划飘,考虑用sql profile方式来稳固,临时解决问题。如何?

  21. coolbond @ 2014-05-12 14:06

    老熊,我是oracle11g 11.2.3,请问为何没有数据字典:sys.sqlprof$attr?我无法查询sql_profile的attr和value

    老熊 Reply:

    11g是如下几个字典文件:
    SQLOBJ$、SQLOBJ$AUXDATA、SQLOBJ$DATA

  22. coolbond @ 2014-05-16 11:04

    谢谢老熊,对的,查询profile具体的value,11g使用以下SQL
    select a.name,b.COMP_DATA from dba_sql_profiles a, SQLOBJ$DATA b
    where a.signature = b.signature
    and a.name=’SYS_SQLPROF_0145ab8a9f860002′;
    profle_name根据实际修改

  23. wangliang @ 2014-08-18 17:23

    我看了下,1和6的语句,应该是exact_matching_signature吧,怎么是FORCE_MATCHING_SIGNATURE呢?两个语句就差了个大小写啊?

Add your comment now