Oracle Sql Profile

SQL Profiles-Part I

http://www.laoxiong.net/sql-profiles-part.html

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 过程已成功完成。  
 
然后看看下面这一条SQLSQL> 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执行计划的稳定性。

SQL Profiles-PartII

http://www.laoxiong.net/sql-profiles-partii.html

在上一篇《SQL Profiles-Part I》,我向大家介绍了什么是SQL Profiles及其作用,如何使用SQL Tuning Advisor来生成SQL Profile,以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天,将向大家介绍如何手工创建SQL Profiles(即不通过SQL Tuning Advisor)来达成2个目的:
锁定或者说稳定SQL执行计划。
在不能修改应用的SQL的情况下,来改变或者说是强制使SQL使用我们指定的执行计划,即使原始的SQL包含了Hints。
那么,这里最关键的一点是,如何来手工创建SQL Profiles?
答案是,正如上一篇中有朋友的留言,使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。

SQL> desc dbms_sqltune  
...  
PROCEDURE IMPORT_SQL_PROFILE  
参数名称                       类型                    输入/输出默认值?  
------------------------------ ----------------------- ------ --------  
 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  
...

这个过程其名字与实际功能有所差异,其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR,这种类型其实就是VARCHAR2的集合类型(COLLECTION):

SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS';  
 
TYPE     sqlprof_attr  
 AS VARRAY(2000) of VARCHAR2(500)

下面我们就用这个过程来创建SQL PROFILE:
为避免干扰,将上一篇测试中生成的SQL Profile删除掉,同时恢复T1表的统计信息中的表行数:

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');  
 
PL/SQL 过程已成功完成。  
 
SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953);  
 
PL/SQL 过程已成功完成。

现在我们手工创建一个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 过程已成功完成。  
 
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b  
  2  where a.signature = b.signature  
  3  and a.name='SQLPROFILE_NAME1';  
 
ATTR_VAL  
----------------------------------------  
USE_NL(T1 T2)  
INDEX(T2)

下面执行SQL Profiles对应的SQL:

SQL> select  t1.*,t2.owner  
  2       from t1,t2  
  3       where t1.object_name like '%T1%'  
  4       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%')  
 
Note  
-----  
   - SQL profile "SQLPROFILE_NAME1" used for this statement  
 
统计信息  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        933  consistent gets

可以看到,SQL使用了SQL Profile,不过没有达到我们预期的效果。
看起来是SQL Profile使用的Hints有问题。我们重新设置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  /  
 
PL/SQL 过程已成功完成。  
 
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b  
  2  where a.signature = b.signature  
  3  and a.name='SQLPROFILE_NAME1';  
 
ATTR_VAL  
----------------------------------------  
USE_NL(T1@SEL$1 T2@SEL$1)  
INDEX(T2@SEL$1)

再次执行下面的SQL:

SQL> select  t1.*,t2.owner  
  2       from t1,t2  
  3       where t1.object_name like '%T1%'  
  4       and t1.object_id=t2.object_id;  
 
已选择29行。  
 
执行计划  
----------------------------------------------------------  
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")  
 
Note  
-----  
   - SQL profile "SQLPROFILE_NAME1" used for this statement  
 
统计信息  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        294  consistent gets

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
那么我们再一次手工修改T1表的统计信息,看看结果如何:

SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000);  
 
PL/SQL 过程已成功完成。  
 
SQL> select  t1.*,t2.owner  
  2       from t1,t2  
  3       where t1.object_name like '%T1%'  
  4       and t1.object_id=t2.object_id;  
 
已选择29行。  
 
执行计划  
----------------------------------------------------------  
Plan hash value: 3787413387  
--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
|   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
|*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
|*  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")  
 
Note  
-----  
   - SQL profile "SQLPROFILE_NAME1" used for this statement  
 
统计信息  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        294  consistent gets

可以看到,Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大,但是这里的执行计划仍然与未修改统计信息之前一样,使用range scan+ nested loop join。
通过以上的测试,我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用,同时也验证了这种方式的有效性,SQL Profiles能够像Outlines一样,能够稳定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;  
 
已选择29行。  
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));  
 
PLAN_TABLE_OUTPUT  
----------------------------------------------------------------------------------------  
SQL_ID  6m45w7r0xgdfj, child number 0  
-------------------------------------  
select  t1.*,t2.owner      from t1,t2      where t1.object_name like '%T1%'  
   and t1.object_id=t2.object_id  
 
