开始写博客并贡献sql优化菜鸟心得

      上周公司三亚年会归来后，好的博客能给晋级加分这条制度让我叹服。当初注册ITPUB，是想在ITPUB博客里记录自己学习成长工作的点点滴滴，但又觉得自己学习总结成笔记后还要往博客挪文字，好费事，遂放弃了写博客。现在新公司里博客这么受重视，也是个人技术、表达能力的体现，我似乎又找到了写博客的动力。
      oracle就像一门科学，其中我最感兴趣的是sql优化，既然开始写博客，就把以前在第一家公司时未公开的菜鸟心得贴出来吧！
      时间推到2年前————
    对于sql优化，我是很敬畏的，其实是“不明觉厉”，现在水平还是很菜，不过还算有些心得，今天就抽空总结一下自己sql优化了解的手段。
    一共9种方法：
    1、重启数据库
    2、刷新共享池
    3、在SQL引用的对象（表、视图等）上执行DDL操作（包括授权）
    4、dbms_shared_pool.purge
    5、修改SQL（包括重组sql，和直接加hint）
    6、outline
    7、sql tuning advisor(dbms_sqltune)
    8、sql profile(dbms_sqltune)
    9、修改、删除、重新收集统计信息
    我对这9条还划分为4大类
    第一类 暴力清除share pool，包含1、2、3、4
    第二类 最基本原始的方法，包含5
    第三类 借用工具调整执行计划，包含6、7、8
    第四类 调整统计信息促使CBO修改执行计划，包含9
    下面就单独介绍下。
    1、重启数据库
    sql语句解析后得到的执行计划存放在内存SGA的share pool中，重启数据库后内存没了，所有语句都要重新解析，有利于一些陈旧的语句生产新的执行计划，但代价太大，我当然没刻意做过，不过每次停电或系统维护后，性能都会稍微变好。
    2、刷新共享池
    alter system flush shared_pool;
    不重启数据库，用这个命令清空share pool，效果和1相同。
    3、在SQL引用的对象（表、视图等）上执行DDL操作（包括授权）
    DDL操作会使对象执行计划重新分析，而授权操作是代价最小的DDL，可以授权给自己或public；但重新分析对象语句的代价可能也很大。
    4、dbms_shared_pool.purge
    以上3种清除share pool的方法确实暴力，而本条方法只针对单个语句，不会误伤，具体参数不说明了，一般用法如下
    select address,hash_value from gv$sql where sql_id='7udw7xcy62gm5';
    exec dbms_shared_pool.purge('address,hash_value', 'c')；
    需要注意的是该功能是11g新功能，oracle官方文档说在10.2.0.4和10.2.0.5中作为补丁添加进来，其中10.2.05可以直接使用，而10.2.0.4需要设置一个事件：
    alter system set event = '5614566 trace name context forever' scope = spfile;
    scope后不能跟memory和both，设置完必须重启数据库才能使用。
    但我自己在10.2.0.4设置事件重启数据库后和10.2.0.5环境中运行该功能都没报错，但都没有清除指定语句的执行计划，后来在oracle官网提供的11gR2 windows 32版本数据库中测试成功。
    5、修改SQL（包括重组sql，和直接加hint）
    这条方法真是最基本原始的了，凡是搞应用开发的都会，但我认为这才是sql优化最显水平的一条路，其他方法都是在围绕出问题的语句修修补补，而只有精通应用、掌握逻辑、熟练sql优化的强力DBA才能在这里称王，其他的都只是一般的工程师，再强调下这个方法能称王的几个必备条件：首先必须是DBA，然后要熟练sql优化，最后得精通应用、掌握逻辑！
    6、outline
    这个我真没用过，只看过，因为是9i以前的利器，而10g后已经被dbms_sqltune取代了，我也没用心看。outline就是可以不修改语句，通过写一条新语句，将两条语句的执行计划交换，达到人为控制目标语句执行计划的目的，但据说可能会引起内存中产生竞争library cache latch。
    7、sql tuning advisor(dbms_sqltune)
    这个advisor可以脱离人力，由系统针对一条语句给出分析过程和优化意见，深一点理解就是创建优化的sql profile，由DBA决定是否采用，与outline相比，优势在于可以调整任何语句，outline只能调整含绑定变量的语句，需要注意的是接受系统意见的accept操作属于暂时调整执行计划，并不锁定，但不能自由手写hint；当环境改变出现更好的执行计划时，CBO会放弃曾经accept的sql profile，有关操作最好在sqlplus中进行，因为要看结果。
set serveroutput on
declare
my_tuning_name varchar2(30);
begin
my_tuning_name:=dbms_sqltune.create_tuning_task('bjc70bkvrp8yp',null,'comprehensive',2100,'hash_full');
end;
/
exec dbms_sqltune.execute_tuning_task('hash_full');
exec dbms_output.put_line(dbms_sqltune.report_tuning_task('hash_full'));
exec dbms_sqltune.drop_tuning_task('hash_full');
exec dbms_sqltune.reset_tuning_task('hash_full');
exec dbms_sqltune.accept_sql_profile(task_name => 'hash_full',replace =>TRUE);
exec dbms_output.put_line(dbms_sqltune.script_tuning_task('hash_full'));
exec dbms_sqltune.drop_sql_profile('SQLPROFILE_NAME1');
    8、sql profile(dbms_sqltune)
    该功能是用dbms_sqltune包中的过程import_sql_profile实现的，这个就能由DBA手写hint，人为产生sql profile了，并且执行计划将是锁定的；只是手写hint时要带上Query Block Name（Query Block Name可以查询plan_table得到，当然，首先要做explain plan；也可以查询v$sql_plan得到），需要的话也要设置replace参数为true。
declare  
    v_hints sys.sqlprof_attr;   
begin  
    v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');   
    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%''       and t1.object_id=t2.object_id',v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);   
end;   
/ 
    9、修改、删除、重新收集统计信息
    统计信息是CBO的基础，10g每天都有自动作业，必要时动手去做、去改、去删吧，重新收集的话要注意系统是否繁忙，修改和删除操作要做好测试、减小风险。

     当时就认定了sql改写是最显功力的，以后要加强sql学习。

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

转载于:http://blog.itpub.net/26614613/viewspace-2056941/