【专家答疑】每个Oracle DBA说不出的痛—性能优化

性能优化是每个Oracle DBA说不出的痛，也是难点。你可能已经在互联网的各种技术博客和论坛中学习到了一些关于Oracle优化的内容。但是有些内容是如此的散乱，以至于当你真的想要开始做优化时，根本不知道如何入手。

ITPUB技术论坛特邀《Oracle数据库性能优化实践指南》作者，霜月琴寒 论坛ID: 霜月琴寒,针对Oracle数据库性能优化问题给予解答，欢迎网友积极提问，与专家一起讨论!

专家简介：霜月琴寒，本名王鹏，1999年毕业后一直从事测试工作，最近6年一直在做性能测试。目前在东软软件有限公司主要从事社保系统的研发，负责各地市性能测试项目。在刚开始学习Oracle优化时，发现关于Oracle优化的内容非常的散乱，强烈地感到：一本权威、系统的Oracle 优化指导书是多么的重要。于是总结出《Oracle数据库性能优化实践指南》，希望能让读者少走一些弯路，在性能优化的道理上演着正确的方向前进。

讨论话题：

1.性能优化包括几个方面？

2.哪些工具和特性可以帮助进行性能优化？

嘉宾：我也在这里分享一下对话题的理解：

我们有一些任务，有一些资源，然后我们使用一些方法将任务在资源上完成。这个过程中，优化方法可以被归类为三个方面：

任务：减少任务，例如：压缩，转储表，将表不常用的列挪到另一个表中，分区（减少扫描块），降低高水位线（减少扫描块），用快速全索引扫描代替全表扫描（索引一般比表的数据少）。客户端和服务器端缓存，函数缓存等

资源：优化资源，也可以增加资源（有时增加资源不起作用，因为有瓶颈存在，资源无法被利用。例如，经常会看到CPU只能利用到20%，无论负载如何增加，CPU利用率也上不去）：增加磁盘（不是容量），以增加IO带块，条带化磁盘，优化内存，设置合理的内存参数

方法：批量，并行，异步，直接路径等

在不同组件上，优化的方法名字不同，但实际的原理就限于那么几个，只是在不同的地方叫不同的名字而已。几乎所有的优化方法，都可以归结到以上三个大的分类中。在学习优化的具体方法中，我们可以试着自己进行这样的归类，这样，我们会对优化有一个由复杂到简单的理解。

Q:数据库性能优化的作用有哪些呢？能请专家讲讲不。还有SQL优化真的有必要报个班去专门学习一下吗？有很多培训班有SQL优化的课程，不知道通过课程是否真的能学到强大的技能？

A:不敢说自己是专家。我就是一个性能测试工程师而已。说道数据库的性能优化的作用。我试着这样解释一下： 在一个系统中，当出现性能瓶颈时，这个瓶颈可能会出现在任何一个组件上。比如：操作系统，网络设备，中间件或者数据库。当我们发现系统的整体的吞吐量不能满足要求时，我们首先会定位瓶颈发生的位置。如果发生在数据库中就要针对其进行优化了。 而优化数据库的目的和在其他地方的优化的目的是一样的，就是提高系统吞吐量，当然，这个吞吐量是在一个可以接受的响应时间的前提下。 想要做好数据库优化，自己的理解啊：如果你把任何对象当成一个黑盒子，那么我们最多只能是使用它，想要优化它一定要把黑盒子打开，了解原理，才能做优化。而且，在开始时系统性比细节更重要，开始主要是理解，操作可以稍微少些，主要是理解原理，知道一个事情从前到后到底是怎么发生的。看一些，系统性介绍原理 的书，而且要看权威的，正确的观点的书。 至于培训班，我真是不能给你什么建议。因为我不了解，没有上过优化的培训班。 想学习肯定是好事，我也有你这样不知如何学习的时候。还是先找些书看看吧，反正我是这么摸索着学过来的。看书的时候，如果遇到好书，要看个十几遍的。还要不断看看书的目录，目录就是知识的架构，是有层次的。什么时候，你可以看到目录就想到其中的内容，说明你就是真的理解并且记住了。好好努力吧，有个一两年，只要努力，进步会很快的。 Oracle优化，每人都有自己的观点了。我个人的理解：可以分为两大方面，实例的优化和SQL的优化。 实例是由一些内存结构和后台进程构成的，优化也集中于此。 SQL的优化，根据优化的方法可以分成SQL和PL/SQL的优化。单个SQL的优化可以使用SQL优化顾问和SQL访问顾问，PL/SQL的优化可以借助工具SQLProfile。 对于，实例的优化除了单实例以外，还涉及到RAC的优化，也可以根据这个来划分。 RAC的优化主要分成两个方面：内部互联和LMS的优化。 内部互联值得是RAC各个节点之间的私有网络，用于实现对共享内存的模拟。 LMS主要是指RAC的一些相关服务，主要用来构造请求的块，发送请求的块等等动作。 AWR报告是我个人目前比较依赖的一个性能数据分析工具，它的数据量很大也很全。 看AWR报告，要从整体到局部。 先看是否是RAC环境，这决定了不同的思路。如果是RAC，就要首选看下各个节点负载是否平衡。GC中请求一个块是否高于1毫秒，是否有超过1%的块丢失。这些现象都可能预示着RAC的特有问题。 操作系统统计信息：如果这里看到操作系统的资源例如CPU很高了（85%以上），就知道资源已经利用率非常高了，要警觉了，由此表现出的一般是比较严重紧急的问题。 然后看下%busyCPU,如果这个值挺高，就说明Oracle在忙的CPU中占比较高，也就是说，系统的CPU过高是Oracle引起的，否则，可能是同一服务器上的其他应用引起的。 然后看下时间模型：这里有各种比较严重的等待事件。 如果是DBCPU非常高，但是性能仍不满足要求，就要想办法降低CPU了。消耗CPU的比较常见的的是：硬解析，排序，哈希操作，计算。 AWR报告中有一个Summary部分，在分析的开始可以先看看这个部分。一般仔细分析后，就可以有个大致问题的判断了。然后可以根据问题的不同，在下面报告的细节中找到相应的现象。如果现象和推论不一致，就要调整思路，重新在大脑中建立问题的模型了。

