Oracle SQL Profile指定执行计划

SQL Profiles在10g中引入，可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化（更容易生成、更改和控制，在对SQL语句的支持上做得更好，也就是适用范围更广）


现在我在使用Outlines的场合，均使用SQL Profiles来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的library cache latch的争用，不得不关闭Outline的使用，但是改用SQL Profiles不再有这个问题。这或许是个BUG，不过既然能用SQL Profiles代替，也就没再深入去研究这个问题。


使用SQL Profiles无非是两个目的：
1、锁定或者说是稳定执行计划。
2、在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。


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('sys','t1',cascade=>true,method_opt=>'for all columns size 1'); 
SQL> exec dbms_stats.gather_table_stats('sys','t2',cascade=>true,method_opt=>'for all columns size 1'); 


然后看看下面这一条SQL：
SQL> set autotrace traceonly
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;


36 rows selected.


Elapsed: 00:00:00.06


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   412   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   412   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    67   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 87908 |   944K|   344   (1)| 00:00:05 |
---------------------------------------------------------------------------


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


   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)




Statistics
----------------------------------------------------------
         38  recursive calls
          0  db block gets
       1557  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         36  rows processed
注意，逻辑读为1557


从执行计划可以看到，这条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> 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;  


36 rows selected.


Elapsed: 00:00:00.04


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5070   (1)| 00:01:01 |
|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5070   (1)| 00:01:01 |
|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5070   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    67   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


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


   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        307  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed


从执行计划可以看到Oracle优化器评估的成本为5070，远远高于原来的412。
但是实际的逻辑读是多少呢？从1557降到307。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。


【接下来开始使用SQL_PROFILE进行优化】
先取出没有加hint的那条SQL_ID。
SQL> select sql_id,sql_text from v$sqlarea where sql_text like 'select t1.*,t2.owner from t1,t2 where t1.object_name%';                                         
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id


下面我们用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';  --获取上一条SQL的SQL_ID，即没有加hint的那条SQL
  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  /  


PL/SQL procedure successfully completed.


SQL> print tuning_task;  

TUNING_TASK
--------------------------------------------------------------------------------------------------------------------------------
TASK_10619


SQL> set long 99999
SQL> col comments for a100
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual;  
COMMENTS
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_10684
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/06/2016 14:20:15
Completed at       : 04/06/2016 14:20:18




COMMENTS
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
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)


COMMENTS
----------------------------------------------------------------------------------------------------
--------------------------------------------------------
  A potentially better execution plan was found for this statement.


  Recommendation (estimated benefit: 80.04%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_10684',
            task_owner => 'SYS', replace => TRUE);


  Validation results
  ------------------


COMMENTS
----------------------------------------------------------------------------------------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.


                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .036642            .02716      25.87 %
  CPU Time (s):                 .036494           .026995      26.02 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1503               298      80.17 %


COMMENTS
----------------------------------------------------------------------------------------------------
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                    36                36
  Fetches:                           36                36
  Executions:                         1                 1


  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.


COMMENTS
----------------------------------------------------------------------------------------------------
  2. Statistics for the SQL profile plan were averaged over 10 executions.


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974


---------------------------------------------------------------------------


COMMENTS
----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    36 |  1476 |   412   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |    36 |  1476 |   412   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |    36 |  1080 |    67   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 87908 |   944K|   344   (1)| 00:00:05 |
---------------------------------------------------------------------------


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




COMMENTS
----------------------------------------------------------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)


2- Using SQL Profile
--------------------
Plan hash value: 1022743391


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------


COMMENTS
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   139   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   139   (1)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   139   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    67   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


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




COMMENTS
----------------------------------------------------------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


-------------------------------------------------------------------------------




SQL> 


通过结果对比，显然加了HINT的方式效率更高。上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。
结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为36，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。这样一来，即使SQL正文不加hint该SQL也会按照我们的要求走。注意：执行计划的NOTE部分，有个SQL_PROFILE字样
SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);
SQL> set autotrace traceonly
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id; 


36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   139   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   139   (1)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   139   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    67   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


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


   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


Note
-----
   - SQL profile "SYS_SQLPROF_0153ea63e5ff0000" used for this statement




Statistics
----------------------------------------------------------
         34  recursive calls
          0  db block gets
        320  consistent gets
          1  physical reads
        144  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         36  rows processed


SQL> 
我们再执行其他的类似SQL看看：比如修改like条件，再加几个空格修改大小写等
SQL> SElect t1.*,t2.owner from t1,t2 where t1.object_name     like '%T2%' and t1.object_id=t2.object_ID;


57 rows selected.


Elapsed: 00:00:00.35


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   139   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   139   (1)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   139   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    67   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


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


   3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


Note
-----
   - SQL profile "SYS_SQLPROF_0153ea63e5ff0000" used for this statement




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        300  consistent gets
          7  physical reads
          0  redo size
       2957  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         57  rows processed


SQL> 
卧槽，居然还是有效地！调用了同一个SQL Profile！即便cursor_sharing是EXACT的。因为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> show parameter cursor_sharing


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT


补充：对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。


7、删除profile
如不再使用，可删除profile
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_015431a0d02b0000');