一个programmer关于Oracle SQL的学习小结与思考

    作为一个开发人员，开始认真系统学习Oracle SQL也只有月余，之前的SQL知识仅仅是项目里面用到什么就去看什么，简单说就是Read The Fxxking Guide。在系统学习Oracle SQL之前，俺的想法就是“按照需求完成功能，剩下的交给DBA来处理”。这个想法从整体来看显而易见是错误的，但若分割成两个独立的分句“按照需求完成功能”与“剩下的交给DBA来处理”却又是正确的。接下来详细阐述我的观点。

 

你的问题是什么？

    这个问题应该无时无刻地回响在我们的脑海中。我们必须清晰地了解我们面对的问题是什么？否则就会演变成一句老话“进去的是垃圾，出来的还是垃圾！”。而SQL调优的最牛的境界就是不需要继续运行那个SQL（因为无论如何调优，那条SQL语句仍然会占用资源，而不运行该SQL，则它的cost就是零，没有比这个更牛X的了），要做到这一步的前提，显然是充分理解所需处理的问题。不知道问题的来龙去脉，不知道写该SQL的目的，或者是不清晰明白“我的问题是什么”，写出来的SQL的也是蹩脚SQL，也是被无数DBA深恶的SQL，也很可能成为应用的性能murder。也是基于此原因，开发人员也应该熟练掌握基本SQL语法，基本的调优原则，不要写出有明显性能问题的SQL语句。并且这是开发人员应该做的，且必须做到的，因为我们才是切实明白或者说更应该理解我们所需面对的问题的人,我们才是对后续SQL调优的最佳人选，而且DBA很难通过SQL语句去了解开发人员写该SQL的目的，这一点就剥夺了DBA从本质上提升性能的机会，留给他们的只能是无尽的猜测，hint，运气好的话，还能找到那个被诅咒的开发人员问问写那条SQL的原因何在。当然，我由衷的希望这个DBA没有带刀 ：）

    在明确了“问题是什么？”后，进一步就是按照正确的理解去实现SQL。开发人员应该“按照需求完成功能”，不能完成功能的SQL都是垃圾，不论其性能多么优异，写得多么漂亮。就如，一条SQL本该返回100条数据，而实际却只返回了99条，那么哪怕是换一种写法能返回100条数据，但是性能比目前的返回99条数据的SQL要差很多，那么我们也应该选择能完成功能的SQL，99不是100，用户要的是100，而不是99。

 

别总拿性能来说事，它不一定就是问题。

    我们的周边，总会有人以性能为借口，说你的这种写法会有性能问题，应改成XXXX写法，或者说我们因该对每一条SQL都使其性能最优。依我来看，这是一种危险的想法，首先犹如宇宙中某一个反夸克一样难以琢磨的CBO生成的执行计划在不同机器上的差异来说，在某台机器上执行得不错的执行计划，在另一台机器也许就是灾难，开发人员在开发环境中做的“提前优化”，也许在生产环境中就是不管用，乃至产生负作用。更有甚者忽视表的数据量规模，一味追求“性能”，他们的目的是显示自己对Oracle多“熟悉”，自己多“牛逼”，殊不知面对的数据量是千和万级别时，乃至十万级别，在Oracle面前这都是小菜一碟。一条SQL执行一次需0.1 ms，而另一种写法需0.2 ms一次，前者的性能是后者的1倍，但是在面对至少100ms的网络传输延迟面前，两种SQL的写法都无关紧要了，1/1000级别的性能提升毫无意义，倘若当初为了将0.2ms提升至0.1ms花费了大量精力，这也许对开发人员个人而言是有益的，但对整个项目来说这就是浪费。

    总拿性能说事多多少少和提前优化相关，这也是一个老生常谈的问题了。

    话说当年在贝尔实验室. 一群工程师围着一个巨慢无比的小型机发呆. 为啥呢, 因为他们觉得这个机器太慢了. 什么超频, 液氮等技术都用了, 这个小型机还是比不上实验室新买的一台桌上计算机. 这些家伙很不爽, 于是准备去优化这个机器上的操作系统. 他们也不管三七二十一, 就去看究竟那个进程占用CPU时间最长, 然后就集中优化这个进程. 他们希望这样把每个程序都优化到特别高效, 机器就相对快了. 于是, 他们终于捕捉到一个平时居然占50% CPU 的进程, 而且这个进程只有大约20K的代码. 他们高兴死了, 立即挽起袖子敲键盘, 愣是把一个20K的C语言变成了快5倍的汇编. 这时候他们把此进程放到机器上这么一实验, 发现居然整体效率没变化. 百思不得其解的情况下他们去请教其他牛人. 那个牛人就说了一句话: 你们优化的进程, 叫做 System Idle.

    在此引用科学怪人高爷爷的话说, 提前优化是万恶之源 (Premature optimization is the root of all evil)

    别曲解高老爷子的话，如果你真的十分明确某一部分倘若处理不好极有可能成为一个性能瓶颈，那么还是可以拿出你所有的本事去搞定它。在SQL这个话题里，除非你处理的是大量数据，否则还是不要轻易提前优化。大数据量时，需谨慎对待，你需要了解各种访问路径以及连接方式在大数据量时能产生的大致影响，如果能明确此时使用hash join肯定会比nested loop好，那么可以加上USE_HASH hint。然后再谨慎地验证自己的想法。在其他情况下，应尽量使SQL简单明了。

 

