【大话IT】身怀数据库优化绝技，如何进行oracle性能诊断？

迷倪小魏

讲述一个我在实际工作中的oracle性能诊断的例子-------Oracle 碎片优化：
SELECT * FROM aaa where rownum<100

call count cpu elapsed disk query currentrows
------- ------ -------- ---------- -------------------- ---------- ----------
Parse 1 0.14 0.17 44 198 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 3.71 5.86 67489 68340 0 99
------- ------ -------- -------------------- ---------- ---------- ----------
total 10 3.85 6.03 67533 68538 0 99

从这上面看，确实产生了67533个物理读和68538个逻辑读。执行时间为6.03秒。从等待事件来看：
BINDS #39:
EXEC #39:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1422207486718
WAIT #39: nam='SQL*Net message to client'ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=206418 tim=1422207486810
WAIT #39: nam='SQL*Net more data to client'ela= 203 driver id=1650815232 #bytes=2002 p3=0 obj#=206418 tim=1422207487071
.......
      确实存在大量的DB FILE SCATTERD READ。表里存在大量的碎片。找第一个SCATTERD READ的参数 file#=146 block#=92900，让客户执行alter system dump datafile 146 block min 92900 block max 92904。

获得的结果如下：
data_block_dump,data header at0x6000000000208e64
===============
tsiz: 0x1f98
hsiz: 0x4c
pbl: 0x6000000000208e64
bdba: 0x24816ae4 76543210
flag=--------
ntab=1
nrow=29
frre=0
fsbo=0x4c
fseo=0xf7
avsp=0x1f4c
tosp=0x1f4c
0xeti[0] nrow=29 offs=0
0x12ri[0] sfll=1
0x14ri[1] sfll=2
0x16ri[2] sfll=3
......
block_row_dump:
end_of_block_dump

里面全部是空块。建议客户做一个ALTER TABLE <table> MOVE;表重组后，发现原来10G的表只剩下650M了。再执行这个SQL，只有12个BUFFER GET了：

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
1 physical reads
0 redo size
18921 bytes sent via SQL*Net to client
558 bytes  received via SQL*Net from client
8 SQL*Net roundtrips to/from client

    从这个例子里可以看到优化的一个流程。遇到SQL 的问题，可以做10046和10053 事件，获取详细的信息，通过trace，分析原因，找到原因后，就可以解决问题，这里发现是碎片的问题，通过Move table 后，解决了碎片的问题，SQL 的性能得到提高。

jrtongxin5266

我去，这造假太有意思了，到是改改内容啥的...

Antilog

这本书不错哟，在图书馆看过

wuweilong

1 请讲述你在实际工作中最满意的一次oracle性能诊断经过？
答：本人偏向运维这一块，真正优化这一块做的不是太多，接触过的几个优化案例基本都是通过Oracle的SQL Tuning Advisor来完成的。
   
2 请讲述你认为的oracle性能诊断和Oracle开发之间的关系
答、开发质量对数据库的性能影响是非常直接的，我遇到过一个1.6亿行的大表居然没有做分区，应用里面是一条基于该表id列的hash joine的查询，访问效率非常慢，而且没有用索引，效果非常慢，后面将两张表进行整合并且通过在线重定义DBMS_REDEFINITION做了基于虚拟列的分歧。

3  请讲述你在oracle性能诊断方面的失误及教训
答：也许是我经历的不够复杂，经验不多，当然也可能是我谨慎的性格原因；目前为止我只有提高性能的，没有降低性能的事件发生。我我对自己的要求都是看完了，想好了再说 ，诊断过程中的异常分析过后再定论。而且有的时候要看高峰时段，有的时候要看一个业务周期结合来看。不能只看一个方面。

4  请推荐你在oracle性能诊断方面使用的工具  
答：比如常用的AWR,ADDM,DBMS,DBPLAY,SQL TRACE 根据出现的性能问题及场景进行选择。

sp60cn

