[20141119]使用脚本完成sql_profile工作

[20141119]使用脚本完成sql_profile工作.txt

--工作需要经常使用sql profile来优化sql语句，由于记不住命令，我基本都在toad下使用，这样非常简单。
--database => optiomize => oracle tuning advisor(OEM)来使用。
--有必要建立sql语句执行调用来使用sql profile来完成优化工作,简化sql优化工作。

1.建立任务：
set verify off
set long 20000000
set longchunksize  20000000
column report_tuning_task format a100
declare
  a varchar2(200);
begin
  a := dbms_sqltune.create_tuning_task(task_name=>'tuning &1',description=>'tuning sql_id=&1',scope=>dbms_sqltune.scope_comprehensive,time_limit=>1800,sql_id=>'&1');
  dbms_sqltune.execute_tuning_task( a );
end;
/

prompt =================================================================================================================================================
prompt tuning sql_id=&1 : report
prompt =================================================================================================================================================
select dbms_sqltune.report_tuning_task('tuning &1') report_tuning_task FROM dual;

prompt =================================================================================================================================================
prompt if finished,drop tuning task , run:
prompt execute dbms_sqltune.drop_tuning_task('tuning &1')
prompt if accept sql profile, run:
prompt execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE ,name=>'tuning &1');;
prompt execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE, name=>'tuning &1', FORCE_MATCH=>True)
prompt if drop or alter sql profile ,run :
prompt execute dbms_sqltune.drop_sql_profile(name => 'tuning &1')
prompt execute dbms_sqltune.alter_sql_profile(name => 'tuning &1',attribute_name=>'STATUS',value=>'DISABLED')
prompt =================================================================================================================================================

set serveroutput off

2.如果接受sql profile：

execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE,);
--如果sql语句含有文字变量，想使这些语句也有效，可以执行如下：
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE, FORCE_MATCH=>True);

--如果不满意可以:
execute dbms_sqltune.drop_sql_profile(name => 'tuning &1')
execute dbms_sqltune.alter_sql_profile(name => 'tuning &1',attribute_name=>'STATUS',value=>'DISABLED')

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

转载于:http://blog.itpub.net/267265/viewspace-1340660/