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

http://www.javaeye.com/topic/366991#

作为一个开发人员，开始认真系统学习 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=‘avalue’;

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
]...])*/

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

ORDERED

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

SELECT
/*+ORDERED*/A.COL1,B.COL2,C.COL3
FROM
TABLE1A,TABLE2B,TABLE3C
WHERE
A.COL1=B.COL1
AND
B.COL1=C.COL1;

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'
;

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

FULL

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

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

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

LEADING

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

USE_NL

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

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

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;

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;

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’;

Select * from table_a where indexed_column = ‘5’; 

该 SQL实 际会被改写成

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

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

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时，尽量保持简单明了。在进行任何调 优动作之前，请先重新收集最新的统计信息。