有些开发工程师喜欢在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
加了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/