在过去的十年中 Oracle 已经成为世界上最专业的数据库之一对于 IT 专家来说就是要确保利用 Oracle 的强大特性来提高他们公司的生产力最有效的方法之一是通过 Oracle 调优它有大量的调整参数和技术来改进你的 Oracle 数据库的性能
Oracle 调优是一个复杂的主题关于调优可以写整整一本书不过为了改善 Oracle 数据库的性能有一些基本的概念是每个 Oracle DBA 都应该遵从的
在这篇简介中我们将简要地介绍以下的 Oracle 主题—— 外部调整我们应该记住 Oracle 并不是单独运行的因此我们将查看一下通过调整 Oracle 服务器以得到高的性能
——Row resequencing 以减少磁盘 I/O 我们应该懂得 Oracle 调优最重要的目标是减少 I/O ——Oracle SQL 调整 Oracle SQL 调整是 Oracle 调整中最重要的领域之一只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能这是一点都不奇怪的
—— 调整 Oracle 排序排序对于 Oracle 性能也是有很大影响的
—— 调整 Oracle 的竞争表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响
我们首先从调整 Oracle 外部的环境开始如果内存和 CPU 的资源不足的话任何的 Oracle 调整都是没有帮助的
外部的性能问题
Oracle 并不是单独运行的 Oracle 数据库的性能和外部的环境有很大的关系这些外部的条件包括有 CPU——CPU 资源的不足令查询变慢当查询超过了 Oracle 服务器的 CPU 性能时你的数据库性能就受到 CPU 的限制内存 —— 可用于 Oralce 的内存数量也会影响 SQL 的性能特别是在数据缓沖和内存排序方面网络 —— 大量的 Net 通信令 SQL 的性能变慢
许多新手都错误的认为应该首先调整 Oracle 数据库而不是先确认外部资源是否足够实际上如果外部环境出现瓶颈再多的 Oracle 调整都是没有帮助的
在检查 Oracle 的外部环境时有两个方面是需要注意的 当运行队列的数目超过服务器的 CPU 数量时服务器的性能就会受到 CPU 的限制补救的方法是为服务器增加额外的 CPU 或者关闭需要很多处理资源的组件例如 Oracle Parallel Query 内存分页当内存分页时内存容量已经不足而内存页是与磁盘上的交换区进行交互的补救的方法是增加更多的内存减少 Oracle SGA 的大小或者关闭 Oracle 的多线程服务器
可以使用各种标准的服务器工具来得到服务器的统计数据例如 vmstatglancetop 和 sar DBA 的目标是确保数据库服务器拥有足够的 CPU 和内存资源来处理 Oracle 的请求
以下让我们来看一下 Oracle 的 rowresequencing 是如何能够极大地减少磁盘 I/O 的
Rowresequencing （行的重新排序）
就象我们上面提到的有经验的 Oracle DBA 都知道 I/O 是响应时间的最大组成部分其中磁盘 I/O 特别厉害因为当 Oracle 由磁盘上的一个数据文件得到一个数据块时读的进程就必须等待物理 I/O 操作完成磁盘操作要比数据缓沖慢 倍因此如果可以令 I/O 最小化或者减少由于磁盘上的文件竞争而带来的瓶颈就可以大大地改善 Oracle 数据库的性能
如果系统响应很慢通过减少磁盘 I/O 就可以有一个很快的改善如果在一个事务中通过按一定的范围搜索 primarykey 索引来访问表那么重新以 CTAS 的方法组织表将是你减少 I/O 的首要策略通过在物理上将行排序为和 primarykey 索引一样的顺序就可以加快获得数据的速度
就象磁盘的负载平衡一样行的重新排序也是很简单的而且也很快通过与其它的 DBA 管理技巧一起使用就可以在高 I/O 的系统中大大地减少响应的时间
在高容量的在线事务处理环境中（ online transaction processing OLTP ）数据是由一个 primary 索引得到的重新排序表格的行就可以令连续块的顺序和它们的 primary 索引一样这样就可以在索引驱动的表格查询中减少物理 I/O 并且改善响应时间这个技巧仅在应用选择多行的时候有用或者在使用索引范围搜索和应用发出多个查询来得到连续的 key 时有效对于随机的唯一 primarykey （主键）的访问将不会由行重新排序中得到好处
让我们看一下它是如何工作的考虑以下的一个 SQL 的查询它使用一个索引来得到 行selectsalaryfromemployeewherelast_name like B%这个查询将会使用 last_name_index 搜索其中的每一行来得到目标行这个查询将会至少使用 次物理磁盘的读取因为 employee 的行存放在不同的数据块中
不过如果表中的行已经重新排序为和 last_name_index 的一样同样的查询又会怎样处理呢？我们可以看到这个查询只需要三次的磁盘 I/O 就读完全部 个员工的资料（一次用作索引的读取两次用作数据块的读取）减少了 次的块读取
重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何以及你需要由序列中访问多少行至于一个表中的行与索引的排序键的匹配程度可以查看数据字典中的 dba_indexes 和 dba_tables 视图得到
在 dba_indexes 的视图中查看 clustering_factor 列如果 clustering_factor 的值和表中的块数目大致一样那么你的表和索引的顺序是一样的不过如果 clustering_factor 的值接近表中的行数目那就表明表格中的行和索引的顺序是不一样的
行重新排序的作用是不可以小看的在需要进行大范围的索引搜索的大表中行重新排序可以令查询的性能提高三倍
一旦你已经决定重新排序表中的行你可以使用以下的工具之一来重新组织表格 使用 Oracle 的 Create Table As Select （CTAS） 语法来拷贝表格 Oraclei 自带的表格重新组织工具
以下我们来看以下 SQL 语句的调优
SQL 调优Oracle 的 SQL 调优是一个复杂的主题甚至是需要整本书来介绍 Oracle SQL 调优的细微差别不过有一些基本的规则是每个 Oracle DBA 都需要跟从的这些规则可以改善他们系统的性能 SQL 调优的目标是简单的 消除不必要的大表全表搜索不必要的全表搜索导致大量不必要的 I/O 从而拖慢整个数据库的性能调优专家首先会根据查询返回的行数目来评价 SQL 在一个有序的表中如果查询返回少于 % 的行或者在一个无序的表中返回少于 % 的行那么这个查询都可以调整为使用一个索引来代替全表搜索对于不必要的全表搜索来说最常见的调优方法是增加索引可以在表中加入标准的 B 树索引也可以加入 bitmap 和基于函数的索引要决定是否消除一个全表搜索你可以仔细检查索引搜索的 I/O 开销和全表搜索的开销它们的开销和数据块的读取和可能的并行执行有关并将两者作对比在一些情况下一些不必要的全表搜索的消除可以通过强制使用一个 index 来达到只需要在 SQL 语句中加入一个索引的提示就可以了 在全表搜索是一个最快的访问方法时将小表的全表搜索放到缓存中调优专家应该确保有一个专门的数据缓沖用作行缓沖在 Oracle 中你可以使用 alter table xxx cache 语句在 Oracle 或以上小表可以被强制为放到 KEEP 池中缓沖 确保最优的索引使用 对于改善查询的速度这是特别重要的有时 Oracle 可以选择多个索引来进行查询调优专家必须检查每个索引并且确保 Oracle 使用正确的索引它还包括 bitmap 和基于函数的索引的使用 确保最优的 JOIN 操作有些查询使用 NESTED LOOP join 快一些有些则是 HASH join 快一些另外一些则是 sortmerge join 更快
这些规则看来简单不过它们占 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 时由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT当与 Oracle 建立起一个 session 时在内存中就会为该 session 分配一个私有的排序区域如果该连接是一个专用的连接 （dedicated connection） 那么就会根据 initora 中 sort_area_size 参数的大小在内存中分配一个 Program Global Area （PGA） 如果连接是通过多线程服务器建立的那么排序的空间就在 large_pool 中分配不幸的是对于所有的 session 用做排序的内存量都必须是一样的我们不能为需要更大排序的操作分配额外的排序区域因此设计者必须作出一个平衡在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（ disk sorts ）的同时对于那些并不需要进行很大排序的任务就会出现一些浪费当然当排序的空间需求超出了 sort_area_size 的大小时这时将会在 TEMP 表空间中分页进行磁盘排序磁盘排序要比内存排序大概慢 倍
上面我们已经提到私有排序区域的大小是有 initora 中的 sort_area_size 参数决定的每个排序所占用的大小由 initora 中的 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 的性能确实有影响
由 DBA 的基本知识知道 pctfree 参数是控制 freelist unlinks 的（即将块由 freelists 中移除）设置 pctfree= 意味着每个块都保留 % 的空间用作行扩展 pctused 参数是控制 freelist relinks 的设置 pctused= 意味着只有在块的使用低于 % 时才会回到表格的 freelists 中
许多新手对于一个块重新回到 freelists 后的处理都有些误解其实一旦由于一个删除的操作而令块被重新加入到 freelist 中它将会一直保留在 freelist 中即使空间的使用超过了 % 只有在到达 pctfree 时才会将数据块由 freelist 中移走
表格和索引存储参数设置的要求总结以下的一些规则是用来设置 freelists freelist groups pctfree 和 pctused 存储参数的你也知道 pctused 和 pctfree 的值是可以很容易地通过 alter table 命令修改的一个好的 DBA 应该知道如何设置这些参数的最佳值
有效地使用空间和高性能之间是有矛盾的而表格的存储参数就是控制这个方面的矛盾 对于需要有效地重新使用空间可以设置一个高的 pctused 值不过副作用是需要额外的 I/O 一个高的 pctused 值意味着相对满的块都会放到 freelist 中因此这些块在再次满之前只可以接受几行记录从而导致更多的 I/O 追求高性能的话可以将 pctused 设置为一个低的值这意味着 Oracle 不会将数据块放到 freelists 中直到它几乎是空的那么块将可以在满之前接收更多的行因此可以减少插入操作的 I/O 要记住 Oracle 扩展新块的性能要比重新使用现有的块高对于 Oracle 来说扩展一个表比管理 freelists 消耗更少的资源
让我们来回顾一下设置对象存储参数的一些常见规则经常将 pctused 设置为可以接收一条新行对于不能接受一行的 free blocks 对于我们来说是没有用的如果这样做将会令 Oracle 的性能变慢因为 Oracle 将在扩展表来得到一个空的块之前企图读取 个 dead 的 free block 表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少在很多情况下 RAW 和 LONG RAW 列都很巨大以至超过了 Oracle 的最大块的大小这时 chained rows 是不可以避免的如果一个表有同时插入的 SQL 语句那么它需要有同时删除的语句运行单一个一个清除的工作将会把全部的空闲块放到一个 freelist 中而没有其它包含有任何空闲块的 freelists 出现 freelist 参数应该设置为表格同时更新的最大值例如如果在任何时候某个表最多有 个用户执行插入的操作那么该表的参数应该设置为 freelists= 应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的对于这类 Oracle freelist groups 应该设置为访问该表格的 Oracle Parallel Server 实例的数目