在该死的CBO面前一切都是神秘莫测的，但是我们因该相信它。

    在Oracle引入CBO后，我们总是能听到某天某个SQL，CBO产生了逆天的执行计划，性能差到了没有终点。此处省去一万字对CBO的血泪批判。但是我要说的是，从概率来说，CBO产生的执行计划，大部分情况下是正确的。

给大家举个例子：

    表tab1，有100万条数据， indexed_column是一个varhcar2(255)的not null列，而有一个索引idx_some_column建立在该列上，该索引既可以是B树索引，也可以是位图索引。Tab1表已经被完全统计过了。

Select * from tab1 where indexed_column = ‘a value’; 

 对上述sql，我们预期会返回14万条记录。我的问题是使用索引来读取这14万条记录快呢？还是走全表扫描快呢？

    我无法给你一个100%的正确的答案，但是在大多数情况下，走索引会比走全表扫描慢。在获取14万条记录之前，大概会产生3×14万的single block I/O，再加上通过rowid获取14万条记录的single block I/O，总共接近56万个single block I/O。而在全表扫描时，只需要用multiblock I/O读取100万条记录，multiblock I/O的次数主要取决于当前表空间的block size大小和每条记录的大小。而我们知道对I/O而言，我们更喜欢大量少次的multiblock I/O,尽量避免少量多次的single block I/O。整体而言，全表扫描会比使用索引快，而CBO的选择也是如此。答案肯定会是这样吗？不！走索引也有比走全表扫描更快的时候，当该索引大部分都缓存在内存中时，访问索引的single block I/O成本就会很低，这样走索引也许就会比全表扫描快。但是，我们能确定缓存里面一定有该索引的缓存数据吗？这次访问时还存在缓存数据，下次访问时就一定存在吗？那么谁能比你更清楚当前缓存中是否包含该索引呢？CBO！所以请不要贸然地加上index hint。或者理所当然地认为使用索引会更快。

    我们都知道CBO会综合考虑多方面的因素，来选择最后的最优执行计划。而随着环境的不同，CBO也会产生不同的执行计划。这也就是在一台机器跑得很好的sql，在另一台机器上就问题多多的原因。盲目地强制CBO生产某个特定的执行计划，就是灾难的开始。所以，我再次抛出我的观点，仅对需处理大数据量的SQL语句谨慎地调优，而其他仅仅会处理少量数据的SQL就由CBO全权负责。

 

Hint hint hint

    谈到SQL调优，就不能不提及hint。Hint是我们改变CBO抉择的有效途径。而最常用的就是改变访问路径的hint和改变连接方式的hint。这两点对查询而言是至关重要地。在《Oracle Database 10g Performance Tuning Tips & Techniques》中列举了Top 9的最常用的hint。

 

INDEX

虽然索引并不总会快于全表扫描,但是很多时候我们希望Oracle使用索引来执行某些SQL,这时候我们可以通过index hints来强制SQL使用index.

Index Hints的格式如下:

/*+ INDEX ( table [index [index]...] ) */ 

 

 

ORDERED

用ORDERED来提示CBO按表的出现顺序来连接表。

SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1; 

 

 

 

 

 

 

PARALLEL

 

FIRST_ROWS

提示CBO生成的执行计划是以最小响应时间来返回记录。在需要快速获取前N行时有用。

SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 

 

 

 

 

 

 

FULL

表明对表选择全局扫描的方法.

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT'; 

 

 

 

 

 

 

LEADING

将指定的表作为连接次序中的首表.

 

USE_NL

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 

 

 

 

 

 

 

APPEND

 

USE_HASH

将指定的表与其他行源通过哈希连接方式连接起来.

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 

 

 

 

 

 

 

除去PARALLEL和APPEND 以外，剩下的就是开发人员用得最多的hint。

 

访问路径与连接方式，使用hint前的必修内容

    要想从本质理解何时使用hint，使用何种hint就需要了解oracle的访问路径。你至少需要知道全表扫描，几种索引扫描和nested loop join，hash join以及merge sort join.。更多更详细的说明，请参考Oracle的官方资料。

 

