手动使用sql profile来进行执行计划的稳固

1. create table to be test
SQL> create table protest as select * from all_objects;
Table created.
SQL> select count(*) from protest;
  COUNT(*)
----------
     12819  
2. create index protest_idx on protest(object_id);
3. GATHER statistics 
exec dbms_stats.gather_table_stats('','PROTEST');
4. explain plan for select * from protest where object_id = 4;
---通过 outline 可以获取系统产生的 hint
select * from table(dbms_xplan.display(null,null,'outline'));
---output
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3036224489
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    89 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PROTEST     |     1 |    89 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PROTEST_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "PROTEST"@"SEL$1" ("PROTEST"."OBJECT_ID"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=4)
28 rows selected.
6. compare with step 4
explain plan for select/*+full( protest ) */ * from protest where object_id = 4;
SQL> select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3877474892
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    89 |    48   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PROTEST |     1 |    89 |    48   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "PROTEST"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=6)
27 rows selected.
7. use sql profile ，添加sql profile
declare 
v_hints sys.sqlprof_attr;
begin 
v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "PROTEST"@"SEL$1")');
dbms_sqltune.import_sql_profile('select * from protest where object_id = 4',
v_hints,
'PROTEST',
force_match=>true
);
end;
8. test the result ,结果
select * from protest where object_id = 4


Execution Plan
----------------------------------------------------------
Plan hash value: 3877474892
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    89 |    48   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PROTEST |     1 |    89 |    48   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=4)
Note
-----
   - SQL profile "PROTEST" used for this statement  ---重点在这里
Statistics
----------------------------------------------------------
         32  recursive calls
         32  db block gets
        195  consistent gets
          3  physical reads
      12228  redo size
       1604  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
8. drop sql profile 删除 profile
exec dbms_sqltune.drop_sql_profile('PROTEST')


10. 删除profile之后的结果
select * from protest where object_id = 4
Execution Plan
----------------------------------------------------------
Plan hash value: 3036224489
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    89 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PROTEST     |     1 |    89 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PROTEST_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=4)
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


---注意：----------------  problem  : -------------------------------
---  如果系统开了baseline的稳固方式 ，就算创建了sql profile 
---  也不会的到预期的测试结果 ，需要先关闭baseline alter system set optimizer_use_sql_plan_baselines = false；
---  （果两中方式都启用的情况 ，后期会继续做测试）
---- 看如下结果
SQL> show parameter optimizer_use
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_use_invisible_indexes      boolean                FALSE
optimizer_use_pending_statistics     boolean                FALSE
optimizer_use_sql_plan_baselines     boolean                TRUE
SQL> EXPLAIN PLAN FOR SELECT * FROM PROTEST WHERE OBJECT_ID = :a;
Explained.
SQL> select *From table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3036224489


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


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:A))
14 rows selected.
SQL> declare 
  2     v_hints sys.sqlprof_attr;
  3     begin 
  4     v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "PROTEST"@"SEL$1")');
  5     dbms_sqltune.import_sql_profile('select * from protest where object_id = :a',
  6                                     v_hints,
  7                                     'PROTEST',
  8                                     force_match=>true
  9     );
 10  end;
 11  /
PL/SQL procedure successfully completed.
SQL> EXPLAIN PLAN FOR SELECT * FROM PROTEST WHERE OBJECT_ID = :a;
Explained.
SQL> select *From table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3877474892
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    89 |    48   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PROTEST |     1 |    89 |    48   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "PROTEST"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=TO_NUMBER(:A))
Note
-----
   - SQL profile "PROTEST" used for this statement
31 rows selected.