Oracle 使用 sql profile 固定执行计划

测试使用 sql profile 固定执行计划：

Oracle 10g之前有outlines,10g之后 sql profile 。如果针对非绑定变量的sql,outlines则效果不佳，不建议使用 。

1、准备测试用表
SQL> create table zzh_ob as select * from dba_objects;
SQL> create index ind_obid on zzh_ob(object_id);
SQL> select object_id from zzh_ob where rownum<2;
 OBJECT_ID
----------
        16
SQL> exec dbms_stats.gather_table_stats(user,'zzh_ob',cascade=>true);
 
原sql执行计划:
SQL> set autot trace explain
SQL> select * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 3485916696

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   127 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZZH_OB   |     1 |   127 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_OBID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=16)
 
新sql执行计划
SQL> select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 474274488

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |   127 |   388   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ZZH_OB |     1 |   127 |   388   (1)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=16)

2、获取新sql的sql_id
SQL> col sql_id for a20
SQL> col sql_text for a120
SQL> select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%';
SQL_ID               SQL_TEXT
-------------------- ------------------------------------------------------------------------------------------------------------------------
g5btxvsh51ct5        EXPLAIN PLAN SET STATEMENT_ID='PLUS1200001' FOR select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%'
4kn9w9q3xf73k        EXPLAIN PLAN SET STATEMENT_ID='PLUS1200001' FOR select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16
68x7hd7uaqqk1        select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%'
1syfgv2q5ggn2        select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16
                     
3、获取新sql的outline
SQL> set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor('1syfgv2q5ggn2',null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1syfgv2q5ggn2, child number 0
-------------------------------------
select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16

Plan hash value: 474274488

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |   388 (100)|          |
|*  1 |  TABLE ACCESS FULL| ZZH_OB |     1 |   127 |   388   (1)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "ZZH_OB"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("OBJECT_ID"=16)

32 rows selected.
 
4、创建sql profile(SQLPROFILE_01)                     
SQL> declare
   v_hints sys.sqlprof_attr;
   begin
   v_hints:=sys.sqlprof_attr(
     'BEGIN_OUTLINE_DATA',
     'IGNORE_OPTIM_EMBEDDED_HINTS',
     'OPTIMIZER_FEATURES_ENABLE(''19.1.0'')',
  'DB_VERSION(''19.1.0'')',
     'ALL_ROWS',
     'OUTLINE_LEAF(@"SEL$1")',
     'FULL(@"SEL$1" "ZZH_OB"@"SEL$1")',
     'END_OUTLINE_DATA');
   dbms_sqltune.import_sql_profile(
     'select * from zzh_ob where object_id=16',
     v_hints,'SQLPROFILE_01',
    force_match=>true,replace=>false);
  end;
  /
 
5、查看是否使用 sql profile
SQL> set autot trace explain
SQL> select * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 474274488

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |   127 |   388   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ZZH_OB |     1 |   127 |   388   (1)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=16)

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

Statistics
----------------------------------------------------------
        247  recursive calls
          0  db block gets
       1544  consistent gets
          2  physical reads
          0  redo size
       2685  bytes sent via SQL*Net to client
        401  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
          1  rows processed

查询已经创建的 sql profile ： 
SELECT name, created, category, sql_Text from dba_sql_profiles ORDER BY created DESC;