Q:对于突发增加的流量，如何在数据库层面应对？直接cache?比如互联网中的促销?

A:就个人理解解释一个常见问题：锁和栓锁到底有什么区别？（个人理解，不一定准确，大家讨论哈） 为了解决共享问题就需要一个机制防止同时访问同一个数据。就看这个数据放在哪里了，如果放在表和索引中，那么就在这个容器上（表和索引上）加上锁，如果数据放在内存结构中，就在其上加上锁，不过名字不同，一个叫锁，一个叫latch. 而chain是什么呢？由于大表的全表扫描较慢，为了提高数据访问速度就用索引，指向具体需要访问的数据。如果内存很大，要想获得数据，如果扫描整个内存就很慢，这就需要一个内存的索引，指明数据的具体所在，这个内存中的索引就是chain.这个chain也是内存的一种结构，也存储了数据，也有共享问题，所以也就有了其上的特定的latch.如果是小的内存结构，就不需要索引了，所以我们会看到DataBufferChainLatch。因为DataBuffer实在很大，全扫描肯定是很慢的。 另外，LRU列表估计也很大，因为我们也会看到，LRUChinaLatch。 促销时业务可能会增长10倍以上，仅仅做cache可能是不够的，主要原因是cache只能结果临时的问题，一旦缓冲满了，就不再起作用了。 我试着回答一下：要分析一下业务的在数据库方面的特点，以及当前数据库的性能短板。例如，这些促销中的负载，可能这些负载是高CPU的，也可能是有很多其他争用，也可能会造成一些RAC相关的问题，也可能是IO问题。 我们可以明确在促销时会进行哪些业务，然后，用Loadrunner模拟这些负载，查看数据库的具体瓶颈在何处，在促销前，就可以进行针对性的优化。 当然，促销造成的瓶颈也不一定就在数据库，这样根据性能测试的结果而定了。

Q:在计算型系统中（比如：医药企业的奖金计算），常常连接3个以上的Table或者View，Leftjoin和Group by语句大量涌现。

对这样的系统，在前期DB设计和后期SQL的写法，有哪些建议？谢谢！

