DBA优化之路(zt)

最新推荐文章于 2024-03-08 15:53:03 发布

cuixie2370

最新推荐文章于 2024-03-08 15:53:03 发布

阅读量106

点赞数

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

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

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=SIMILAR或FORCE。

用dbms_profiler测试存储过程的性能

关于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的wait 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$sql、v$sqlarea、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 executions > 0

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

上面的这个查询主要将physical reads > 500及consistent gets > 20000的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/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

event、sql trace等工具收集正在执行的sql的性能状态数据并记录到跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间、物理读、逻辑读等.这些数据将可以用来优化你的系统.user_dump_dest参数说明了生成跟踪文件的目录，设置sql trace首先要在init&sid.ora中设定timed_statistics为true, 这样才能得到那些重要的时间状态. 由于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 message、null event、pipe get、pmon/smon timer、rdbms rpc message及sql*net等；非空闲等待事件：buffer busy waits、db file scattered read、db file sequential read、enqueue、free buffer waits、latch free、log file sync、log file paralle write等。

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

v$session_wait的p1、p2、p3告诉我们等待事件的具体含义，如果wait event是db file scattered read，p1=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$sql或v$sqlarea进行过滤，按[如何对sql进行调整及优化]一节中提供的sql可以找出存在性能问题的sql,长时间地对v$sql进行捕获，并对抓到的sql进行分析处理，可以在很大程度上解决性能问题。