SQL profile如何固定执行计划？

创建实验表

create table sqltest as select * from hr.employees;
select * from sqltest s where s.employee_id =202;
select sql_id,sql_text from v$sql where sql_text like '%s.employee_id =202%';
 
SQL_TEXT
--------------------------------------------------------------------------------
7arc85h8gfyn6
select * from sqltest s where s.employee_id =202
 
1aa7ub8cx3p8r
select sql_id,sql_text from v$sql where sql_text like '%s.employee_id =202%'
 
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID       7arc85h8gfyn6, child number 0
-------------------------------------
select * from sqltest s where s.employee_id =202
 
Plan hash value: 2099455689
 
-----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |             |             |           2 (100)|              |
|*  1 |  TABLE ACCESS FULL| SQLTEST |            1 |          133 |            2   (0)| 00:00:01 |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("S"."EMPLOYEE_ID"=202)

如何将2099455689此执行计划固定在SQL7arc85h8gfyn6上？

绑定该执行计划

输入：7arc85h8gfyn6，2099455689
 
declare
  v_hint      sys.sqlprof_attr;
  v_sql_text  clob;
  p_bad_sqlid varchar2(32) default '&bad_sqlid';
begin
  select sql_text
    into v_sql_text
    from dba_hist_sqltext
   where sql_id = p_bad_sqlid
     and rownum = 1;
  select extractvalue(value(d), '/hint') as outline_hints bulk collect
    into v_hint
    from xmltable('/*/outline_data/hint' passing
                  (select xmltype(other_xml) as xmlval
                     from dba_hist_sql_plan
                    where sql_id = '&bad_sqlid'
                      and plan_hash_value = '&good_hash'
                      and other_xml is not null)) d;
  dbms_sqltune.import_sql_profile(v_sql_text,
                                  v_hint,
                                  'sql_profile_&bas_sqlid',
                                  force_match             => true,
                                  replace                 => true);
end;

创建索引依旧不会全表扫 

SQL> CREATE INDEX TESE_1 ON SQLTEST(employee_id);
 
Index created.
 
SQL> select * from sqltest s where s.employee_id =202;
 
 
 
SQL>  select * from table(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID       7arc85h8gfyn6, child number 0
-------------------------------------
select * from sqltest s where s.employee_id =202
 
Plan hash value: 2099455689
 
-----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |             |             |           2 (100)|              |
|*  1 |  TABLE ACCESS FULL| SQLTEST |   2 |          266 |            2   (0)| 00:00:01 |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("S"."EMPLOYEE_ID"=202)
Note
-----
   - SQL profile sql_profile_7arc85h8gfyn6 used for this statement

提示sql_profile生效。

删除sql_profile,验证效果

SQL> exec dbms_sqltune.drop_sql_profile(name => 'sql_profile_7arc85h8gfyn6');
 
PL/SQL procedure successfully completed.
 
SQL> select * from sqltest s where s.employee_id =202;
 
EMPLOYEE_ID FIRST_NAME                   LAST_NAME
----------- -------------------- -------------------------
EMAIL                               PHONE_NUMBER        HIRE_DATE JOB_ID      SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
           202 Pat                Fay
PFAY                        603.123.6666                17-AUG-05 MK_REP      6000
                           201              20
 
 
SQL> select * from table(dbms_xplan.display_cursor()); 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID       7arc85h8gfyn6, child number 0
-------------------------------------
select * from sqltest s where s.employee_id =202
 
Plan hash value: 1486395402
 
--------------------------------------------------------------------------------
-------
 
| Id  | Operation                        | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |       |       |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| SQLTEST |     1 |   133 |     2  (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TESE_1  |     1 |       |     1    (0)| 00: 00:01 |
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."EMPLOYEE_ID"=202)

删除profile后走索引扫，验证绑定成功。