Oracle 执行计划调整与固定（Automatic-SQL Profile）

         众所周知，SQL的查询快慢直接影响用户的使用体验，而一个SQL的执行速度主要取决于它采用何种执行计划，在常年累月的系统使用中，数据的不断积累，很可能曾经飞快的SQL变得颤颤巍巍，一秒十卡。其幕后元凶就是表数据的增加造成了优化器对当前执行计划的篡改。

         本章介绍一项有效固定执行计划的方式，大家通过该方式不仅可以固定执行计划，还可以进行SQL调优等操作。

一、创建SQL Profile自动调整任务

Declare
    mTaskName  Varchar2(30);
    mSqlText  Clob;
Begin
    mSqlText := 'Select /*+ no_index(sls idx_sls) */ * From sls Where n=''1''';
    mTaskName  := dbms_sqltune.create_tuning_task(
                             sql_text => mSqlText ,
                             user_name => 'AIKES',
                             Scope     => 'COMPREHENSIVE',
                             time_limit => 60,
                             task_name => 'Aikes_tune_task',
                             description => 'Task to tune a query on table sls');
End;

1、mSqlText：待优化的SQL，这里需要注意单引号的转义写法，并且SQL末尾不能有分号

2、user_name：当前数据库登录用户名

3、task_name：当前优化任务的名称

 

二、执行sql调整任务

Begin
    dbms_sqltune.execute_tuning_task(task_name => ' Aikes_tune_task ');
End;

1、task_name：前一步创建好的优化任务名称

 

三、查看SQL优化报告

查询该SQL，打开CLOB内容即为优化报告。

Select dbms_sqltune.report_tuning_task(task_name => ' Aikes_tune_task ') From dual;

1、SQL优化

        优化报告会提示一些基本的优化方式，例如：收集统计信息、创建索引等等。

2、固定执行计划

        若没有可优化的方式，则会提示找到了性能更好的执行计划，大家可以执行推荐的指令进行执行计划固定，之后，该SQL的查询会一直走此执行计划。

注：在执行优化语句后，默认为全SQL匹配优化，例如：条件为n=1，则优化只针对n=1的sql，通过给优化语句增加参数 force_match => True，可以实现同类型语句都达到优化效果（绑定变量生效）。

begin
    dbms_sqltune.accept_sql_profile(task_name => 'Aikes_tune_task',task_owner => 'AIKES', force_match => True ,replace => TRUE);
end;

        本例中，我将待优化的SQL加上了不使用索引的hint，所以此时的查询是全表扫描。若执行了SQL Profile推荐的指令后，它将会采用走索引的执行计划，这样SQL中的hint不再生效，从而实现了不改变SQL文本情况下优化SQL。

 

四、删除优化任务

        通过该SQL可以查看当前有哪些优化任务：

Select * FROM Dba_Tune_Mview Where owner = 'Aikes';

        通过该指令删除优化任务：

Begin
       dbms_sqltune.drop_tuning_task(task_name => ' Aikes_tune_task ');
End;

五、删除固定的执行计划

         通过查看SQL的统计信息，可以在最下方看到该SQL使用了何种SQL Profile

        可以看到，此时执行计划走的是索引，在NOTE栏写着该SQL使用了SQL Profile。

 

        通过该指令删除固定的执行计划：

Begin
  dbms_sqltune.drop_sql_profile(name => ' SYS_SQLPROF_0168372e2f030000 ');
End;

 

        执行完成后再次查看，SQL变成了全表扫描，固定的执行计划也已经被删除。

 

七、总结

         通过SQL Profile 进行固定执行计划，一定程度上可以实现不改变SQL文本而提升SQL执行效率，但是随着时间累积，数据量增加、索引变动等等，都会导致被固定的执行计划效率变低，又需要重新调整执行计划，若当时做优化的开发人员现在已经离职，重新排查必定是个不小的工作量。

         所以，凡事无绝对，把握好调优的天平才能做到真正的得心应手。