问题描述
最近收到一个同事的一个问题，一条sql语句原先执行1分钟就可以执行完成，但是最近几天执行半个小时都没有执行完成，我让对比了一下sql的执行计划，发现sql执行计划确实不一样了。
其实也可以通过跟踪10053事件发现执行计划的生成过程，由于不在现场操作比较麻烦，所以就建议对执行计划进行绑定。
绑定执行计划最常用的是通过hint、sql profile、spm，但是hint需要修改应该用代码，我就建议先使用sql profile看看是否可以选择正确的执行计划，如果不行就使用spm在进行测试。
这里说一下sql profile跟SPM的主要区别，SPM可以对执行计划进行绑定，绑定的执行计划优先级最高，而sql profile其实是在profile追加了一些补充信息，oracle可以根据profile中的信息选择更优的执行计划。
sql profile步骤
1、查找到sql_id
SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%sql文本%' and sql_text not like '%v$sql%';
2、创建优化任务
SQL> begin
SQL> dbms_sqltune.create_tuning_task(sql_id => '18n2httn72ub0',task_name=>'my_profile_test');
SQL> dbms_sqltune.execute_tuning_task('my_profile_test');
SQL> end;
SQL> /
3、查询优化报告
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> SELECT dbms_sqltune.report_tuning_task('my_profile_test') FROM dual;
主要关注以下内容
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 97.65%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_profile_test',
task_owner => 'SYS', replace => TRUE);
根据建议接受profile
4、接受profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'my_profile_test',replace => TRUE,force_match=>true);
参数force_match默认值是false，只有在SQL文本完全匹配的情况下才会应用SQL Profile，这种情况下只要目标SQL的SQL文本发生一点变动，原有的SQL profile就将失去作用，所以为了避免这种情况应用程序应建议使用绑定变量。
5、如何检查是否使用了我们创建的profile
查询如下视图检查我们的profile
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
NAME TYPE STATUS FOR
-------------------------------------- ---------------
my_profile_test MANUAL ENABLED YES
执行sql执行计划检查是否应用了我的profile，执行计划中会存在以下内容
Note
-----
- SQL profile "my_profile_test" used for this statement
查询v$sql视图中，sql_profile列会展示我创建的profile_name
SQL> select SQL_ID,sql_text,sql_profile from v$sql where sql_id='18n2httn72ub0';
SQL tuning advisor
Oracle 11g中增加了自动优化任务，他是从从AWR中基于每周，每日，每小时来提取TOP SQL，交给advisor进行分析优化，默认每天晚上22点开始执行，oracle不自动实施在调整过程中生成的所有sql profile，它只实施哪些提高性能至少3倍的SQL概要文件建议，另外他还会生成创建索引等建议。
一、检查任务状态
1、检查是否开启状态，ENABLED表示开启
SQL> select status from dba_autotask_client where client_name='sql tuning advisor'；
2、检查任务从周一到周日时间窗口状态，ENABLED表示开启
SQL> select window_name,sql_tune_advisor from dba_autotask_window_clients；
3、查看每天执行时间
SQL> select * from dba_scheduler_windows；
4、检查是否自动接收profile，表示如果自动优化任务建议使用profile，则会自动接受（生产环境不建议开启），默认是false
select * from dba_advisor_parameters where task_name='SYS_AUTO_SQL_TUNING_TASK' and parameter_name ='ACCEPT_SQL_PROFILES';
修改参数ACCEPT_SQL_PROFILES的值
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'ACCEPT_SQL_PROFILES', value => TURE);
END;
/
二、启用/禁用自动调整任务
begin
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => 'NULL',
window_name => 'NULL');
end;
begin
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => 'NULL',
window_name => 'NULL');
end;
三、查看自动任务生成的调整报告
1、查询任务的一些运行结果和状态
select * from dba_advisor_task;
2、查询自动任务的具体行动执行信息
select * from dba_advisor_actions;
3、查询自动任务的调整报告，需要使用dba权限的用户
3.1、查看系统自动优化建议(报告前一天的)，'SYS_AUTO_SQL_TUNING_TASK’是系统自定义的名称。
select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;
3.2、查看一个范围内的报告，begin_exec和end_exec取自dba_advisor_actions中的execution_name 字段的值
select dbms_sqltune.report_auto_tuning_task(begin_exec => 'EXEC_23343',end_exec => 'EXEC_23347') from dual;
3.3、查看execution_name的调优报告，dba_advisor_actions中的execution_name 字段的值
select dbms_sqltune.report_tuning_task(task_name => 'SYS_AUTO_SQL_TUNING_TASK', execution_name => 'EXEC_23343') from dual;
3.4、查看某个对象的调优报告，dba_advisor_actions中的object_id 值
select dbms_sqltune.report_auto_tuning_task(object_id => '28711') from dual;
展示的报告跟手动执行的报告一致
