ITPub博客

首页 > 数据库 > Oracle > using sql prfile to ignore hints

using sql prfile to ignore hints

原创 Oracle 作者:warmbreeze 时间:2017-03-22 15:01:38 0 删除 编辑
有些开发工程师喜欢在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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: sqlplus -L
下一篇: abou AMM
请登录后发表评论 登录
全部评论

注册时间:2012-02-15

  • 博文量
    45
  • 访问量
    42543