Plan hash value: 3787413387  
 
--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |       |       |  5061 (100)|          |  
|   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 |  
--------------------------------------------------------------------------------------  
 
Outline Data  
-------------  
 
  /*+  
      BEGIN_OUTLINE_DATA  
      IGNORE_OPTIM_EMBEDDED_HINTS  
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')  
      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")  
      END_OUTLINE_DATA  
  */  
 
Predicate Information (identified by operation id):  
---------------------------------------------------  
 
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
 
Note  
-----  
   - SQL profile "SQLPROFILE_NAME1" used for this statement

上面所显示的“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]“,在这篇文章中,可以下载到sqlt.zip,这个压缩文件内有一个文件:coe_xfr_sql_profile.sql。这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。下面是示例:

SQL> select /*+ proftest1 */ t1.*,t2.owner  
  2       from t1,t2  
  3       where t1.object_name like '%T1%'  
  4       and t1.object_id=t2.object_id;  
 
--在shared pool中查找刚刚执行的SQL,其IDb4zvp712np1bp--  
 
SQL> @coe_xfr_sql_profile.sql  
 
Parameter 1:  
SQL_ID (required)  
 
输入 1 的值:  b4zvp712np1bp  
 
PLAN_HASH_VALUE AVG_ET_SECS  
--------------- -----------  
     2959412835        .112  
 
Parameter 2:  
PLAN_HASH_VALUE (required)  
 
输入 2 的值:  2959412835  
 
Values passed:  
~~~~~~~~~~~~~  
SQL_ID         : "b4zvp712np1bp"  
PLAN_HASH_VALUE: "2959412835"  
 
Execute coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql  
on TARGET system in order to create a custom SQL Profile  
with plan 2959412835 linked to adjusted sql_text.  
 
COE_XFR_SQL_PROFILE completed.  
SQL>@coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql  
 
PL/SQL 过程已成功完成。  
 
SQL>WHENEVER SQLERROR CONTINUE  
SQL>SET ECHO OFF;  
 
            SIGNATURE  
---------------------  
  6058051510930011685  
 
... manual custom SQL Profile has been created  
 
COE_XFR_SQL_PROFILE_b4zvp712np1bp_2959412835 completed  
 
SQL>select attr_val from sys.sqlprof$attr where signature=6058051510930011685;  
 
ATTR_VAL  
--------------------------------------------------  
BEGIN_OUTLINE_DATA  
IGNORE_OPTIM_EMBEDDED_HINTS  
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')  
ALL_ROWS  
OUTLINE_LEAF(@"SEL$1")  
FULL(@"SEL$1" "T2"@"SEL$1")  
FULL(@"SEL$1" "T1"@"SEL$1")  
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")  
USE_HASH(@"SEL$1" "T1"@"SEL$1")  
END_OUTLINE_DATA

coe_xfr_sql_profile.sql这个脚本首先要求输入sql id,然后从shared pool、awr中获取sql执行的各个执行计划的统计信息(执行计划不稳定的SQL通常会有多个不同的执行计划),然后输入你认为是正确的、需要稳定的执行计划的hash value,脚本就会生成另一个脚本,这里为coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql,然后运行这个脚本,就会创建出稳定执行计划所需要的SQL Profile,SQL Profile的名字为:coe+sql_id+plan_hash_value,这里为coe_b4zvp712np1bp_2959412835。注意,这里创建的SQL Profile,force match默认为FALSE,我们可以手工修改脚本将其改为TRUE,同时我们也可以按意愿来修改生成的脚本的其他内容。
除了上面提到的脚本,http://kerryosborne.oracle-guy.com这个BLOG里面也有许多与SQL Profiles相关的脚本。其中create_sql_profile.sql可完成类似的功能,只不过功能相对简单,只能从shared pool中生成SQL Profile,因此也更方便。
任务二:在不能修改SQL的情况下改变并固定SQL的执行计划,即使原始的SQL使用了Hints。
常常遇到这样的情况,SQL语句其执行计划有问题,或者是SQL使用了错误的Hints(比如 /*+ RULE */)导致SQL性能较差,但是应用又不能修改或者时间内不能修改,那么我们怎么来改变SQL的执行计划呢。有3种办法,一种是调整统计信息,这个不建议使用,因为比较复杂、不稳定可靠(统计信息可能会重新收集),影响面广(会影响其他访问此对象的SQL)。第二种是使用OUTLINE,这种方法比较复杂。第三种就是我们今天要介绍的使用SQL Profiles了。
使用SQL Profiles来改变SQL的执行计划,其本质上就是使用Hints来改变SQL的执行计划。对于简单的SQL,我们同样可以像前面一样手工构造Hints然后再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE来实现。但是这种方法还是略显烦琐。那么通常的方法就是”乾坤大挪移“了:
取得原始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,然后取得的SQL ID为gmvb9bp7f9kqd:

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;  
 