1 请讲述你在实际工作中最满意的一次oracle性能诊断经过？
之前大部分的时间是在OLAP数据库上做SQL优化，优化了许多关键业务，从运行几小时优化到几分钟。有从技术层面上的也有从业务层面上的。都挺有成就感的。
但说到最有成就感的，就属前几个月的一次。刚跳槽到新公司，ERP数据库长期（5个月了）存在性能的问题。每到月初都会因为IO的瓶颈导致数据库卡死。
这个问题咨询了顾问、厂商均没办法得到有效的解决。
老员工把AWR给我看，当时就觉得这个案例似曾相识。原来跟我们的小Y大师的
http://www.itpub.net/thread-2051954-1-1.html
这个精华帖的内容十分相似。按照其中的思路优化后。问题瞬间解决。月初再也没卡过了。在领导心中的地位立即上升了不少。在此感谢小Y大师的分享。

2 请讲述你认为的oracle性能诊断和Oracle开发之间的关系
我跟开发关系都挺好的，遇到性能的问题，大部分先跟开发先了解下业务上内容，然后再进行优化。一般开发都挺配合。
有的时候告诉开发这个SQL有问题，他们就自己优化了。省事了。

3  请讲述你在oracle性能诊断方面的失误及教训
还是菜鸟时，发现个SQL需要优化，建个索引性能能提高N倍，当时就很开心的往生产上直接建了索引。
一张核心表，数据量大，索引建的比较久，因此导致部分应用无法运行。
教训：不要轻易的在生产环境进行操作，要十分的慎重。业务知识的非常重要的，很多时候比技术更重要。

4  请推荐你在oracle性能诊断方面使用的工具 ；
SQL Tuning Advisor  、AWR  、SQL TRACE 等。

rql21

xuexiaogang 发表于 2016-12-12 10:58
1 请讲述你在实际工作中最满意的一次oracle性能诊断经过？
答：在我工作中，在第二个单位有过一次最大限度 ...

说的很好，赞同！

rql21

jrtongxin5266 发表于 2016-12-15 14:44
1 请讲述你在实际工作中最满意的一次oracle性能诊断经过？
实话实说，原来做的开发虽然用过oracle，但是没 ...

大胆尝试，小心求证

rql21

迷倪小魏 发表于 2016-12-15 18:45
讲述一个我在实际工作中的oracle性能诊断的例子-------Oracle 碎片优化：
SELECT * FROM aaa where rownum

以前也碰到过这样的情况

lastwinner

1 请讲述你在实际工作中最满意的一次oracle性能诊断经过？
答：了解业务，熟悉开发，略懂数据库，因此而做过的性能问题诊断，我都很满意。

2 请讲述你认为的oracle性能诊断和Oracle开发之间的关系
答：开发产生的问题通常就是表结构设计不当、索引设计不当、SQL和PL/SQL使用不当等引发的性能问题，但是Oracle性能诊断包含的方面可不仅仅是这些，除了Oracle数据库架构、参数配置等，它还涉及到网络、存储、操作系统、应用等。

3  请讲述你在oracle性能诊断方面的失误及教训
答：也许是我经历的不够复杂，所以我还是犯过一些错误的，教训就是思考要更全面才能更多的避免问题产生，详阅4。

4  请推荐你在oracle性能诊断方面使用的工具
答：自己写脚本去查性能数据，而不是单纯依赖于AWR，因为一个AWR只是一个独立时间段上的数据库性能表现，它很可能会误导你的判断。我就曾经被误导过，以致于得出了不正确的结论，但还好在正式版报告发出前得到了纠正。
另外会使用Z3作为SQL审核的工具，来自动化分析SQL存在的问题，毕竟很多数据库的性能问题都源自于不良的设计和糟糕的SQL，所以从SQL上下手还是很有成效的。
Z3简介： http://www.enmotech.com/web/classify/25.html

xuexiaogang

rql21 发表于 2016-12-20 09:28
说的很好，赞同！

谢谢，感谢认同