using sql prfile to ignore hints

有些开发工程师喜欢在sql里加hint, 希望走自认为高效的执行计划，但往往事与愿违，
加了hint的sql反而走了不好的执行计划，碰到这种情况，在不能修改代码的情况下，可以用sql profile来忽略这些hint,
创建sql profile的hint就是 IGNORE_OPTIM_EMBEDDED_HINTS

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');

select /*+ full(t) */ count(*) from t where object_id=1;
TEST@bbb>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7r8trk89zfznk, child number 0
-------------------------------------
select /*+ full(t) */ count(*) from t where object_id=1
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     5 |    55   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=1)

begin
  dbms_sqltune.import_sql_profile(
  name => 'test_profile',
  category => 'DEFAULT',
  sql_text => 'select /*+ full(t) */ count(*) from t where object_id=1',
  profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'));    
end;
/

select /*+ full(t) */ count(*) from t where object_id=1;
TEST@bbb>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  67cgq3sx0fgfz, child number 0
-------------------------------------
select /*+ full(t) */ count(*) from t where object_id=1
Plan hash value: 468740019
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| T_IND |     1 |     5 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1)
Note
-----
   - SQL profile test_profile used for this statement

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/37279/viewspace-2135826/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/37279/viewspace-2135826/