已选择29行。  
 
执行计划  
----------------------------------------------------------  
Plan hash value: 2959412835  
 
-----------------------------------------------------------------------------------  
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |   250K|  9765K|       |   998  (25)| 00:00:12 |  
|*  1 |  HASH JOIN         |      |   250K|  9765K|  1128K|   998  (25)| 00:00:12 |  
|   2 |   TABLE ACCESS FULL| T2   | 49954 |   536K|       |   159   (2)| 00:00:02 |  
|*  3 |   TABLE ACCESS FULL| T1   |   250K|  7080K|       |   288  (81)| 00:00:04 |  
-----------------------------------------------------------------------------------

然后我们构造一条SQL,让这条SQL按我们希望的执行计划运行,构造的SQL其ID为cymak300cycmd:

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;  
 
已选择29行。  
 
执行计划  
----------------------------------------------------------  
Plan hash value: 3787413387  
 
--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
|   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
|*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
|*  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")

然后使用coe_xfr_sql_profile.sql脚本来提取我们构造的SQL的Outline Data,生成的结果为coe_xfr_sql_profile_cymak300cycmd_3787413387.sql,打开结果文件,可以看到有这么一段:

h := SYS.SQLPROF_ATTR(  
q'[BEGIN_OUTLINE_DATA]',  
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',  
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]',  
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'[END_OUTLINE_DATA]');

再针对gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql,生成的结果文件为coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql。手工修改这个文件,将里面h := SYS.SQLPROF_ATTR…那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force_match从FALSE改成TRUE。
最后我们运行coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql这个脚本文件:

SQL>@coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql  
 
PL/SQL 过程已成功完成。  
 
SQL>WHENEVER SQLERROR CONTINUE  
SQL>SET ECHO OFF;  
 
            SIGNATURE  
---------------------  
 15409905709853673912  
 
... manual custom SQL Profile has been created  
 
COE_XFR_SQL_PROFILE_gmvb9bp7f9kqd_2959412835 completed

这样就完成了我们所需要的SQL Profile的创建。下面再看看原来的SQL执行情况(这里我故意将like条件改了一下,以查看force match是否起作用):

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;  
 
已选择77行。  
 
执行计划  
----------------------------------------------------------  
Plan hash value: 3787413387  
 
--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
|   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
|*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
|*  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 "coe_gmvb9bp7f9kqd_2959412835" used for this statement  
 
统计信息  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        363  consistent gets

可以看到SQL Profile起作用了。
最后一步,生成SQL Profile时稍显复杂,不过我们可以修改之前提到的create_sql_profile.sql文件来达到同样的目的,只不过前几个步骤仍然是不可省略的。将里面的代码:

select extractvalue(value(d), '/hint') as outline_hints bulk collect  
  into ar_profile_hints  
  from xmltable('/*/outline_data/hint' passing  
                (select xmltype(other_xml) as xmlval  
                   from v$sql_plan  
                  where sql_id = '&&sql_id'  
                    and child_number = &&child_no  
                    and other_xml is not null)) d;

改为
select extractvalue(value(d), '/hint') as outline_hints bulk collect  
  into ar_profile_hints  
  from xmltable('/*/outline_data/hint' passing  
                (select xmltype(other_xml) as xmlval  
                   from v$sql_plan  
                  where sql_id = '&&modi_sql_id'  
                    and child_number = &&modi_child_no  
                    and other_xml is not null)) d;

