1.1.2 软件代码的编写对数据库性能的影响

软件代码对数据库的影响，通常指的是应用程序代码中对数据库操作的代码部分对数据库产生的影响。具体来讲就是SQL语句或是PL/SQL包。SQL语句造成的影响，一种是SQL语句本身在逻辑上就是效率低下的，另一种就是SQL语句没有绑定变量。

性能低下的SQL语句，比如使用Hint，不合适的外连接，谓词的隐含转换，优化器的选择等，会对SQL的执行产生非常大的影响，特别是多表关联的情况下，影响更是显著。它主要体现为SQL语句的执行受到了人为的约束，比如数据的访问方式（索引还是全表扫描），以及表关联方式的选择上（Hah Join，Nested Loops）。

1.1.2.1 人为地在SQL代码中加入Hint来约束SQL的执行计划

我曾经遇到的一个例子就是这样，开发人员为了要求每次对一个表做操作的时候都使用索引，于是在代码中强行加入了Hint约束SQL的执行计划，它的样子大概是这样：

    Select   /＊+ index(t1 ind_t1) ＊/ col1,col2 from t1 where col1＞...... and
    col1＜......

我猜测他们在系统上线之前测试的阶段，发现这条SQL选择索引比全表扫描效率高得多，为了保证以后执行计划能够始终选择索引，他们在代码中的SQL里加入了这个Hint。

系统上线后，没有出现过问题，直到有一天用户抱怨查询非常慢，我从数据库里得到了用户端发出的SQL，才知道这个SQL在代码里加入了Hint。问题是，为什么之前操作都没有问题呢？我登录这个数据库，查看了一下这个表的信息，惊奇地发现，这个表每天仍然定时在做分析操作，这是一个奇怪的现象，人为地对表进行定时分析，却不允许数据库来选择执行计划，这显然是不合理的事情。但这种现象在开发人员当中又是比较普遍的，大家了解一些数据库的技术，却无法将这些知识整合起来运用，系统设计及开发阶段，没有DBA参与进来，直到系统进入运行维护阶段，才有DBA来充当救火队员的角色，这在当前中国软件开发中是一个很普遍的现象。

接着说这个案例。这是一个Oracle 10gr2的数据库，CBO（基于成本的优化器）的技术已经比较成熟，所以此时应该选择由Oracle数据库来决定SQL的执行计划。我分别执行了这条原始SQL和去掉了Hint的SQL，并获得了各自的执行计划，执行计划显示出，去掉Hint的SQL选择了全表扫描（Full Table Scan），执行中扫过的数据块远远小于通过索引访问数据的SQL，于是原因找到了。

可是为什么之前没有出现过这个问题？我对比了一下最近的数据和之前的数据，发现近期的数据在创建索引的列上的列值重复率要远远高于从前，因此Oracle在选择索引之后，比以前读取了更多的索引块和数据块，造成了大量的I/O操作。

因此，对于高版本的数据库（10g以上），我们还是应该让数据库自己根据表、索引的统计分析信息来决定SQL的执行计划，因为表中的数据是会变化的，这种人为的强行干预，必然会在某个时候出现问题。

1.1.2.2 不必要的外连接操作

外连接是一个代价非常昂贵的执行过程。如果业务需要，这种操作是必要的，但是有时候会出现人为地在SQL中使用不必要的外连接的情形，这实际上是因为有的开发人员担心遗漏一些数据而刻意使用它，这就非常有可能留下隐患，让数据库选择昂贵的执行计划而不是最优的那一个。

下面的这个例子说明了一个不必要的外连接使用。

我们创建两个简单的表，并插入一些数据，同时给其中的一个表T2的C列上插入一些空值：

    SQL＞ create table t1 as select rownum a,rownum+100 b from dba_users where
    rownum＜10;

    表已创建。

    SQL＞ create table t2 as select decode(mod(rownum,2),0,rownum) c,rownum+1000
    d from dba_users where rownum＜10;

    表已创建。
    SQL＞ select ＊ from t1;

                    A         B
    ---------- ----------
                    1       101
                    2       102
                    3       103
                    4       104
                    5       105
                    6       106
                    7       107
                    8       108
                    9       109

    已选择9行。

    SQL＞ select ＊ from t2;

                    C         D
    ---------- ----------
                          1001
                    2       1002
                          1003
                    4       1004
                          1005
                    6       1006
                          1007
                    8       1008
                          1009
    已选择9行。

通过下面这条语句，通过使用A字段和T2表C字段关联，我们获取了T1表上所有的行，以及T2表上符合条件的行：

    SQL＞ select a,b,c,d from t1,t2 where t1.a=t2.c(+) ;
                    A         B         C         D
    ---------- ---------- ---------- ----------
                    2       102         2       1002
                    4       104         4       1004
                    6       106         6       1006
                    8       108         8       1008
                    1       101
                    3       103
                    5       105
                    7       107
                    9       109

请看下面这条SQL，它是什么意思呢？

    SQL＞ select a,b,c,d from t1,t2 where t1.a=t2.c(+) and t2.d＞1000;
                    A         B         C         D
    ---------- ---------- ---------- ----------
                    2       102         2       1002
                    4       104         4       1004
                    6       106         6       1006
                    8       108         8       1008

这条SQL的意思是告诉数据库，我要得到T1表上所有的行，并且用A列和T2表C做关联，同时要求T2表C列的值大于1000。

让我们再看看另一条结果集完全一样的SQL：

    SQL＞ select a,b,c,d from t1,t2 where t1.a=t2.c and t2.d＞1000;
                    A         B         C         D
    ---------- ---------- ---------- ----------
                    2       102         2       1002
                    4       104         4       1004
                    6       106         6       1006
                    8       108         8       1008

