TEST@bbb>select * from v$version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production




create table t as select * from dba_objects;


create index t_ind on t(object_id);


exec dbms_stats.gather_table_stats(user,'T');






--使用索引
TEST@bbb>select object_name from t where object_id=99;


OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EDITION$


TEST@bbb>select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g54w1tv61hg0x, child number 0
-------------------------------------
select object_name from t where object_id=99


Plan hash value: 1376202287


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


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


   2 - access("OBJECT_ID"=99)




--加hint走full
TEST@bbb>select /*+ full(t) */ object_name from t where object_id=99;


OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EDITION$


TEST@bbb>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  60vsncps78z5c, child number 0
-------------------------------------
select /*+ full(t) */ object_name from t where object_id=99


Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   345 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    30 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T@SEL$1


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


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */


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


   1 - filter("OBJECT_ID"=99)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "OBJECT_NAME"[VARCHAR2,128]


--根据outline Data创建sqlprofile


declare
 v_h sys.sqlprof_attr;
 begin
 v_h:=sys.sqlprof_attr(
      'BEGIN_OUTLINE_DATA',
      'IGNORE_OPTIM_EMBEDDED_HINTS',
      'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
      'DB_VERSION(''11.2.0.4'')',
      'ALL_ROWS',
      'OUTLINE_LEAF(@"SEL$1")',
      'FULL(@"SEL$1" "T"@"SEL$1")',  
      'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
 'select object_name from t where object_id=99',v_h,'SqlProfile_g54w1tv61hg0x', 
force_match=>true,
replace=>true);
end;
/


--再次执行第一个sql
TEST@bbb>select object_name from t where object_id=99;


OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EDITION$


TEST@bbb>select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g54w1tv61hg0x, child number 0
-------------------------------------
select object_name from t where object_id=99


Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   345 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    30 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=99)


Note
-----
   - SQL profile SqlProfile_g54w1tv61hg0x used for this statement




sql prfile已生效