dba麻烦终结者之路

或许你厌倦了朝五晚六的开发工作，开始考ocp；或许你刚走出象牙塔，立志在数据库管理方面大干一场？经过一翻努力，终于有了份dba的工作，忐忑不安地坐在电脑旁，激动得手心冒汗，却不知如何去调整、优化数据库；面对突如其来的故障，电话响个不停，老板虎视耽耽地站在身旁，不知你些时是否能静下心来？

       可能读了许多数据库管理、调优、备份与恢复、pl/sql开发方面的书，也可能做了很多故障排除的实验，可当故障真正降临时，却显得那么可怕，通常正在运转的生产数据库一直处于性能恶化趋势，麻烦总是从你意想不到的地方出现，阿门。

数据库系统本身永远是的值得注意的麻烦制造者：数不清的bug、对象失效、磁片碎片、索引重建以及很多没有顾及到的突发事件等；没有sql经验的程序员也是很历害的麻烦制造者：编写性能不佳的sql以及创建一些性能较差的存储对象；最可怕的麻烦制造者是谁呢？吼吼，正是来源于dba本身，对数据库一个微小的修改，或许就导致一场灾难。

做为一个新手dba来讲,有关oracle体系统结构的概念非常重要，如果想比较透彻地理解这些概念，必须做大量的实验，书上得来终觉少，绝知些事要躬行，呵呵，千万不要在生产库上进行哦；如果想从麻烦制造者成长为一个麻烦终结者，只顾自己埋头苦学是不够的，毕竟你的生产环境与学习环境产生的故障很有限，通过在相关论坛上阅读贴子，从网友的经验与教训中汲取营养，拓展发现与解决问题的技巧。

独立学习与思考是dba快速成长的关键。许多新手发现系统出现问题或未知的现象，第一时间总是去咨询资深dba，其实这是坏习惯，尽量对问题进行分析与推理，如果实在没有头绪的话，可以在google或相关的论坛上发贴求助，网络上总会有许多意相不到的惊喜，相信90%的问题已经有了答案，关键是如何找到它。

不要对internal的东西费心费神，打好基础才是主要的，要有一定的pl/sql编程技术，牢牢掌握数据库备份与恢复，然后提高系统调优及SQL优化的能力，当技术累积到一定的层次时，对于许多internal的东西自然自然就领会啦。

良好的沟通能力有助于更快地解决问题。很多时间，可能已经解决了问题，却不知为什么会产生这种问题，这时可以咨询一下项目负责人或相关程序员，尽量把问题的根源搞清楚，如果问题没能根本解决，问题必然卷土重来。

作为dba，需要为项目组的程序员提供统一的《数据库开发规范》，如果可能，也可做为程序员做sql编写及sql优化技巧方面的培训，尽量让性能不佳的sql胎死腹中，新手dba，更要融入项目组，理解业务系统的需求，并掌握一定的数据库建模知识，通过对数据库结构的掌握，为数据库结构优化与sql优化打下基础。

努力学习对dba是必不可少的，需要注意的是：并不是方方面面的知识都需要熟记硬背。有选择地去深入研究某个方面的技能，才能突破泛泛之境；不要太在意研究配置dataguard、安装rac等琐事，雕虫小技而已；（http://www.cnoug.org/viewthread.php?tid=2226）这是piner网友收集整理的oracle faq，相信无论新手熟手，都是可以翻翻的。

“工欲善其事，必先利其器”，做为dba来讲，必须为自己及程序员搭建顺手的工作环境（本文以linux平台为例）。在linux平台上，sqlplus是不具有回调功能的，如何搭建具有回调环境的sqlplus呢？（http://www.dbanotes.net/Oracle/uniread-howto.htm）大家可以参考fenng网友的贴子。还有就是安装sqlplus的help及sql语法的help，具体方法大家可以参考下面这个贴子(http://www.cnoug.org/viewthread.php?tid=1710)。在9i以后的版本中sqlplus的help默认是安装的，sql语法的help就必须自己安装啦。