注意这里modi_sql_id和modi_child_no为我们构造的SQL执行后的id及child_number。同时这2个变量在文件前面需要定义,此处不再细述。
小结:本文承接上一篇,介绍了如何利用SQL Profile来稳定执行计划;如何利用SQL Profile来改变SQL的执行计划。对于SQL Profiles来说,不属于任何一个用户,比Outlines更具有操控性灵活性。对于SQL Profiles的category,这里不做介绍,有兴趣的朋友请参考文档。

SQL Profiles使用实例

Reference:
http://www.muzijiang.cn/index.php/2010/07/sql_profiles_usage_sample/

在统计信息不完整或者缺失时,有时优化器会产生错误的判断,产生性能比较低下的执行计划。传统的方式,通过手工添加相关的提示生成正确的执行计划。但对于那些封装好的应用程序,修改代码不是一件容易的事情,相信这个问题也曾经困扰过很多的DBA。在Oracle10g中可以很方便的使用自动SQL调整(Automatic SQL Tuning)来调整这些SQL语句,而不用去修改应用代码。其中使用的关键技术就是强大的SQL Profiles。

下面通过一个小例子,看看SQL Profiles是如何进行SQL语句自动调整的。

1. 使用HR用户登录,创建测试用例

SQL> create table test (n number );
Table created.
SQL> begin
       for i in 1 .. 10000 loop
         insert into test values(i);
         commit;
       end loop;
     end; 
/
PL/SQL procedure successfully completed.

2. 创建索引并进行分析

SQL> create index test_idx on test(n);
Index created.
SQL> analyze table test estimate statistics;
Table analyzed.

3. 执行SQL语句,模拟一个性能低下的执行计划

SQL> select /*+ no_index(test test_idx) */ * from hr.test where n=1;
Execution Plan
———————————————————-
Plan hash value: 1357081020
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     6   (0)| 00:00:01 |
————————————————————————–

4. 使用SYS用户登录,创建自动调整任务

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext := ‘select /*+ no_index(test test_idx) */ * from hr.test where n=1′;
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         user_name   => ‘SCOTT’,
         scope       => ‘COMPREHENSIVE’,
         time_limit  => 60,
         task_name   => ‘tuning_task_1′,
         description => ‘Task to tune a query on a specified table’);
END;
/
PL/SQL procedure successfully completed.

