oracle 数据库sql调优

Oracle 数据库性能优化（SQL 调优篇）引言对于数据库系统来说，优化是一件非常重要而且烦琐的工作。

通过优化我们可以大大的提高系统运行效率和存储空间的利用率，达到用有限的资源实现一个高效的应用系统；说它烦琐是因为它涉及的面太宽了，从系统的规划、库表的设计、sql 语句的编写、物理设备和网络设备的性能、内存和存储空间的分配等等都影响着系统的性能。

正因为它的烦琐，才给系统优化工作者们提供了一个施展才华的舞台。

对于sql 是一个基础简单却有是较难写好的查询语言，它的好坏直接影响系统的性能；本篇将从sql 查询的内部原理、oracle 数据库服务器是怎么处理sql 的、oracle 数据库sql 优化原则、oracle sql 怎么优化，对于sql 怎样使用就没有作说明。

Sql 查询内部原理查询在处理过程中分为四个大的阶段：将查询转换为内部格式阶段、将内部格式转换为规范格式阶段、为执行选择低层过程阶段、生成并选择最低的查询计划阶段，如图：阶段1：将查询转换为内部格式阶段这一阶段主要是进行语法分析，将原查询转换为数据库内部格式以便于机器处理，不编译 视图处理 翻译 关系代数 表达式 表达式转换代价估算 优化后的代码 执行符合语法规范的报错返回，为sql 优化过程铺平道路。

阶段2：将内部格式转换为规范格式在这一阶段，数据库优化器将执行一系列“保证能够优化”的优化过程，是不会去考虑实际数据的值和数据库的存取路径；优化器将查询的内部表示转换为等价的规范格式。

比如说将“A=B 替换为B=A 或者是p and q 替换为q and p ”，这样做的目的是消除语句表面上的差异，以便能够找到一种在某些方面比原查询更为高效的表示方法。

比如说能够将表达式(A JOIN B)WHERE retriction on A 转为等价高效的表达式（A WHERE restriction on A ）JOIN B 。

阶段3：为执行选择低层过程在这一阶段，优化器考虑的是索引、物理存取路径、数据值的分布、数据的物理聚集存储等问题。

基本的策略就是将查询表达式看成一系列的“低层操作”，对于每一个可能的低层操作，都有一组可用的低层过程，而每一个低层过程都会有一个相关的代价计算公式（磁盘i/o 代价，cpu 利用率）。

阶段4：生成并选择最低代价的查询计划这一阶段就是构造一组查询计划，选择一个最优，也就是代价最小的查询计划。

因为每个计划都绑定一系列的低层过程，每一个过程对应查询中的一个低层操作。

优化器此时将所有的过程进行计算，选择一个代价最低的执行。

那么oracle 又是怎么来处理sql 的呢？我们来看看：oracle 分析处理sql 的简单过程oracle 在处理SQL 语句上我们简单的概括为的三个阶段：语法分析、执行、 返回指令（如图）Select 过程：用户select 查询语句发送至server ，server 在共享池进行语法分析和生成执行计划，然后由后台专有server 从磁盘读取数据到data buffer ，并且返回给用户，整个过程如图：解析 S t a t e m e n t O K 执行 提取UPDATE操作过程：为了支持读一致性，恢复和回滚，所有修改操作需要回滚段。

修改操作执行：（1）将数据块送到数据缓冲区（2）将回滚块送到数据缓冲区（3）在修改行上设置行锁（4）保存回滚数据到回滚段块（5）将修改写到数据块Update、select在oracle并行处理：如图Insert 和delete操作和update类似，这里不作论述。

我们在对查询的原理、oracle 内部查询机制作了一些简单讨论后，我们现在开始讨论oracle 的sql优化：oracle sql优化原则Oracle 的SQL 调优是一个非常复杂的主题，可以豪不夸张的说需要一本书的内容来介绍Oracle SQL 调优的细微差别。

不过有一些基本的规则是我们每个开发人员都需要跟从的，这些规则可以改善我们系统的性能。

oracle sql优化的原则是：消除不必要的大表全表搜索、最优的索引使用、最优的JOIN 操作。

消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的I/O ，从而拖慢整个数据库的性能。

调优专家首先会根据查询返回的行数目来评价SQL 。

在一个有序的表中，如果查询返回少于40% 的行，或者在一个无序的表中，返回少于7% 的行，那么这个查询都可以调整为使用一个索引来代替全表搜索。

对于不必要的全表搜索来说，最常见的调优方法是增加索引。

可以在表中加入标准的B 树索引，也可以加入bitmap 和基于函数的索引。

