1.创建模拟表




SQL> create table system.wh as select * from dba_objects;


表已创建。


SQL> create index system.id on system.wh(object_id);


索引已创建。


2.进行一次全表扫




SQL> SELECT /*+ FULL(wh)*/count(*) FROM system.wh WHERE OBJECT_ID=2;


  COUNT(*)
----------
         1


定位SQL


SQL> select sql_id,sql_text from v$sql where sql_text like '%FULL(wh)%';


SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
2tk8yjjs1jnqz
select sql_id,sql_text from v$sql where sql_text like '%FULL(wh)%'


56zb0jhvkm7dj
SELECT /*+ FULL(wh)*/count(*) FROM system.wh WHERE OBJECT_ID=2




看来 56zb0jhvkm7dj   就是这个SQL_ID


确定SQL outline信息


SQL> select * from table(dbms_xplan.display_cursor('56zb0jhvkm7dj',null,'outline'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  56zb0jhvkm7dj, child number 0
-------------------------------------
SELECT /*+ FULL(wh)*/count(*) FROM system.wh WHERE OBJECT_ID=2


Plan hash value: 1206455612


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   306 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |     1 |    13 |   306   (1)| 00:00:04 |
---------------------------------------------------------------------------


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


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')


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


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


   2 - filter("OBJECT_ID"=2)




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)




已选择37行。


3.创建profile


SQL> declare
  2   v_hints sys.sqlprof_attr;
  3   begin
  4   v_hints:=sys.sqlprof_attr(
  5        'BEGIN_OUTLINE_DATA',
  6        'IGNORE_OPTIM_EMBEDDED_HINTS',
  7        'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',
  8        'DB_VERSION(''11.2.0.3'')',
  9        'ALL_ROWS',
 10        'OUTLINE_LEAF(@"SEL$1")',
 11        'FULL(@"SEL$1" "WH"@"SEL$1")',  
 12        'END_OUTLINE_DATA');
 13        dbms_sqltune.import_sql_profile('SELECT count(*) FROM system.wh WHERE OBJECT_ID=:p',v_hints,'sqlprofilewh',force_match=>true,replace=>true);
 14  end;
 15  /


PL/SQL 过程已成功完成。




4.验证一下SQL执行情况


SQL> variable p number;  
SQL> exec :p := 2;  


PL/SQL 过程已成功完成。


SQL> set autotrace on
SQL> SELECT count(*) FROM system.wh WHERE OBJECT_ID=:p;


  COUNT(*)
----------
         1




执行计划
----------------------------------------------------------
Plan hash value: 1206455612


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   306   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| WH   |     1 |     5 |   306   (1)| 00:00:04 |
---------------------------------------------------------------------------


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


   2 - filter("OBJECT_ID"=TO_NUMBER(:P))


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




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1120  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


虽然应该走索引的查询却走了固定的 sqlprofilewh