ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBA优化之路(zt)

DBA优化之路(zt)

原创 Linux操作系统 作者:tolywang 时间:2007-11-21 00:00:00 0 删除 编辑

http://www.itpub.net/thread-298019-1-1.html

做为一个新手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调整的需要,而且olap应用与oltp应用初始化参数的调整是有很大区别的,通常是对初始化参数经过多次调整,才能达到比较合谐的效果,(http://blog.csdn.net/biti_rainy/archive/2004/07/03/learn_oracle_20040703_7.aspx)这个贴子是biti_rainy关于sga调整的总结,基本可以适合大多数情况。

32bit的操作系统中,sga1.7g的限制,如果相在32bit的操作系统上突破1.7g的限制,就需要使用特殊的手段,(http://www.itpub.net/showthread.php?s=&threadid=124424)这个贴子是coolyl网友针对各个平台sga突破1.7g的限制的总结,sga并不是越大越好,这个最好具体情况,慎重待之。

关于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环境,如果mview基表的事务非常多,那mview的刷新将对系统造成一定的压力;在oltp环境中,规模较大的报表却适合使用mview来提高查询性能。http://www.itpub.net/224536.html这个贴子可以下载到《expert one on one oracle》中文扫描版该书的第13章专门讲述mview的运用

也可以看看本人关于mview所作的测试http://blog.itpub.net/post/96/7535),创建与使用mview一定要小心,特别在分区表上(http://blog.itpub.net/post/96/3809),如果对分区进行分割(alter table &table_name split partition ...)时,该分区表上的mview将不能被fast refresh,这时所有针对该分区表的事务将会被失败。

关于stored outlinessql优化中的运用

stored outlines是为了维持sql执行计划稳定性而推出的功能,主要适用于无法对源代码进行修改等情况下,为了保证产品数据库的良好运行,需要稳定SQL的执行计划,人为调整某些特定的sql的执行计划,需要慎重地确定某个sql所需要的outlines

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

当初始化参数cursor_sharing=EXACT时,如果查询(where id = 2 / where id = 3)等这种情况下,就没有办法使用stored outlined对该类型的SQL进行执行计划的稳定,除非对该SQL先使用bind variable或将cursor_sharing=SIMILARFORCE

dbms_profiler测试存储过程的性能

关于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 5wait event主要由性能不佳的sql引起的;磁盘排序及temp 表空间瀑涨等大多也与sql有关,不排除创建索引与重建索引时引起temp 表空间瀑涨的问题,但这方面的原因应该是dba负责,大表在创建索引或重建索引最好安排在系统空闲时。

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

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

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

如何收集与勘别出性能不佳的sql呢?通常要综合以下性能指标(response time/consistent gets/physical reads/resultset size)进行判断;要根据自己的情况从v$sqlv$sqlareav$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 executions > 0

and (disk_reads/executions > 500 or buffer_gets/executions > 20000);

上面的这个查询主要将physical reads > 500consistent gets > 20000sql语句找了出来,当然你也可以将响应时间也进行限制,通常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/source/oracle/20031218/oracle%20SQL%20performance%20tuning1.html

如何对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

eventsql trace等工具收集正在执行的sql的性能状态数据并记录到跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间、物理读、逻辑读等.这些数据将可以用来优化你的系统.user_dump_dest参数说明了生成跟踪文件的目录,设置sql trace首先要在init&sid.ora中设定timed_statisticstrue, 这样才能得到那些重要的时间状态. 由于sql trace生成的trace文件读起来很困难,所以要用tkprof对其进行转换,TKPROF有许多执行参数,可以参考http://tahiti.oracle.com文档

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#1216(需要到otn注册免费的帐号)。

基于等待事件的性能诊断方法

等待事件(wait event)是oracle核心代码的一个命名部分,有两种类型的等待事件:空闲事件(idle event)与非空闲事件(non-idle event,空闲事件指oracle正在等待某种工作,常见的空闲等待事件:client messagenull eventpipe getpmon/smon timerrdbms rpc messagesql*net等;非空闲等待事件:buffer busy waitsdb file scattered readdb file sequential readenqueuefree buffer waitslatch freelog file synclog file paralle write等。

什么是瓶颈?一旦熟悉了系统的等待事件,就能够把握问题的关键,并能够用相应的方法去处理阻塞系统的瓶颈,一定不要随意的进行优化,否则一波不行又起一波,可以通过v$system_event熟悉系统总的等待情况,然后通过v$session_event查看系统中session的待情况,最后通过v$session_wait定位瓶颈对象。v$session_wait是会话级的,它包含session的实时信息,最重要的是:它显示了等待事件与相应资源的更深入信息,可明确地定位出要优化的范围。

v$session_waitp1p2p3告诉我们等待事件的具体含义,如果wait eventdb file scattered readp1=file_id/p2=block_id/p3=blocks,然后通过dba_extents即可确定出热点对象;如果是latch free的话,p2为闩锁号,它指向v$latch

col event format a32

col name format a32

select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name

from v$session_wait sw,v$latch l

where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'

and sw.p2 = l.latch#(+);

--求等待事件及其对应的latch

col owner format a18

col segment_name format a32

col segment_type format a32

select owner,segment_name,segment_type

from dba_extents

where file_id = &file_id and &block_id between block_id and block_id + blocks - 1;

--求等待事件及其热点对象

select sw.sid,event,l.name,de.segment_name

from v$session_wait sw,v$latch l,dba_extents de

where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'

and sw.p2 = l.latch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1;

--综合以上两条sql,同时显示latch及热点对象(速度较慢)

select sql_text

from v$sqltext_with_newlines st,v$session se

where st.address=se.sql_address and st.hash_value=se.sql_hash_value

and se.sid =&wait_sid order by piece;

--如果是非空闲等待事件,通过等待会话的sid可以求出该会话在执行的sql

通过等待事件找出系统中消耗资源较严重的sql,是dba进行系统诊断的手段之一,只是过程稍嫌烦琐,由于session是动态的,往往是瞬息万变,不可捕获(但有针对性),但可能通过对v$sqlv$sqlarea进行过滤,按[如何对sql进行调整及优化]一节中提供的sql可以找出存在性能问题的sql,长时间地对v$sql进行捕获,并对抓到的sql进行分析处理,可以在很大程度上解决性能问题。

基于资源限制的性能诊断方法

如果想用resource limit功能,就必须将初始化参数resource_limit=true,当然也可以指定相关的resource_manager_plan参数更细致地管理资源;针对某个用户的资源限制,可以通过用户的profile来实现。关于create profile

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

上一篇: Oracle Hint的用法
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13383026