使用SQL Profile固定执行计划

原SQL语句如下：
update Tindex      a set a.object_type='SPECIAL' where a.object_type is not null
and exists (select 1 from Tindex b where b.object_name='TINDEX'
         and a.object_name=b.object_name)

使用以下语句查看当前游标执行计划，和预期的不一样
select * from table(dbms_xplan.display_cursor('55j33gdu4qx7g'));

用explain plan for解释加了hint语句的SQL，或者SQL语句能出现期望的执行计划，例如在不同的版本的数据库上执行会产生不同的执行计划。

SQL> explain plan for
  2  update Tindex a
  3     set a.object_type = 'SPECIAL'
  4   where a.object_type is not null
  5     and exists (select /*+full(b)*/1
  6            from Tindex b
  7           where b.object_name = 'TINDEX'
  8             and a.object_name = b.object_name);
 
Explained
 
获取outline：
SQL> select * from table(dbms_xplan.display(null,null,'outline'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2122516761
--------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                 |     1 |    59 |   570
|   1 |  UPDATE                      | TINDEX          |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TINDEX          |     1 |    34 |     3
|   3 |    NESTED LOOPS              |                 |     1 |    59 |   570
|   4 |     SORT UNIQUE              |                 |     7 |   175 |   566
|*  5 |      TABLE ACCESS FULL       | TINDEX          |     7 |   175 |   566
|*  6 |     INDEX RANGE SCAN         | IDX_TINDEX_NAME |     1 |       |     2
--------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$3FF8579E" "A"@"UPD$1")
      LEADING(@"SEL$3FF8579E" "B"@"SEL$1" "A"@"UPD$1")
      INDEX(@"SEL$3FF8579E" "A"@"UPD$1" ("TINDEX"."OBJECT_NAME"))
      FULL(@"SEL$3FF8579E" "B"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"UPD$1")
      UNNEST(@"SEL$1")
      OUTLINE_LEAF(@"SEL$3FF8579E")
      ALL_ROWS
      OPT_PARAM('star_transformation_enabled' 'true')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."OBJECT_TYPE" IS NOT NULL)
   5 - filter("B"."OBJECT_NAME"='TINDEX')
   6 - access("A"."OBJECT_NAME"='TINDEX')
       filter("A"."OBJECT_NAME"="B"."OBJECT_NAME")
 
41 rows selected

由于dbms_sqltune.import_sql_profile需要使用sql text的文本内容，如果SQL语句较长且有换行或大小写的问题，如果直接写SQL，可能和原来的SQL语句不同。

使用outline的内容创建profile
declare
v_hints sys.sqlprof_attr;
sql_txt clob;
begin
v_hints:=sys.sqlprof_attr(
      'BEGIN_OUTLINE_DATA',
      'USE_NL(@"SEL$3FF8579E" "A"@"UPD$1")',
      'LEADING(@"SEL$3FF8579E" "B"@"SEL$1" "A"@"UPD$1")',
      'INDEX(@"SEL$3FF8579E" "A"@"UPD$1" ("TINDEX"."OBJECT_NAME"))',
      'FULL(@"SEL$3FF8579E" "B"@"SEL$1")',
      'OUTLINE(@"SEL$1")',
      'OUTLINE(@"UPD$1")',
      'UNNEST(@"SEL$1")',
      'OUTLINE_LEAF(@"SEL$3FF8579E")',
      'ALL_ROWS',
      'OPT_PARAM(''star_transformation_enabled'' ''true'')',
      'OPTIMIZER_FEATURES_ENABLE(''10.2.0.4'')',
      'IGNORE_OPTIM_EMBEDDED_HINTS',
      'END_OUTLINE_DATA'
);
  select sql_fulltext into sql_txt from v$sql where sql_id='55j33gdu4qx7g';
  dbms_sqltune.import_sql_profile(sql_text => sql_txt,
  profile => v_hints,name => 'SQLPROF_INC_CUST_999',
  replace => TRUE,force_match => TRUE);
end;
/

新产生了一个SQL的子句执行计划
SQL> select sql_fulltext,buffer_gets,executions,sql_id,child_number  from v$sql where sql_id='55j33gdu4qx7g';

SQL_FULLTEXT                                                                     BUFFER_GETS EXECUTIONS SQL_ID        CHILD_NUMBER
-------------------------------------------------------------------------------- ----------- ---------- ------------- ------------
update Tindex      a set a.object_type='SPECIAL' where a.object_type is not null          48          3 55j33gdu4qx7g            0
update Tindex      a set a.object_type='SPECIAL' where a.object_type is not null       14249          5 55j33gdu4qx7g            1

可以看到使用了SQL profile
select * from table(dbms_xplan.display_cursor('55j33gdu4qx7g',1));

SQL_ID  55j33gdu4qx7g, child number 1
-------------------------------------
update Tindex      a set a.object_type='SPECIAL' where a.object_type is not null and
exists (select 1 from Tindex b where b.object_name='TINDEX'          and
a.object_name=b.object_name)
 
Plan hash value: 2122516761
 
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                 |       |       |   570 (100)|          |
|   1 |  UPDATE                      | TINDEX          |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TINDEX          |     1 |    34 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                 |     1 |    59 |   570   (1)| 00:00:07 |
|   4 |     SORT UNIQUE              |                 |     7 |   175 |   566   (1)| 00:00:07 |
|*  5 |      TABLE ACCESS FULL       | TINDEX          |     7 |   175 |   566   (1)| 00:00:07 |
|*  6 |     INDEX RANGE SCAN         | IDX_TINDEX_NAME |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"."OBJECT_TYPE" IS NOT NULL)
   5 - filter("B"."OBJECT_NAME"='TINDEX')
   6 - access("A"."OBJECT_NAME"='TINDEX')
       filter("A"."OBJECT_NAME"="B"."OBJECT_NAME")
 
Note
-----
   - SQL profile "SQLPROFILE_full" used for this statement
 
刷新共享池或重启数据库之后仍然生效

如果想要删除profile，可以用以下命令：
exec  dbms_sqltune.drop_sql_profile(name => 'SQLPROFILE_full');