为什么没有使用索引呢？

    关于索引有无数的话题可以谈，哪怕是关于索引本身就有很多内容。最常用的索引为B 树索引，位图索引以及函数索引。想了解索引的细节和种类可以参阅Oracle的文档。在这里只简单的描述一下几个常见的未能使用索引的原因，更多信息请查找TOM的著作，里面会有较为详细的讲解。在说明之前，我们一定要明白使用索引并不一定能加快SQL的执行速度。

    第一个原因，使用索引会使查询更慢。

关于这个问题，先前已有例子说明，再次只需强调一下“使用索引就一定快”不是神话，而是彻头彻尾的流言。

    第二个原因，显式或隐式对有索引的列使用了某个函数。

Select * from table_a where f(indexed_column) = some_value;

 

此时由于对indexed_column使用了某个函数f()，因此，CBO将不会使用建立在该列的索引。同样，若indexed_column为Number,下面的SQL也不会使用在indexed_column上的索引

Select * from table_a where indexed_column = ‘5’; 

 该SQL实际会被改写成

Select * from table_a where to_char(indexed_column) = ‘5’; 

 这就是由于类型不匹配导致隐式使用函数的例子。

 

其他一些需要注意的地方

    不要轻信流言。

    我曾经听说过distinct比group by的性能更优异。这完全是错误的，我在多台机器上测试过，distinct ‘all columns’ 100W条数据与group by ‘all columns’100W条数据，两者所用的时间基本相等，逻辑读数完全一致。而在小数据量下，还需要比较吗？因此，那种方式更贴合“我们的问题”就用那种，请无视两者的性能差异—它们没有“差异”。

 

    绑定变量的窥视对SQL执行计划的影响。

    毫无疑问，减少硬解析，跟多地使用软解析会对oracle性能带来提升。

Select * from a_table where a_col = :value 

 

如果有这么一句sql，oracle在执行时，会尝试获取绑定变量value的数据类型。假如a_col是一个varchar类型的列，并且有一个索引建立在该列上，那么在第一次运行时该sql时，value的类型若为varchar，那么CBO将有可能会选择索引访问路径，若value的类型为其他类型，由于会发生隐式类型转换，将使CBO不在选择使用索引的执行计划。而窥视的影响则在此之后，当以后该SQL再次执行的时候，CBO仍会选择之前的执行计划，而不论此次执行时value的数据类型是什么。最终，即便如此，我们仍然应该尽量多地使用绑定变量。

 

   EXISTS or IN

    呵呵，这也是一个老话题了，别迷信坊间传言，没有那个一定比另一个好。我就跑出过使用exist或in产生出完全一样的执行计划，并且拥有一样的逻辑读的情况。不过按照《Oracle 10gR2 Performance Tuning Guide》的说法，在subquery中具有高度可选择性的条件时，应该使用in，相反，在parentquery里具有高度可选择性的条件时，应该用exists。

 

为什么说要把剩下的留给DBA？

    很简单，开发人员可以做很多事情，他们最了解功能需求，因此他们具有最多的调优空间，但是我们不能强求每一个开发人员都很了解数据库。对，他们的确应该具备基本的调优能力，但是这不代表他们需要去了解数据库的方方面面。例如，改变表数据对应的buffer cache的类型，在一些情况下的确会实质性地提升性能。开发人员必需了解这个吗？指定Buffer cache的类型需要在crate table或alter table时操作，需要把这个权限开放给开发人员吗？OPTIMIZER_INDEX_CACHING与 OPTIMIZER_INDEX_COST_ADJ这两个oracle参数对CBO影响巨大，这个一般是完全靠oracle自己收集信息以调整，或者是由有经验的DBA预置，再让oracle接手。这个开发人员必需知道吗？share pool的大小，log buffer的大小，乃至SGA，PGA这些都影响着oracle的运行，但这些开发人员必需知道吗？或者说需要他们去动手调整吗？这就更不用提 RMAN,ASSM,ASM,AMMS这些东东。不！开发人员必需知道的是SQL语法，单行函数，聚合函数以及基本的SQL调优手段，这里包括各种访问路径，连接方式以及改变这些访问路径和连接方式的hint，除此之外让DBA来做，因为那些领域是DBA专长，开发人员无法替代他们，或者说无法比他们做得更好。当然，某个/些开发人员对数据库有爱就另当别论了。

 

在最后，总结一下的我想法。谨慎地对需处理大量数据的SQL进行调优，而在仅需处理小数据量的SQL时，尽量保持简单明了。在进行任何调优动作之前，请先重新收集最新的统计信息。

 

评论

1 楼 石建武 2009-06-27  

自己这段时间也在系统的学习oracle，楼主的话给我指明了方向，做为一个开发人员我们应该做好那些事情。明确自己的职责。谢谢！