使用sql profile实现outline的功能

首先看一个查询：
sys@EBANK>select object_name from t where object_id between 1000 and 2000;

1001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 15384 |   345K|   533   (1)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| T    | 15384 |   345K|   533   (1)| 00:00:07 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<=2000 AND "OBJECT_ID">=1000)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2387  consistent gets
          0  physical reads
      29996  redo size
      27644  bytes sent via SQL*Net to client
       1107  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1001  rows processed
      
对于这个查询，由于表中统计信息过旧，错误的选择了全表扫描，加索引提示：

sys@EBANK>
sys@EBANK>select /*+ index(t idx_t) */ object_name from t where object_id between 1000 and 2000;

1001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 15384 |   345K| 15421   (1)| 00:03:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 15384 |   345K| 15421   (1)| 00:03:06 |
|*  2 |   INDEX RANGE SCAN          | IDX_T | 15384 |       |    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=2000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        203  consistent gets
          0  physical reads
          0  redo size
      27644  bytes sent via SQL*Net to client
       1107  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1001  rows processed

对于上述查询，显然加索引提示是合理的，而当无法修改应用时，我们需要从数据库端来干预此sql的执行计划，采用sql profile可以完成此功能
我们暂且把未加提示的sql称之为sql_nohi,加了提示的sql称之为sql_hi，执行如下存储过程，该存储过程的功能首先取得加sql_hi的outline数据，也就是一系列hints，
然后使用这些outline数据来对sql_nohi生成profile，也就是说让sql_nohi来使用sql_hi的执行计划:
declare
  ar_hint_table    sys.dbms_debug_vc2coll;
  ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
  cl_sql_text      clob;
  i                pls_integer;
begin
  with a as (
  select
           rownum as r_no
         , a.*
  from
           table(
             -- replace with
             -- DBMS_XPLAN.DISPLAY_AWR
             -- if required
             dbms_xplan.display_cursor(
               '&&good_sql_id'
             ,  null
             , 'OUTLINE'
             )
             -- dbms_xplan.display_awr(
             --   '&&1'
             -- , null
             -- , null
             -- , 'OUTLINE'
             -- )
           ) a
  ),
  b as (
  select
           min(r_no) as start_r_no
  from
           a
  where
           a.plan_table_output = 'Outline Data'
  ),
  c as (
  select
           min(r_no) as end_r_no
  from
           a
         , b
  where
           a.r_no > b.start_r_no
  and      a.plan_table_output = '  */'
  ),
  d as (
  select
           instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
  from
           a
         , b
  where
           r_no = b.start_r_no + 4
  )
  select
           substr(a.plan_table_output, d.start_col) as outline_hints
  bulk collect
  into
           ar_hint_table
  from
           a
         , b
         , c
         , d
  where
           a.r_no >= b.start_r_no + 4
  and      a.r_no <= c.end_r_no - 1
  order by
           a.r_no;
 
  select
           sql_text
  into
           cl_sql_text
  from
           -- replace with dba_hist_sqltext
           -- if required for AWR based
           -- execution
           v$sql
           -- sys.dba_hist_sqltext
  where
           sql_id = '&&bad_sql_id';
 
  -- this is only required
  -- to concatenate hints
  -- splitted across several lines
  -- and could be done in SQL, too
  i := ar_hint_table.first;
  while i is not null
  loop
    if ar_hint_table.exists(i + 1) then
      if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
        ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
        ar_hint_table.delete(i + 1);
      end if;
    end if;
    i := ar_hint_table.next(i);
  end loop;
 
  i := ar_hint_table.first;
  while i is not null
  loop
    ar_profile_hints.extend;
    ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
    i := ar_hint_table.next(i);
  end loop;
 
  dbms_sqltune.import_sql_profile(
    sql_text    => cl_sql_text
  , profile     => ar_profile_hints
  , name        => 'PROFILE_&&1'
  -- use force_match => true
  -- to use CURSOR_SHARING=SIMILAR
  -- behaviour, i.e. match even with
  -- differing literals
  , force_match => false
  );
end;
/

此过程可以完成上述功能，测试如下：

首先取得两条sql的sql_id：
sys@EBANK>select sql_id,sql_text from v$sql where sql_text like ('select%from t where object_id between 1000 and 2000%');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------
dmn81fk77n9z6 select object_name from t where object_id between 1000 and 2
              000

036w3z13k3wgh select /*+ index(t idx_t) */ object_name from t where object
              _id between 1000 and 2000

执行gen_sql_profile.sql:

sys@EBANK>@gen_sql_profile.sql
Enter value for good_sql_id: 036w3z13k3wgh
old  17:                '&&good_sql_id'
new  17:                '036w3z13k3wgh'
Enter value for bad_sql_id: dmn81fk77n9z6
old  83:            sql_id = '&&bad_sql_id';
new  83:            sql_id = 'dmn81fk77n9z6';
Enter value for profile_name: test
old 112:   , name        => 'PROFILE_&profile_name'
new 112:   , name        => 'PROFILE_test'

PL/SQL procedure successfully completed.

此时再执行未加hints的sql:
sys@EBANK>set autot traceonly
sys@EBANK>select object_name from t where object_id between 1000 and 2000;

1001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 15384 |   345K| 15421   (1)| 00:03:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 15384 |   345K| 15421   (1)| 00:03:06 |
|*  2 |   INDEX RANGE SCAN          | IDX_T | 15384 |       |    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=2000)

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

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        203  consistent gets
          0  physical reads
          0  redo size
      27644  bytes sent via SQL*Net to client
       1106  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1001  rows processed

可以看到，oracle使用了sql profile，sql的执行计划得以改变，对于上述sql profile，是sql_id相同的条件下生效的，改变一下where条件中的值：
sys@EBANK>exec dbms_sqltune.drop_sql_profile('PROFILE_test');

PL/SQL procedure successfully completed.

sys@EBANK>@gen_sql_profile                                                                                               
Enter value for good_sql_id: 036w3z13k3wgh
old  17:                '&&good_sql_id'
new  17:                '036w3z13k3wgh'
Enter value for bad_sql_id: dmn81fk77n9z6
old  83:            sql_id = '&&bad_sql_id';
new  83:            sql_id = 'dmn81fk77n9z6';
Enter value for profile_name: TEST
old 112:   , name        => 'PROFILE_&profile_name'
new 112:   , name        => 'PROFILE_TEST'

PL/SQL procedure successfully completed.

sys@EBANK>set autot traceonly explain
sys@EBANK>select object_name from t where object_id between 1000 and 2001;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 15399 |   345K| 15436   (1)| 00:03:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 15399 |   345K| 15436   (1)| 00:03:06 |
|*  2 |   INDEX RANGE SCAN          | IDX_T | 15399 |       |    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=2001)

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

sys@EBANK>select object_name from t where object_id between 1000 and 3000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 30736 |   690K| 30809   (1)| 00:06:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 30736 |   690K| 30809   (1)| 00:06:10 |
|*  2 |   INDEX RANGE SCAN          | IDX_T | 30736 |       |    69   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=3000)

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

可见，对于这种没有使用绑定变量的sql，即使是where条件中的取值不同，仍然能够使用sql profile，这是由于设置了force_match => true的原因。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-674702/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-674702/