最需要新手注意的网址：http://tahiti.oracle.com  http://metalink.oracle.com

关于操作系统/网络参数的调整


做为dba，对linux/unix应该有相当的基础。理解raid、raw、lvm、ocfs、asm等与存储相关的概念；能够安装oracle软件及打补丁；理解linux/unix常用的命令rpm、cpio、tar、ftp、top、vmstat、iostat、sar、netstat、crontab等；应用服务器的调整有一定的了解；关于linux/unix的问题，可以到http://www.chinaunix.com http://www.puschitz.com/去寻找答案。

关于初始化参数（sga）的调整


深刻理解oracle的初始化参数是dba必不可少的功课，却不能把调整参数做为提高性能的救命稻草，不合适的参数必将带来性能上的下降，甚至数据丢失的危险；不要以为使用隐藏参数为荣，做事要有未雨调缪的打算，在系统故障时可以坦然对之。

没有任何工式可以满足sga调整的需要，通常都是经过多次调整，才能达到比较合谐的效果，

http://blog.csdn.net/biti_rainy

这个贴子是biti_rainy关于sga调整的总结，基本可以适合大多数情况。

在32bit的操作系统中，sga有1.7g的限制，如果相在32bit的操作系统上突破1.7g的限制，就需要使用特殊的手段，

（http://www.itpub.net/showthread.php?s=&threadid=124424）这个贴子是coolyl网友针对各个平台sga突破1.7g的限制的总结。

在64bit的操作系统中，sga不需要特殊方法可以上到3.9g，如果想突破4g的话，方法与32bit系统中突破1.7g的方法类似，也就是说必须使参数use_indirect_data_buffers=true，然后使用db_block_buffers来设置buffer cache的大小。

关于statspack的若干建议


不要对statspack报太大希望，它只能告诉你过去某段时间数据库的运行状态，以及预测将来一段时间的性能趋势（初始化参数没能重大调整及业务没能巨剧变化的情况下），通过statspack的报表，dba可以对初始化参数进一步进行微调。

statspack可以告诉你性能瓶颈所在，仅此而已，引起性能瓶颈的根本原因必须dba亲自动手查；当然引起性能瓶颈的原因也可能已经收集到啦，在众多收集到的sql中需要仔细斟别哦，如果sql语句太长，就比较麻烦，因为在statspack中，过长的sql会被截断的；无论如何，statspack都是dba不可却少的助手，（http://www.eygle.com/more/statspack_list.htm）这是eygle网友关于statspack的系列研究贴子，希望对你有用。

如果你需要经常制做statspack的性能趋势报表，一般可以用excel来做，就是麻烦了一些，偶写了一款专门制做statspack报表的工具，不仅可以更快更方便地制作出漂亮的报表，而且可以对知识进行管理。（http://www.cnoug.org/viewthread.php?tid=20115）

关于logmnr在调优中的运用


一直以来，logmnr都不是调优所推荐的工具，主要用于安全审计方面，其实在追究系统瓶颈上logmnr可是得天独厚，通过对日志的审查（需要dba有足够的耐心哦），可以更清楚地知道oracle在某段时间内做了什么，这样做是不是合理？当然logmnr并不能告诉你什么合理，你必须自己判断。

在b/s结构的应用中，在session连接时用dbms_application_info.set_client_info设置session的client_info，这样在用logmnr进行日志挖掘时，就知道是那个页面执行了这个操作，范围就比较小；在c/s结构的应用中，那是通常每个client连接后，都可能需要很久才断开session，客户每打开某个业务模块，最好用dbms_application_info.set_client_info设置该session的client_info信息。

关于materialized view在调优中的运用


在olap环境中，mview是以空间换时间的一种有效手段，更少的物理读/写，更少的cpu时间，更快的响应速度，所以它不适合高端的oltp环境；在oltp环境中，规模较大的报表适合使用mview来提高查询性能。（http://www.itpub.net/224536.html）这个贴子可以下载到《expert one on one oracle》中文扫描版，该书的第13章专门讲述mview的运用。

