让Oracle跑得更快Oracle 10g性能分析与优化思路..doc

引起数据库性能问题的因素 1.1 软件设计对数据库的影响 1.1.1 软件架构设计对数据库性能的影响 软件系统的架构对数据库的影响是非常直接的。例如一套系统，如果并发数非常大，比如是超过3000个并发，通常这种情况下，我们会考虑采用一套软件来搭建一个中间层，这就是通常讲到的3层或是多层结构。使用这一套软件的目的是用来构建一个缓冲池，在数据库之前对大量的并发进行处理，以便于每次只有少数的用户连接到数据库中，其他的用户在缓冲池的队列中等待。当然，这只是一个动态的过程，程序会尽可能快地去响应所有用户的请求，这种提前对大量并发用户进行处理的方式，会比让这3000个用户直接连接到数据库中效果要好得多，同时数据库也可以使用更多的资源来处理用户的操作请求而不是去开3000个进程来处理每个用户的请求，这个开销是非常大的。所以对于大量并发的系统来讲，在数据库之前建一个缓冲用户请求的中间件服务，显得至关重要。同时，很多这种中间件软件还提供了负载均衡的功能。 当然，Oracle数据库自身也提供了一种MTS的技术，作用和这种中间件服务是一样的，但目前看来，采用商业中间件软件或是开发商自己开发一套中间件服务的做法更多一些（参见图1-1）。 图1-1 多层架构体系 1.1.2 软件代码的编写对数据库性能的影响 软件代码对数据库的影响，通常指的是应用程序代码中对数据库操作的代码部分对数据库产生的影响。具体来讲就是SQL语句或是PL/SQL包。SQL语句造成的影响，一种是SQL语句本身在逻辑上就是效率低下的，另一种就是SQL语句没有绑定变量。 性能低下的SQL语句，比如使用Hint，不合适的外连接，谓词的隐含转换，优化器的选择等，会对SQL的执行产生非常大的影响，特别是多表关联的情况下，影响更是显著。它主要体现为SQL语句的执行受到了人为的约束，比如数据的访问方式（索引还是全表扫描），以及表关联方式的选择上（Hah Join，Nested Loops）。 ? 人为地在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的执行计划，因为表中的数据是会变化的，这种人为的强行干预，必然会在某个时候出现问题。 不必要的外连接操作 外连接是一个代价非常昂贵的执行过程。如果业务需要，这种操作是必要的，但是有时候会出现人为地在SQL中使用不必要的外连接的情形，这实际上是因为有的开发人员担心遗漏一些数据而刻意使用它，这就非常有可能留下隐患，让数据库选择昂贵的执行计划而不是最优的那一个。 下面的这个例子说明了一个不必要的外连接使用。 我们创建两个简单的表，并插入一些数据，同时给其中的一个表T