A:这位朋友，我是做测试的，以下回答可能不全面也可能比较浅陋，你参考吧，有不对的地方咱们一起讨论： 对于计算： 数据类型可以尽量选用PLS_INTEGER或者SIMPLE_INTEGER,二者都比NUMBER类型计算快。二者取值范围相同，都是-2147483648~2147483648，但是SIMPLE_INTEGER不能为Null，溢出时不会报异常，但是本地编译后速度最快。 对于浮点操作： BINNARY_FLOAT和BINARY_DOUBLE都比NUMBER快。另外，尽量避免类型转换。 对于PL/SQL: 1.如果计算很多，可以考虑JAVA的存储过程，这样计算较快。但是如果是大量的数据库操作JAVA存储过程就不适用了。 2.函数缓存。对于昂贵的但结果集确定的函数或者对静态表执行的数据访问的函数，可以考虑做函数缓存。 3.DML触发器性能。触发器尽量带When条件触发，不要用ForEachRow触发器，尽量使用After触发器，而不是Before触发器。 4.写PL/SQL时注意： 1）将For循环转换成数组处理（10g及之后的版本会自动转换） 2）对DML尽量采用数组处理 3）尽量减少循环次数 4）把较少发生的情况放在AND前面，把较多发生的情况放在OR前面 5）在IF或Case的表达式中，把最可能发生的情况放在最前面 6）用迭代代替递归 7）NoCopy技术。减少产生的副本在子程序和主程序间来回传递。 对于连接： 以上的帖子提到过连接的一些内容。另外，如果连接比较常用可以考虑在表中增加冗余列，避免连接。但这么做要考虑冗余列的一致性问题。 另外，簇表也可以解决一些连接问题。但是也有其它问题：针对簇中的单表扫描会变慢，因为会扫描簇中的所有表，带来额外IO。 再补充一点： 优化PL/SQL前要先优化单个SQL DBMS_PROFILER可以帮助你定位最消耗资源的PL/SQL代码行 11g的DBMS_HPROF可以帮助你定位昂贵的子程序或者被调用的程序。这个我没有用过，你可以参考。

Q:在SQL优化过程中，经常要决定是使用索引还是全表扫描来访问表，请问各位是如何考虑这个问题呢？

很多人都会说，这个没有绝对的，不是使用索引就一定比全表扫描高效。得根据实际情况分析。在调试的时候，大家都会对这2种方式进行比较。谁高效就用谁。

所以，这个问题的本质应该是分析SQL优化时，使用索引或全表扫描时该考虑什么问题，使其更高效。

对索引而言

1. 索引列的选择：走A列索引好还是B列索引好？

2. 索引方式的选择：唯一扫、范围扫、跳跃扫、全扫、快速全扫等

3. 聚簇因子

4. 索引空块的处理

5. 统计信息

对全表扫而言

1. 并行度的使用

2. 多块读的数量

3. 是否进行缓存

4. 统计信息

等等

大师，你怎么看这个问题？

A:不敢自称大师，差得太远。这个问题是我们常常面对的一个问题，我想如果我们自己能有一个处理的思路，那么对我们日常工作来说应该是有好处的。我仅就我的粗浅理解说一下： 这位朋友已经说了一些索引和全表扫描本身的内容，说的挺具体，挺好。 我说一下我处理的原则，不是很具体，大家一起讨论。 Oracle的优化器，尤其是在11g以后，有了SQL基线，还是比较聪明的。我基本上首先是信任它做出的选择的。但是，这有个重要的提前，就是以下几个方面的信息要正确： 1.优化器目标 2.统计信息：对象、系统、扩展统计信息 3.参数设置 4.提示 5.绑定变量窥探和自适应游标 6.SQLoutline\SQLProfile\SQLBaseline 如果以上信息都是正确的，我是相信优化器的选择的。 如果它选择了索引或者全表扫描，还是不能满足性能要求，我会根据它的选择再去优化索引或者全表扫描本身进一步提高性能。 如果，优化器做出的执行计划性能差的离谱，我会首先确认以上各项信息的正确性。

结语：

随着互联网金融的起事，目前的用户已经不再但限于企业内部，而是真正的客户。

因此从用户的体验角度去看整个系统会更全面，单方面优化某一局部不一定能优化客户的体验。

我们要看的是事物从请求端（web，IOS，andriod 等）发起经过上面的各层最后返回到最终用户的页面。

因此这一串流程中全部问题的综合分析才是解决问题的大思路。目前Gartner在其新的报告中都有提及。

本人关注用户体验2年多吧，国内目前和国外差距还蛮大，不过很多保险公司都开始启动这一块的项目。

初来乍到，欢迎大神指导。

大家都基于自己的工作经验，专注于某一区域的性能优化，为精兵。

覆盖全系统的性能优化，为将。

想要当帅，就要更上一层，看业务系统如何为业务本身带来价值与效益。

本次【专家答疑】内容由ITPUB技术论坛《专家答疑：性能优化怎么破？说出DBA心中的痛！》活动整理而成。