- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/
PL/SQL procedure successfully completed.
SESSION1--SCOTT
重新执行查询，即使使用了no_index提示，索引也会被使用
注意，在执行计划中，我们会看到SQL profile "my_sql_profile" used for this statement
SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan
-------------------------------------------------------------------------
Plan hash value: 1416057887
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
---------------
Note
-----
- SQL profile "my_sql_profile" used for this statement
由这个例子我们可以发现，在必要情况下，SQL Profile可以让hint失效
如何为每个tuning set 产生一个报告
SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.* FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'
FROM dba_sqlset d
ORDER BY d.last_modified DESC
摘自 狂浪 的旮旯天地