SQL优化----dbms_sqltune详解(1)

SQL优化----dbms_sqltune详解(1)

--转载http://blog.sina.com.cn/s/blog_61cd89f60102edi3.html

作为ORACLE DBA对SQL优化是必备的技能，常用的方法就是看执行计划，加hint或者修改SQL写法，但现实的工作中，往往修改应用的SQL是非常困难的，特别是对电信、银行这个行业，修改应用程序过程十分繁杂，有没有办法不修改SQL就可以达到优化SQL执行计划的方法呢？答案是肯定的，从ORACLE 10g开始ORACLE提供了自动优化功能，经过一段时间的总结运用，和大家分享一下如何运用DBMS_SQLTUNE对SQL进行优化。

    在实际工作中，我们常遇到三种情况下的SQL类型的SQL优化

    1.完整的SQL语句，无绑定变更（最简单的一种，也是不常见的一种）

    2.通过AWR获得的SQL语句，这种SQL有绑定变量

    3.在V$SQL不存在的SQL语句

    针对以上三种情况的优化，和大家分享一下我的优化语句，既然要用这个包，必须要有相应的权限

一、执行DBMS_SQLTUNE所需要的权限

    CREATE ANY SQL_PROFILE,

    DROP ANY SQL_PROFILE

    ALTER ANY SQL_PROFILE

    因此要想普通用户执行DBMS_SQLTUNE包进行优化，需要赋相应的权限

二、无绑定变量SQL的优化

    这种是最简单的一种，通常开发的同事用的比较多，详细SQL如下：

执行优化任务：

declare
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext   := 'select * from orabpel.cube_scope';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,
                                                 user_name   => 'orabpel',
                                                 scope       => 'COMPREHENSIVE',
                                                 time_limit  => 60,  --优化限时60s
                                                 task_name   => 'wxw_sql_tuning_task',
                                                 description => 'tune the bad sql');
  dbms_sqltune.Execute_tuning_task(task_name => 'TEST_sql_tuning_task');
END;

--查看优化结果

set long 10000  --SQL*PLUS中不要忘记执行，否则看不全优化结果（切记）
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('wxw_sql_tuning_task') FROM DUAL;

三、通过SQL_ID进行优化

    这种情况通常常用于v$sql中的异常SQL优化

declare
  l_tuning_task varchar2(30);
begin
  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '9qjctmkwvny7j');
  dbms_sqltune.execute_tuning_task(l_tuning_task);
  dbms_output.put_line(l_tuning_task);--A需要跟据此处输出值进行查询
end;
--查看执行计划
select dbms_sqltune.report_tuning_task('task_27888') from dual; --此处标红值是--A此的输出

     注意，在实际执行过程中，如果sql_id已经不在v$sql中会报错，此时这种方法已经无用武之地了，怎么办呢，下面我们看第三种方法，指定AWR报告中的snap_id进行优化

四、通过指定AWR中的Snap_id进行优化

declare
     my_task_name varchar2(30);
begin
     dbms_sqltune.drop_tuning_task(task_name => 'wxw_sql_tuning_task');
     my_task_name := dbms_sqltune.create_tuning_task(
          begin_snap      => 24365,
          end_snap        => 24366,
          sql_id          => 'd40kghyfbg8sj',
          plan_hash_value => null,
          scope           => 'comprehensive',
          time_limit      => 60,
          task_name       => 'wxw_sql_tuning_task',
          description     => 'tune the bad sql'
     );
     dbms_sqltune.execute_tuning_task (task_name => 'wxw_sql_tuning_task');
  end;
--查看sql优化结果
 select dbms_sqltune.report_tuning_task('wxw_sql_tuning_task') from dual;
     注意，此方法仍有一个弊端，前提需要AWR报告中的Snap_id中在数据库中存在，我们知道，在10g中默认保留七天，11g中默认保留八天，一旦过了这个时间，这种方法也无用武之地，既不存v$sql中，AWR也过期，此时我们还有一种方法进行优化，那就是通过无绑定变量SQL进行优化，但此时会带来一个问题，我们AWR报告中的SQL是带有绑定变量的都是：1 ，：2这种形式，如何转换呢？继续往下看

四、绑定变量还原

    此时分两种情况，一种是在v$sql中存在的SQL，一种是在v$sql中不存在的SQL

1> 转化v$sql中对应的绑定变量值

   跟据SQL_ID取v$sql中bind_data的值
select position, value_string
  from table(dbms_sqltune.extract_binds('beda0a200500521ffd700'));

2>如果v$sql中不存在，可以用此方法获取绑定变量值
select dbms_sqltune.extract_bind(bind_data, 1).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 6).value_string
from sys.wrh$_sqlstat
 where sql_id = '1nnrufx6sw8sd'

     基本上通过以上几种方法足可以应对我们工作需要优化SQL的情况，如何做到不修改SQL优化执行计划，就是我们优化结果中sql_profile，关于优化结果中建议接受sql_profile文件怎么处理？他是怎么影响执行计划的？怎么管理？这个放在第2讲和大家分享！

     注意：转载本文请注明链接，谢谢！