sql profile固定执行计划(Automatic)

--在我们无法修改源代码的时候，我们可以采用sqlprofile来改变执行计划
--新增一张测试表
create table test_emp as select * from scott.emp;

--新建索引
create index idx_001 on test_emp(empno);

--下面的语句正常是要走全表扫描的
select /*+ no_index(test_emp idx_001) */ * from test_emp where empno=7521;

select * from v$sql t where t.SQL_TEXT like '%select /*+ no_index(test_emp idx_001) */ * from test_emp where empno=7521%'
select * from table(dbms_xplan.display_cursor('6xgya67tytkax',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

--创建一个调优任务
declare 
  my_task_name varchar2(30);
  my_sqltext clob;
begin
    my_sqltext:='select /*+ no_index(test_emp idx_001) */ * from test_emp where empno=7499';
    my_task_name:=dbms_sqltune.create_tuning_task(
                            sql_text=>my_sqltext,
                            user_name => 'X86',
                            scope=>'COMPREHENSIVE',
                            time_limit=>60,
                            task_name=>'my_sql_tuning_task_2',
                            description=>'Task to tune a query on table test_emp');
end;
 
--执行这个调优任务                       
begin 
    dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task_2');    
end;                       

--发现有可用的调优方案                          
select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual  

 

--应用这个调优方案 
begin dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2', task_owner => 'X86', replace => TRUE,force_match => true);   end;                 
                            
                            
begin
    dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01602055212c0000');
    dbms_sqltune.drop_tuning_task('my_sql_tuning_task_2');
end;