加载中…
个人资料
王显伟
王显伟
  • 博客等级:
  • 博客积分:0
  • 博客访问:1,366,348
  • 关注人气:2,791
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

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

(2013-09-14 22:47:46)
标签:

dbms_sqltune详解

sql优化

王显伟

sql_profile

自动优化sql

分类: ORACLE新特性实践

    作为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讲和大家分享!

     注意:转载本文请注明链接,谢谢!

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有