关于stored outlines在sql优化中的运用


stored outlines是为了维持sql执行计划稳定性而推出的功能，主要适用于测试环境到产品数据库环境的迁移、当搜集统计信息以采样方式运行、搜集统计信息可能给某些特定SQL带来危害、无法对源代码进行修改等情况下，为了保证产品数据库的良好运行，我们需要稳定执行计划。人为的调整某些特定的sql，我们可以使用sql谨慎的确定某个sql所需要的outlines。（摘自biti_rainy原话,原url如下。）

（单击此处的url将不能打开相关链接，拷贝到ie地址栏中即可）关于stored outlines的使用，

http://blog.itpub.net/post/96/1548 也可以参考本人拙作。曾对stored outlines抱有厚望，但在实际运用中却发现outlines并不是那么很好伺候，一般当sql使用bind variable的情况下用outlines来稳定计划会更合适一些。


当初始化参数cursor_sharing=EXACT时，如果查询条件不同，就没有办法使用stored


outlined；如果把业务逻辑封装在stored procedure中，procedure中的变量将以bind variable的形式出现，这时可以用stored outlines来稳定执行计划，具体操作见本人拙作；如果sql中没有文本变量（常数），则可以用stored outlines。


   如何用dbms_profiler测试stored procedure


    关于dbms_profiler package主要用于pl/sql block与stored procedure的性能测试，在开发阶段程序员或dba需要对开发的各种存储对象进行性能测试，通过dbms_profiler package可以找出存储对象中性能不佳的地方，然后进行改行；可以看出dbms_profile与outline的区别是：一个用于开发阶段，需要修改程序，一个用于正式运行阶段，不必去修改程序，只改变sql的执行计划而已。关于dbms_profiler package的两个贴子：


    http://www.samoratech.com/PLSQLProfiler.htm


    http://pages.videotron.com/orautils/pages/dbms_profile.htm


   如何对sql进行调整及优化


    优化sql是最能体现dba智慧与价值的地方。通常在statspack的top 5的wati event主要由性能不佳的sql引起的；磁盘排序及temp tablespace瀑涨等大多与sql有关，不排除创建与重建索引这方面，但这方面的原因应该是dba负责，大表在创建或重建索引必须在系统空闲时。


性能不佳的sql是如何产生的呢？这里面问题就比较复杂一些：不良的数据库结构必将导致不良的sql；还有就是程序员的sql编写技能引起的；不要奢望程序员是sql编写方面的专家，根据偶自己做开发的经历，最快时间完成项目才是最重要的，所以程序员不会太关心sql的性能，即是关心，也是很有限的。


对程序员进行合适的关于sql优化的培训，提高他们的责任感，针对系统中出现的案例进行讲解，程序员潜意识中就会努力避免很多低级的错误；要多与程序员交流，尽量引导程序员描述他在数据库方面感到困难的地方，并提出指导性意见及解决方案。


对新手dba而言，通常都很有兴趣对系统参数或sql进行调优，却不知如何动手。在系统参数方面本身要有一定的理解，也可以请教与资深dba进行探讨，性能提高上奉劝不要抱太大的希望，也可以根据statspack的报表进行分析，对系统参数进行微调；在sql调优方面，必须能够勘别出性能不佳的sql。


如何勘别出性能不佳的sql呢？通常要综合以下性能指标（response time/consistent gets/physical reads）进行判断；要根据自己的情况从v$sql或v$sqltext_new_withlines字典表中把符合条件的sql查询出来：


set lines 99


col sql_text format a81


col bgets_per format 99999999.9


set long 99999999999


set pagesize 9999


select address,hash_value,disk_reads,elapsed_time/1000000 as


"elapsd_time(s)",cpu_time/1000000 as "cpu_time(s)",


       buffer_gets/executions bgets_per,first_load_time,sql_text