5. 执行自动调整任务并输出结果

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_task_1′);
PL/SQL procedure successfully completed.
SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_task_1′) from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_TASK_1′)
——————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name                  : tuning_task_1
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 07/02/2010 15:48:07
Completed at                      : 07/02/2010 15:48:08
Number of SQL Profile Findings    : 1
——————————————————————————-
Schema Name: SCOTT
SQL ID     : 8m2dnmd4a7vx0
SQL Text   : select /*+ no_index(test test_idx) */ * from hr.test where n=1
——————————————————————————-
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: 84.11%)
  ——————————————
  – Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => ‘tuning_task_1′,
            replace => TRUE);
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original With Adjusted Cost
——————————
Plan hash value: 1357081020
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     6   (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   1 – filter(“N”=1)
2- Using SQL Profile
——————–
Plan hash value: 2882402178
—————————————————————————–
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     1   (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   1 – access(“N”=1)
——————————————————————————-

从上面的输出可以看出,自动调整任务发现了一个更好的执行计划,并推荐使用这个计划。

6. 接受执行计划

DECLARE
  my_sqlprofile_name VARCHAR2(30);
BEGIN
  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
    task_name => ‘tuning_task_1′,
    name      => ‘my_sql_profile’);
END;
/
PL/SQL procedure successfully completed.
SQL> select to_char(sql_text) from dba_sql_profiles;
TO_CHAR(SQL_TEXT)
————————————————————————
select /*+ no_index(test test_idx) */ * from hr.test where n=1

7. 再次执行相同的语句,验证结果

SQL> select /*+ no_index(test test_idx) */ * from hr.test where n=1;
Execution Plan
———————————————————-
Plan hash value: 2882402178
—————————————————————————–
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     1   (0)| 00:00:01 |
—————————————————————————–

这里可以看出,Oracle已经使用了不同的执行计划。

dbms_xplan之display_cursor函数的使用

http://blog.csdn.net/robinson_0612/article/details/6866925

分类: SQL/PLSQL 优化 2011-10-12 16:34 1246人阅读 评论(0) 收藏 举报
DBMS_XPLAN包中display_cursor函数不同于display函数,display_cursor用于显示SQL语句的真实的执行计划,在大多数情况下,显示真实
的执行计划有助于更好的分析SQL语句的全过程,尤其是运行此SQL语句实时的I/O开销。通过对比预估的I/O与真实的I/O开销来判断SQL语句所存
在问题,如缺少统计信息,SQL语句执行的次数,根据实际中间结果集的大小来选择合适的连接方式等。本文仅仅讲述display_cursor函数的使
用。

有关执行计划中各字段模块的描述请参考: 执行计划中各字段各模块描述
有关由SQL语句来获取执行计划请参考: 使用 EXPLAIN PLAN 获取SQL语句执行计划
有关使用autotrace来获取执行计划请参考: 启用 AUTOTRACE 功能
有关dbms_xplan之display函数请参考: dbms_xplan之display函数的使用

一、display_cursor函数用法

1、display_cursor函数语法
[sql] view plaincopyprint?
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
2、display_cursor函数参数描述
sql_id
指定位于库缓存执行计划中SQL语句的父游标。默认值为null。当使用默认值时当前会话的最后一条SQL语句的执行计划将被返回
可以通过查询V$SQL 或 V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。
child_number
指定父游标下子游标的序号。即指定被返回执行计划的SQL语句的子游标。默认值为0。如果为null,则sql_id所指父游标下所有子游标
的执行计划都将被返回。
format
控制SQL语句执行计划的输出部分,即哪些可以显示哪些不显示。使用与display函数的format参数与修饰符在这里同样适用。
除此之外当在开启statistics_level=all时或使用gather_plan_statistics提示可以获得执行计划中实时的统计信息
有关详细的format格式描述请参考:dbms_xplan之display函数的使用 中format参数的描述

下面给出启用统计信息时format新增的修饰符
iostats 控制I/O统计的显示
last 默认,显示所有执行计算过的统计。如果指定该值,则只显示最后一次执行的统计信息
memstats 控制pga相关统计的显示
allstats 此为iostats memstats的快捷方式,即allstats包含了iostats和memstats
run_stats_last 等同于iostats last。只能用于oracle 10g R1
run_stats_tot 等同于iostats。只能用于oracle 10g R1

二、演示使用display_cursor函数获取执行计划

1、当前数据库版本以及加载执行计划到库缓存
[sql] view plaincopyprint?
SQL> select * from v$version where rownum<2;

BANNER
------------——
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> SELECT ename,dname,loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.empno = 7788;

ENAME DNAME LOC
-- --— ---
SCOTT RESEARCH DALLAS
2、查看真实的执行计划
[sql] view plaincopyprint?
/*----不传递任何参数给display_cursor函数,显示当前会话最后一条SQL语句的执行计划--—-*/
//
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: moc.liamtoh|2160_nosnibor#moc.liamtoh|2160_nosnibor */
/* QQ: 645746311 */
/
/
SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
------------------
SQL_ID a67wqmkfb9j65, child number 0
-------
SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND
e.empno = 7788

Plan hash value: 2385808155

------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | 1 | 63 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 409 | 12270 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
-----------------—-

Predicate Information (identified by operation id):
-----------

3 - access("E"."EMPNO"=7788)
5 - access("E"."DEPTNO"="D"."DEPTNO")

/*---— 获得SQL语句的SQL_ID,可以看出此SQL_ID与上面显示的执行计划中的SQL_ID一致 --—*/
SQL> select sql_id,address,plan_hash_value,hash_value,child_number from v$sql
2 where sql_text like '%SELECT ename%' and sql_text not like '%from v$sql%';

SQL_ID ADDRESS PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER
--- ---- ----- --
a67wqmkfb9j65 0000000091DBFBC8 2385808155 2629092549 0

/*--— 传递SQL_ID以及format参数,并配合修饰符控制执行计划的输出 ------*/
SQL> select * from table(dbms_xplan.display_cursor('a67wqmkfb9j65',null,'typical -predicate -rows'));

PLAN_TABLE_OUTPUT
----------------
SQL_ID a67wqmkfb9j65, child number 0
-------
SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno = 7788

Plan hash value: 2385808155

----------------
| Id | Operation | Name | Bytes | Cost (%CPU)| Time |
----------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | NESTED LOOPS | | 63 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 33 | 2 (0)| 00:00:01 |
| 3 | INDEX UNIQUE SCAN | PK_EMP | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 12270 | 1 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_DEPT | | 0 (0)| |
----------------
3、查看真实执行计划并获得统计信息
前提条件
设置参数statistics_level为all,可以基于session级别以及实例级别
或者启用gather_plan_statistics提示
[sql] view plaincopyprint?
/*---查看实例参数statistics_level的值,并在会话级别将其设定为all ---*/
SQL> show parameter statistics_le

NAME TYPE VALUE
-------- --- ------
statistics_level string ALL

SQL> alter session set statistics_level=all;

Session altered.

SQL> select e.ename,e.sal,s.grade
2 from emp e
3 join salgrade s
4 on e.sal between losal and hisal
5 and e.deptno = 20;

ENAME SAL GRADE
-- -- --
SCOTT 3000 4
FORD 3000 4
JONES 2975 4
ADAMS 1100 1
SMITH 800 1

/*-— 执行上述SQL语句后获得其真实的执行计划,使用了iostats last -predicate -note 修饰符控制显示输出 -*/
SQL> set pagesize 0
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last -predicate -note'));
SQL_ID 243b0tpjxj6wv, child number 0
-------
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between losal and
hisal and e.deptno = 20

Plan hash value: 4204027666

-------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------
| 1 | MERGE JOIN | | 1 | 1 | 5 |00:00:00.01 | 14 |
| 2 | SORT JOIN | | 1 | 5 | 5 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL | EMP | 1 | 5 | 5 |00:00:00.01 | 7 |
| 4 | FILTER | | 5 | | 5 |00:00:00.01 | 7 |
| 5 | SORT JOIN | | 5 | 5 | 14 |00:00:00.01 | 7 |
| 6 | TABLE ACCESS FULL| SALGRADE | 1 | 5 | 5 |00:00:00.01 | 7 |
-------------------

/*---- 修改会话级别的参数statistics_level为typical并验证修改结果 ----*/
SQL> alter session set statistics_level=typical;

SQL> col name format a40
SQL> col value format a25
SQL> col display_value format a25
SQL> select name, value, display_value, isses_modifiable
2 from v$parameter
3 where isses_modifiable = 'TRUE'
4 and name like '%&input_name%';
Enter value for input_name: statistics_level
old 4: and name like '%&input_name%'
new 4: and name like '%statistics_level%'

NAME VALUE DISPLAY_VALUE ISSES
-------- ----- ----- -
statistics_level TYPICAL TYPICAL TRUE

/*-- 使用提示gather_plan_statistics,并获得其真实执行计划,使用了allstats -rows修饰符控制显示输出 -*/
SQL> set pagesize 180
SQL> SELECT /*+ gather_plan_statistics */ ename,dname,loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND d.deptno=20 ORDER BY 1,2,3;

ENAME DNAME LOC
-- --— ---
ADAMS RESEARCH DALLAS
FORD RESEARCH DALLAS
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
SMITH RESEARCH DALLAS

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats -rows'));

PLAN_TABLE_OUTPUT
-------------------
SQL_ID d2hh42yzqqjz7, child number 0
-------
SELECT /*+ gather_plan_statistics */ ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND
d.deptno=20 ORDER BY 1,2,3

Plan hash value: 3339094711

-----------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------
| 1 | SORT ORDER BY | | 1 | 5 |00:00:00.01 | 9 | 2048 | 2048 | 1/0/0|
| 2 | NESTED LOOPS | | 1 | 5 |00:00:00.01 | 9 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 |00:00:00.01 | 1 | | | |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 5 |00:00:00.01 | 7 | | | |
-----------------------

Predicate Information (identified by operation id):
-----------

4 - access("D"."DEPTNO"=20)
5 - filter("E"."DEPTNO"=20)

Note
-
- dynamic sampling used for this statement

三、总结

1、与display函数不同,display_cursor显示的为真实的执行计划
2、对于format参数,使用与display函数的各个值,同样适用于display_cursor函数
3、当statistics_level为all或使用gather_plan_statistics提示可以获得执行时的统计信息
4、根据真实与预估的统计信息可以初步判断SQL效率低下的原因,如统计信息的准确性、主要的开销位于那些步骤等