dba麻烦终结者之路

本文转载自ITPUB

或许你厌倦了朝五晚六的开发工作,开始考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网友的贴子。还有就是安装sqlplushelpsql语法的help,具体方法大家可以参考下面这个贴子(http://www.cnoug.org/viewthread.php?tid=1710)。在9i以后的版本中sqlplushelp默认是安装的,sql语法的help就必须自己安装啦。

 

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

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

做为dba,对linux/unix应该有相当的基础。理解raidrawlvmocfsasm等与存储相关的概念;能够安装oracle软件及打补丁;理解linux/unix常用的命令rpmcpiotarftptopvmstatiostatsarnetstatcrontab等;应用服务器的调整有一定的了解;关于linux/unix的问题,可以到http://www.chinaunix.com http://www.puschitz.com/去寻找答案。

关于初始化参数(sga)的调整

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

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

http://blog.csdn.net/biti_rainy

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

32bit的操作系统中,sga1.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设置sessionclient_info,这样在用logmnr进行日志挖掘时,就知道是那个页面执行了这个操作,范围就比较小;在c/s结构的应用中,那是通常每个client连接后,都可能需要很久才断开session,客户每打开某个业务模块,最好用dbms_application_info.set_client_info设置该sessionclient_info信息。

关于materialized view在调优中的运用

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

关于stored outlinessql优化中的运用

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 blockstored procedure的性能测试,在开发阶段程序员或dba需要对开发的各种存储对象进行性能测试,通过dbms_profiler package可以找出存储对象中性能不佳的地方,然后进行改行;可以看出dbms_profileoutline的区别是:一个用于开发阶段,需要修改程序,一个用于正式运行阶段,不必去修改程序,只改变sql的执行计划而已。关于dbms_profiler package的两个贴子:

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

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

   如何对sql进行调整及优化

    优化sql是最能体现dba智慧与价值的地方。通常在statspacktop 5wati 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$sqlv$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 > 1000consistent gets > 50000sql语句找了出来,当然你也可以将响应时间也进行限制,通常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 expertquest toad,鉴与新手对工具的理解有些难度,本人为lecco sql expert写了中文图解。

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

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

    任何工具都是比较低智能的,如果你觉得leccotoad比较顺手,千万勿沉溺其中,它们只是一个拐杖而已,你必须超越它,否则你的价值就值得怀疑;针对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