from v$sql


where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 50000);


    上面的这个查询主要将physical reads > 1000及consistent gets > 50000的sql语句找了出来，当然你也可以将响应时间也进行限制,通常onsistent gets较大或physical reads较大的sql，它的response time也必然会比较大。


    如何在sql执行时产生执行计划呢？在sqlplus上输入set autot on就可以产生比较详细的执行计划；set autot off是让sqlplus取消产生执行计划；set autot traceonly只显示sql影响的行数、执行计划、执行的统计信息、不输出结果集；set autot on exp输出执行后的结果集及执行计划；set autot on stat输出执行后的结果集及统计信息。explain plan只对sql进行分析，产生执行树，用select * from table(dbms_xplan.display)输出explain plan产生执行计划。


set autot[race] {off|on|trace[only]}[exp[lain]] [stat[istics]]


explain plan [set statement_id = &item_id] for &sql; 


select * from table(dbms_xplan.display);


    如何对性能不佳的sql进行优化，想来对任何一个dba都有挑战性。在这个环节上，dba必须掌握如何查看sql的执行计划，并对返回的结果有一定的了解；如果是新手，可以借助一些sql优化工具进行调优,可借用的工具有lecco sql expert及quest toad,鉴与新手对工具的理解有些难度，本人为lecco sql expert写了中文图解。


    sql expert 教程 http://www.cnoug.org/viewthread.php?tid=22327


    quest toad 教程 http://www.cnoug.org/viewthread.php?tid=3242（向原作者致谢）


    任何工具都是比较低智能的，如果你觉得lecco或toad比较顺手，千万勿沉溺其中，它们只是一个拐杖而已，你必须超越它，否则你的价值就值得怀疑；针对sql的优化，必须自己多动手测试，而且也要阅览众书，从别人的经验中激发灵感。


    在优化sql时，需要一层层地对sql进行分析。首先对sql的语法进行分析，剔除冗余的或错误的查询条件（有可能是程序员手误），花得工夫不是很多，性能可得到极大的提高，不要太相信程序员，他们写得必未正确；其次对sql涉及表的结构进行分析，特别是复杂的sql，要检查是否有更佳的连接路线，连接字段是否有索引，索引的选择性如何等；第三偿试用不同的hints改变表的的驱动次序。http://www.adp-gmbh.ch/ora/sql/hints.html 这个贴子是oracle hints的一个列表,hints具体用法可查http://tahiti.oracle.com。


    关于sql调优的细节很多，不可能一一列举，具体环境必须以执行计划为准，通过对sql的理解，提升到对数据库结构的合理性进行揣测，合理的数据库结构，将对sql的性能有较大的提高；有些情况下，修改了数据库结构，并不需要在程序上进行相应的改动，比如将大表进行分区、创建mview等。关于sql优化大家也可以好好研究一下网友black_snail的系列贴子，有详细的示例：http://www.dbonline.cn


   如何对session进行跟踪及tkprof的使用


    跟踪session的活动，oracle提供了很多种手段，不仅可以对当前连接的session进行跟踪，也可以对其它用户的session进行跟踪；通过对trace文件的分析，不仅可以掌握该session的活动，也可以找出这个session中的瓶颈所在，对session的跟踪是dba进行系统调优、故障诊断的常用方法。


    alter session set sql_trace=true/false


    对当前会话的活动进行跟踪及停止跟踪


exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);


    可以对当前session、其它用户的session进行跟踪及停止跟踪


alter session set events '&event trace name context forever,level &level';


alter session set events '&event trace name context off';


exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');


oradebug event 10046 trace name context forever,level 12


关于event跟踪的详细论述大家可以参考hrb_qiuyb的贴子：


http://blog.csdn.net/hrb_qiuyb/archive/2004/06/30/30559.aspx


dba终结者之路2 : http://blog.itpub.net/post/96/15634