要决定是否消除一个全表搜索，你可以仔细检查索引搜索的I/O 开销和全表搜索的开销，它们的开销和数据块的读取和可能的并行执行有关，并将两者作对比。

在一些情况下，一些不必要的全表搜索的消除可以通过强制使用一个index 来达到，只需要在SQL 语句中加入一个索引的提示就可以了。

确保最优的索引使用：对于改善查询的速度，这是特别重要的。

有时Oracle 可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保Oracle 使用正确的索引。

它还包括bitmap 和基于函数的索引的使用。

确保最优的JOIN 操作：有些查询使用NESTED LOOP join 快一些，有些则是HASH join 快一些，另外一些则是sort-merge join 更快。

这些规则看来简单，不过它们占SQL 调优任务的90% ，并且它们也无需完全懂得Oracle SQL 的内部运作。

以下我们来简单概览以下Oracle SQL 的优化。

oracle sql优化我们首先简要查看Oracle 的排序，并且看一看排序操作是如何影响性能的。

调整Oracle 的排序操作排序是SQL 语法中一个小的方面，但很重要，在Oracle 的调整中，它常常被忽略。

当使用create index 、ORDER BY 或者GROUP BY 的语句时，Oracle 数据库将会自动执行排序的操作。

通常，在以下的情况下Oracle 会进行排序的操作：使用Order by 的SQL 语句、使用Group by 的SQL 语句、在创建索引的时候、进行table join 时。

当与Oracle 建立起一个session 时，在内存中就会为该session 分配一个私有的排序区域。

如果该连接是一个专用的连接(dedicated connection) ，那么就会根据init.ora 中sort_area_size 参数的大小在内存中分配一个Program Global Area (PGA) 。

如果连接是通过多线程服务器建立的，那么排序的空间就在large_pool 中分配。

不幸的是，对于所有的session ，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。

因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disk sorts ）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。

当然，当排序的空间需求超出了sort_area_size 的大小时，这时将会在TEMP 表空间中分页进行磁盘排序。

磁盘排序要比内存排序大概慢14,000 倍。

上面我们已经提到，私有排序区域的大小是有init.ora 中的sort_area_size 参数决定的。

每个排序所占用的大小由init.ora 中的sort_area_retained_size 参数决定。

当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在Oracle 实例中的临时表空间中进行。

磁盘排序的开销是很大的，有几个方面的原因。

首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。

Oracle 还必须分配缓冲池块来保持临时表空间中的块。

无论什么时候，内存排序都比磁盘排序好，磁盘排序将会令任务变慢，并且会影响Oracle 实例的当前任务的执行。

还有，过多的磁盘排序将会令free buffer waits 的值变高，从而令其它任务的数据块由缓冲中移走。

接着，让我们看一下Oracle 的竞争，并且看一下表的存储参数的设置是如何影响SQL UPDATE 和INSERT 语句的性能的。

调整Oracle 的竞争Oracle 的其中一个优点是它可以管理每个表空间中的自由空间。

Oracle 负责处理表和索引的空间管理，这样就可以让我们无需懂得Oracle 的表和索引的内部运作。

不过，对于有经验的Oracle 调优专家来说，他需要懂得Oracle 是如何管理表的extent 和空闲的数据块。

对于调整拥有高的insert 或者update 的系统来说，这是非常重要的。

要精通对象的调整，你需要懂得freelists 和freelist 组的行为，它们和pctfree 及pctused 参数的值有关。

这些知识对于企业资源计划（ERP ）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是DML 语句执行慢的原因。

对于初学者来说，最常见的错误是认为默认的Oracle 参数对于所有的对象都是最佳的。

除非磁盘的消耗不是一个问题，否则在设置表的pctfree 和pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到freelists 中。

当这些设置不正确时，那些得到的freelists 也是"dead" 块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。

Freelists 对于有效地重新使用Oracle 表空间中的空间是很重要的，它和pctfree 及pctused 这两个存储参数的设置直接相关。

通过将pctused 设置为一个高的值，这时数据库就会尽快地重新使用块。

不过，高性能和有效地重新使用表的块是对立的。

在调整Oracle 的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。

以下我们来看一下这些freelists 是如何影响Oracle 的性能的。

当有一个请求需要插入一行到表格中时，Oracle 就会到freelist 中寻找一个有足够的空间来容纳一行的块。

你也许知道，freelist 串是放在表格或者索引的第一个块中，这个块也被称为段头（segment header ）。

pctfree 和pctused 参数的唯一目的就是为了控制块如何在freelists 中进出。

虽然freelist link 和unlink 是简单的Oracle 功能，不过设置freelist link (pctused) 和unlink (pctfree) 对Oracle 的性能确实有影响。