从结果集上来看，这是两条等价的SQL语句，就是说，在这种情况下，外连接其实是没有用的，是人为地在SQL里设定的限制！如果仔细看一下第一条语句，我们不难发现，条件中T2.C＞1000已经明确地指出，在结果集中，T2表在任何一行，C列都应该有值的，也就是在这种情况下，根本就不需要使用外连接，业务逻辑上讲，外连接在这里是多余的。这种情况在开发人员的代码中有时候会遇到，对他们来讲，只要保证结果集正确就好，但对数据库来讲，在执行时可能会引起极大的性能差别。

1.1.2.3 CBO下优化器模式的选择

通常对于一种功能单一的数据库来讲，在实例级设置一个优化器模式就可以了，比如对于OLAP系统，绝大多数时候数据库上运行着的是报表作业，执行基本上是聚合类的SQL操作，比如GROUP BY，这时候，把优化器模式设置成all_rows是恰当的。

而对于一些分页操作比较多的网站类数据库，设置成first_rows会更好一些。

我却遇到了另外的一件事情。我们的数据库上运行着的基本上是一个OLAP系统，所以优化器模式设置为ALL_ROWS，这有利于报表SQL的快速完成。但数据库上还运行着一些用户查询的业务，查询的方式可以说成是分页的。有时候就会出现用户抱怨查询慢的问题，尽管我知道问题所在，却比较难解决，因为这些SQL已经被开发人员写到代码里面了。

针对这种情况，如果能在开发阶段就考虑到这个问题，针对需要分页操作的SQL，开发人员在SQL里通过Hint的方式来将优化模式转换成FIRST_ROWS，这样就可以大大地提高数据的处理速度。

比如这样一个每次取出10条记录的分页查询：

    Select ＊ from
    (SELECT  /＊+ first_rows(10) ＊/  a.＊,rownum rnum from
    (SELECT /＊+ first_rows(10) ＊/  id,name from t1 order by id)  a
    Where rownum＜=10)
    Where rnum＞=1;

可以在每个子查询中重复使用FIRST_ROWS（n）来提高查询效率。

尽管说在SQL中人为地加入Hint操作不是一个好主意，但是有些时候，比如需要兼顾其他的用户操作时，可以考虑做这样的设定。但前期需要做一些测试工作，以确保这样的设定能够带来性能上的提高，同时不会对数据库造成其他方面的影响。这是系统设计阶段应该仔细考虑好的一个问题。

1.1.2.4 没有绑定变量的SQL

对于这个话题，其实很多人存在着一个误区。记得有一次在广州出差，我和一个同样做数据库的同事，有这样一段对话：

同事：“我们的系统有没有绑定变量？”

我：“不知道.....”

同事：“我发现没有绑定。”

他的表情很凝重，仿佛发现了数据库的一个致命隐患一般。

我：“无所谓吧？”

他立即反驳我说：“谁说无所谓，SQL没有绑定变量，数据库每次执行就会发生硬分析（Hard parse，喜欢读Statspack Report的朋友对这个词应该很敏感吧，我的同事就是一个Statspack fans，那时候他正在研究Statspack,觉得如果硬分析太多了，天就要塌下来，仿佛把这些硬分析变为软分析之后，数据库性能会提高成百上千倍一样），这样性能肯定会大受影响，有时候用户反映查询慢，会不会是这个原因导致的？”

我说不是这个原因导致的，我可以保证，因为我们是这样的一个系统：数据库的用户连接数很少，大概不会超过50 个，每个用户每天发出的查询操作不会超过50个，这对于一个运行在内存8个GB，10几个CPU的系统上的数据库来说，硬分析对数据库性能的影响微乎其微，完全可以忽略掉，因为我们是一个OLAP系统。

他想了一下，认同了我的观点。

我想说的绑定变量的误区就和上面这个案例一样，有时候它对性能的影响被夸大化了。我在ITPUB上总看到很多这样的帖子，大家在谈及SQL时必定要求绑定变量，仿佛不这样系统就要出问题了一样。实际上，至少对于OLAP系统（在线分析系统，通常指的是这样的一个系统，数据库存放着海量的数据，连接的用户少，SQL语句基本上都是用户产生报表的大查询）来说，未绑定变量对数据库的影响是很有限的，甚至是完全没有必要的，因为只有少量的用户和少量的SQL操作，数据库不需要花多少资源在SQL分析上面。这个话题我们会在后续的章节中讨论到。

绑定变量的真正用途是在一个OLTP系统中，这个系统通常有这样的特点，用户并发数很大，用户的请求十分密集，并且这些请求的SQL大多数是可以重复使用的，我们试想，当这些成千上万的SQL被数据库一遍又一遍地进行语法分析、语义分析，生成执行计划时，这对数据库的压力该有多大？如果一条SQL执行一遍之后就被缓存到数据库的内存当中（实际上是在共享池里），以后的成百上千的用户请求都使用这个SQL解析后的结果，那效率将有多么大的提高！

所以，我的观点是，当你要考察绑定变量对你的数据库的影响有多大时，先确定你的系统是OLTP系统或是OLAP系统；当然，现在很多数据库同时担负这两种角色，那么你需要分析数据库的性能情况，比如，做一个Statspack Report来帮助你确定变量是否绑定，以及是否已经对系统的性能构成严重的影响。

1.1.2.5 PL/SQL包

如果你的程序里面有PL/SQL包，请考虑使用存储过程来代替它，存储过程是经过成功编译后存放在数据库中的代码，执行起来的效率要比程序代码中PL/SQL包的效率高很多，因为它不再需要做语法和语义的分析（语法的分析指的是数据库对代码进行检查，看它是否存在语法上的错误；而语义分析是查看语句执行的对象是否存在，比如需要操作的表、列等，以及是否有执行这些操作的权限）。