Oracle调优(入门及提高篇)

    在过去的十年中， Oracle 已经成为世界上最专业的数据库之一 。关于 IT 专家来说，便是要确保利用 Oracle 的 壮大 特点来 普及他们公司的生产力 。最有效的 步骤之一是通过 Oracle 调优 。它有大量的调整参数和技术来改良你的 Oracle 数据库的性能 。
    Oracle 调优是一个复杂的主题 。关于调优 可以写整整一本书，不过，为了改善 Oracle 数据库的性能，有一些 根本的概念是每个 Oracle DBA 都应该 听从的 。
    在这篇简介中，我们将简要地介绍以下的 Oracle 主题：
    -- 外部调整：我们应该记住 Oracle 并不是 径自运行的 。 因此我们将查看一下通过调整 Oracle 服务器以得到高的性能 。
    --Row re-sequencing 以削减磁盘 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 的性能，特殊是在数据缓冲和内存排序方面 。
    ．网络 -- 大量的 Net8 通讯令 SQL 的性能变慢 。
    许多新手都 舛误的认为应该首先调整 Oracle 数据库，而不是先确认外部资源是不是足够 。实际上，假如外部环境浮现瓶颈，再多的 Oracle 调整都是没有协助的 。
    在 审查 Oracle 的外部环境时，有两个方面是需要 留神的：
    1 、当运行队列的数目超过服务器的 CPU 数量时，服务器的性能就会受到 CPU 的 制约 。补救的 步骤是为服务器添加额外的 CPU 或者关闭需要众多 解决资源的组件，例如 Oracle Parallel Query  。
    2 、内存分页 。当内存分页时，内存容量已经缺乏，而内存页是与磁盘上的 交换区进行交互的 。补救的 步骤是添加更多的内存，削减 Oracle SGA 的大小，或者关闭 Oracle 的多线程服务器 。
    可以 使用各种 标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top 和 sar  。 DBA 的 指标是确保数据库服务器 占有足够的 CPU 和内存资源来 解决 Oracle 的 申请 。
    以下让我们来看一下 Oracle 的 row-resequencing 是如何 可以极大地削减磁盘 I/O 的 。
    Row-resequencing (行的再一次排序)
    就象我们上面提到的，有 教训的 Oracle DBA 都晓得 I/O 是响应 工夫的最大构成 部分 。其中磁盘 I/O 特殊厉害，由于当 Oracle 由磁盘上的一个数据文件得到一个数据块时，读的 历程就必须期待物理 I/O 操作 实现 。磁盘操作要比数据缓冲慢 10,000 倍 。 因此，假如 可以令 I/O 最小化，或者削减由于磁盘上的文件竞争而带来的瓶颈，就 可以大大地改善 Oracle 数据库的性能 。
    假如系统响应很慢，通过削减磁盘 I/O 就 可以有一个很快的改善 。假如在一个事务中通过按 定然的 规模查找 primary-key 索引来 拜访表，那么再一次以 CTAS 的 步骤组织表将是你削减 I/O 的首要策略 。通过在物理上将行排序为和 primary-key 索引一样的顺序，就 可以加速 获得数据的速度 。
    就象磁盘的负载 均衡一样，行的再一次排序也是很 方便的，而且也很快 。通过与其它的 DBA 治理技巧一同 使用，就 可以在高 I/O 的系统中大大地削减响应的 工夫 。
    在高容量的在线事务 解决环境中( online transaction processing ， OLTP )，数据是由一个 primary 索引得到的，再一次排序表格的行就 可以令延续块的顺序和它们的 primary 索引一样，这样就 可以在索引驱动的表格 查问中，削减物理 I/O 而且改善响应 工夫 。这个技巧仅在 利用 取舍多行的时候有用，或者在 使用索引 规模查找和 利用发出多个 查问来得到延续的 key 时有效 。关于随机的唯一 primary-key (主键)的 拜访将不会由行再一次排序中得到 好处 。
    让我们看一下它是如何工作的 。考量以下的一个 SQL 的 查问，它 使用一个索引来得到 100 行：
    selectsalaryfromemployeewherelast_name like 'B%';
    这个 查问将会 使用 last_name_index ，查找其中的每一行来得到 指标行 。这个 查问将会至少 使用 100 次物理磁盘的读取，由于 employee 的行 存放在不同的数据块中 。
    不过，假如表中的行已经再一次排序为和 last_name_index 的一样，同样的 查问又会 怎么 解决呢？我们 可以看到这个 查问 惟独要三次的磁盘 I/O 就读完所有 100 个员工的 材料(一次用作索引的读取，两次用作数据块的读取)，削减了 97 次的块读取 。
    再一次排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中 拜访多少行 。至于一个表中的行与索引的排序键的匹配程度， 可以查看数据字典中的 dba_indexes 和 dba_tables 视图得到 。
    在 dba_indexes 的视图中，查看 clustering_factor 列 。假如 clustering_factor 的值和表中的块数目 大体一样，那么你的表和索引的顺序是一样的 。不过，假如 clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的 。
    行再一次排序的作用是不 可以小看的 。在需要进行大 规模的索引查找的大表中，行再一次排序 可以令 查问的性能 普及三倍 。
    一旦你已经决定再一次排序表中的行，你 可以 使用以下的工具之一来再一次组织表格 。
    . 使用 Oracle 的 Create Table As Select (CTAS) 语法来拷贝表格
    . Oracle9i 自带的表格再一次组织工具
    以下，我们来看以下 SQL 语句的调优 。
    SQL 调优
    Oracle 的 SQL 调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL 调优的 细微差异 。不过有一些 根本的 规定是每个 Oracle DBA 都需要跟从的，这些 规定 可以改善他们系统的性能 。 SQL 调优的 指标是 方便的：
    . 肃清 毋庸要的大表全表查找： 毋庸要的全表查找招致大量 毋庸要的 I/O ，从而拖慢整个数据库的性能 。调优专家首先会依据 查问返回的行数目来评介 SQL  。在一个有序的表中，假如 查问返回少于 40% 的行，或者在一个无序的表中，返回少于 7% 的行，那么这个 查问都 可以调整为 使用一个索引来 接替全表查找 。关于 毋庸要的全表查找来说，最常见的调优 步骤是添加索引 。 可以在表中加入 标准的 B 树索引，也 可以加入 bitmap 和基于函数的索引 。要决定是不是 肃清一个全表查找，你 可以 细心 审查索引查找的 I/O 开销和全表查找的开销，它们的开销和数据块的读取和可能的并行执行有关，并将两者作对照 。在一些状况下，一些 毋庸要的全表查找的 肃清 可以通过强制 使用一个 index 来达到， 惟独要在 SQL 语句中加入一个索引的 揭示就 可以了 。
    . 在全表查找是一个最快的 拜访 步骤时，将小表的全表查找放到缓存中，调优专家应该确保有一个专门的数据缓冲用作行缓冲 。在 Oracle7 中，你 可以 使用 alter table xxx cache 语句，在 Oracle8 或以上，小表 可以被强制为放到 KEEP 池中缓冲 。
    . 确保最优的索引 使用 ：关于改善 查问的速度，这是特殊主要的 。有时 Oracle 可以 取舍多个索引来进行 查问，调优专家必须 审查每个索引而且确保 Oracle 使用正确的索引 。它还包括 bitmap 和基于函数的索引的 使用 。
    . 确保最优的 JOIN 操作：有些 查问 使用 NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则是 sort-merge join 更快 。
    这些 规定看来 方便，不过它们占 SQL 调优 使命的 90% ，而且它们也无需 彻底懂得 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) ，那么就会依据 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 的性能 确切有影响 。
    由 DBA 的 根本 常识晓得， pctfree 参数是操纵 freelist un-links 的(马上块由 freelists 中移除) 。设置 pctfree=10 象征着每个块都保留 10% 的空间用作行 扩大 。 pctused 参数是操纵 freelist re-links 的 。设置 pctused=40 象征着惟独在块的 使用低于 40% 时才会回到表格的 freelists 中 。
    许多新手关于一个块再一次回到 freelists 后的 解决都有些 误会 。其实，一旦由于一个删除的操作而令块被再一次加入到 freelist 中，它将会向来保留在 freelist 中 即便空间的 使用超过了 60% ，惟独在 到达 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 将在 扩大表来得到一个空的块之前， 用意读取 5 个 "dead" 的 free block  。
    ．表格中 chained rows 的浮现 象征着 pctfree 太低或者是 db_block_size 太少 。在众多状况下， RAW 和 LONG RAW 列都很 硕大， 甚至超过了 Oracle 的最大块的大小，这时 chained rows 是不 可以幸免的 。
    ．假如一个表有同时插入的 SQL 语句，那么它需要有同时删除的语句 。运行单一个一个 革除的工作将会把所有的空暇块放到一个 freelist 中，而没有其它包括有任何空暇块的 freelists 浮现 。
    ． freelist 参数应该设置为表格同时更新的最大值 。例如，假如在任何时候，某个表最多有 20 个消费者执行插入的操作，那么该表的参数应该设置为 freelists=20  。
    应记住的是 freelist groups 参数的值只不过关于 Oracle Parallel Server 和 Real Application Clusters 才是有用的 。关于这类 Oracle ， freelist groups 应该设置为 拜访该表格的 Oracle Parallel Server 实例的数目 。