Oracle9i 数据仓库执行性能增强

　　Oracle9i 数据仓库执行性能增强
　　简介
　　Oracle9i 显著改进了基本数据仓库(DWH) 功能的执行性能。由于无需更改任何模式或应用程序代码来利用绝大多数性能上的改进成果，因此几乎所有的客户都可以仅通过升级Oracle 安装而从中获益。本白皮书通过在测试数据仓库中执行典型的数据仓库任务和比较Oracle8i 和Oracle9i 的结果，说明了Oracle9i 对于执行性能的增强。
　　所有任务都是在受到严密控制的单个系统中进行的，确保了对Oracle8i 和Oracle9i 进行公正的比较。从加载数据、在整个转换过程中更改数据、创建实体化视图并对其刷新到最后访问数据，我们发现Oracle9i 显著改善了执行性能和系统利用率。
　　资源需求和相关性
　　本测试在相同硬件系统中，使用相同容量的内存、I/O 子系统和相同数目的CPU 对Oracle8i 和Oracle9i 进行了比较。每个测试都受到监视，以此证明结果的正确，且没有受到在同一时刻运行的其他工作负载的不利影响。所有时间信息都用mi:ss 格式来表示。
　　硬件
　　系统配置：Sun Microsystems sun4u 8 槽Sun Enterprise 4000/5000
　　　12 x 400 MHZ CPU
　　　10 GB 内存
　　　128 x 磁盘，原始设备，64KB Stripe Unit Size，最大吞吐量90 MB/秒
　　数据库
　　建立两个带有等价初始化参数(init.ora) 的基于TPC-H 模式的30 GB 数据库，启用新特性所需的参数除外。
　　　Oracle9i (9iopt)，整个系统全局范围662995316 字节
　　　Oracle8i (8iopt)，整个系统全局范围616460448 字节
　　基本执行性能增强
　　本白皮书说明了通过Oracle9i 带来的执行性能增强并非专门与那些需要明确激活的新特性有关。许多执行性能增强不需要对应用程序代码或数据库方案进行任何改动。因此，许多运行于现有的Oracle8i DWH 环境的语句在移植到Oracle9i 后可自动重新使用。我们将贯穿数据仓库进程，从加载、转换和构建阶段开始，然后将数据汇集到实体化视图中，刷新这些视图，使用典型查询语句来访问数据。最后，我们将查看在Oracle8i 和Oracle9i 中完成整个进程所需的时间，展示每种增强功能为执行性能带来的改进。
　　合并/更新插入
　　合并/更新插入增强
　　在一个数据仓库环境中，表（典型情况为事实表）需要使用来自在线系统的新数据进行周期性刷新。这些新数据可能包括数据仓库表中现有行的改变和/或需要插入的新行。如果新数据中的某一行对应于表中已经存在的某一项，则执行一次UPDATE；
　　如果此行的主键没有存在于表中，就执行一次INSERT。在Oracle9i 之前，无论是插入还是更新数据，这些操作都是针对每一行以一个DML (INSERT/UPDATE) 序列或者一个PL/SQL 循环判决来表达。但是两种方法都在执行性能和可用性上存在不足。
　　Oracle9i 通过MERGE 语句这个新语法对SQL 进行了扩展，也就是说，将条件型INSERT 和UPDATE 序列包含在一条单一的基本语句中，从而克服了这些不足，使得数据仓库应用程序的实现更加简单和直观。
　　‘IF ROW EXISTS -THEN-UPDATE ELSE-INSERT’ 条件序列也称为UPSERT。
　　合并/更新插入结果
　　在Oracle9i 中，对于更新插入进程使用新的基本MERGE 语句就能够改善执行性能，而不是通过相同语义使用一个条件INSERT 和UPDATE 语句序列，这主要取决于含有要被合并的新行的源数据大小；这是在与目的表的大小相比较的情况下，在该表中新行会被合并。与只改动目的表中的几行相比，改动多行会从更新插入中受益更多。如果更新插入通过一个MERGE 的语句来执行，源数据只需被扫描一次。然而，通过一个INSERT/UPDATE 序列，源数据表则需被扫描两次。
　　假如需要额外程序的检查或转换等进一步的约束，而这些约束又无法以标准的SQL来表达，这时从执行的角度来讲通过PL/SQL 进行一次合并至少是最不可取的选择。PL/SQL 方法明显比MERGE 或UPSERT/INSERT 的方法慢许多，而后两者都采用了面向集合的处理逻辑。
　　如果ETL （提取/转换/加载）进程要求在插入之前进行一次程序的转换，以流模式使用全新的可并行的PL/SQL 表函数，并且将结果传送给后继的合并操作，会显著地改善执行性能。
　　
　　在测试中，我们分析了一个三百万新客户的记录更新插入到一个包含四百五十万行的客户表中。运行在Oracle9i 中的全新的MERGE 语句与运行在Oracle8i 中的条件式UPDATE 和INSERT 语句序列相比较。在两个测试里，分别有一百五十万个记录被插入和更新。在这两个方案中，都采用了Oracle 的并行DML 功能来执行并行度为16的语句。执行性能的改善是由于源表的最小化扫描，除此之外，对于通过并行DML插入的行，内部优化的索引维护操作也对执行性能的改善起到了一定的作用。与Oracle8i 中同样的INSERT 语句相比，在Oracle9i 中通过‘普通的’并行化INSERT，同样的内部优化也能提高执行性能。
　　执行性能测试Oracle8i Oracle9i
　　差异百分比改进
　　更新插入更新/插入03:28 合并02:24 01:04 30.7%
　　　
　　图1：更新插入测试结果
　　外部表
　　外部表增强
　　在Oracle9i 中，外部表新特性能以‘视觉表’的形式利用外部数据。通过该界面，外部表可被并行查询并且直接连接到内部的常规表，无需事先将外部数据加载到数据库中。
　　使用外部表能使ETL 过程流水线化，从而转换阶段不会因加载阶段而停滞不前。转换进程可与加载进程合并，却不会中断数据的连续性。不再需要为对比或转换而在存储区域存储数据。
　　外部表与常规表之间的重大区别在于外部编排表是只读的。任何DML 操作都不可行，而且无法对它们创建任何索引。在Oracle9i 中，外部表补充了现有SQL Loader 的功能。它们在特定的环境下很有用，一种情况是完整的外部资源必须连接到现有的数据库对象上并以复杂的方式转换；一种情况是外部数据量大且只使用过一次。
　　另一方面，在需要存储表的额外索引时，SQL*Loader 对于加载数据来说仍不失为一个较好的选择。对于数据在独立综合的转换中使用的操作或数据在进一步处理中只部分采用的操作来说，情况就是如此。
　　外部表结果
　　对外部表所做测试是对更新插入测试的一个补充。
　　在Oracle8i 中，一个仓库模式需要三个步骤加载新记录：
　　1. 将外部文件的数据加载到数据库的存储表中（在本例中为CUSTOMER_MERGE）
　　2. 在升级表中创建独特的索引（更新所需）
　　3. 通过UPDATE/INSERT 将数据合并到目的表中(CUSTOMER)
　　在Oracle9i 中，整个过程可缩短为两步：
　　1. 在数据库中创建元数据（CREATE TABLE …ORGANIZATION external）
　　2. 直接运用外部表（CUSTOMER_MERGE）作为Oracle9i MERGE 语句的来源。
　　上面所提的Oracle9i 的第一步很简单，因为SQL Loader 在基于现有的Oracle8i Loader控制文件上生成了完整的CREATE TABLE 语法。Oracle9i 方法的运用使Oracle8i 中两个必要的维护任务可以省略：数据加载和索引创建。与UPDATE 语句不同，MERGE语句对源表的索引不作特别的要求。
　　在本例中，Oracle8i 加载和索引的步骤所需时间如下：
　　1. 加载（三百万行，16 倍直接并行处理） 19 秒
　　2. 索引存储表（16 倍并行处理） 14秒
　　因此使用Oracle9i 外部表将节省33 秒。该测试同时证明，使用将外部表而不是内部表作为MERGE 的一种直接源仅仅会让执行性能略有下降：外部表需2:27 完成MERGE，而内部表需2:24 完成。
　　　
　　图2：外部表测试结果(*)：公平起见，结果取自于Oracle9i-MERGE
　　Group By
　　可变长度聚合
　　在DWH 的应用中，Group By 是个很关键的基本操作。Group By 在Oracle9i 中已经提升到运用可变长度聚合的高度。
　　在Group By、Cube 和Rollup 处理过程中，Oracle8i 的执行引擎把Oracle 数字作为聚合的累加器。分类引擎中填写了许多记录，这些记录由一连串的Group By 键和一系列聚合工作区域组成。当Group By 操作通过同样的分组键使用行时，聚合就会发生。Oracle8i 的聚合中SUM 占用了23 个字节、AVG 占用了31 个字节、VARIANCE 占用了54 个字节来累加所聚合的数值。分类记录的数量可以不断地增长，能反过来促使内存分类区域的快速消耗。
　　对于大多数应用程序来讲，为聚合而使用全长的Oracle 数字很显然是浪费空间。假如知道一个特定的Oracle 数字只需7 个字节而不是22 个字节的话，我们就能压缩聚合工作区域从而多得到3 倍空间。这可以直接转化为执行性能的改善。如此更加充分地利用空间，分类引擎几乎不会占用磁盘的空间；即使确实发生这样的情形，分类引擎读写的数据也只会是该示例中读写数据的四分之一。
　　聚合测试结果
　　随着Group By 查询中处理的组数不断增加，Oracle9i 对于Oracle8i 的优势也不断加强。针对组数很少的一组查询，两次释放的区别并不太明显。为了测试，我们选定了8 个聚合操作符（GroupBy.sql）并且通过设定SORT_AREA_SIZE 规定了所用内存为20MB 和30MB。具有20MB 内存的条件下，Oracle8i 和Oracle9i 都占用了磁盘空间。此时，Oracle9i 所占用的磁盘空间几乎只是Oracle8i 所占用的一半而且速度还快10％。具有30MB 的内存条件下，Oracle9i 正好达到内存要求而Oracle8i 仍需占用磁盘空间。在该测试中，Oracle9i 的速度要快20％。两个测试中所用的并行度均为24，这表明在第一个测试中，24 个进程的每一个进程都使用了20MB 的内存；在第二个测试中使用的内存则为30MB。
　　
　　　
　　图3：Group By 测试结果DOP 为并行度，
　　Sort = SORT_AREA_SIZE
　　动态内存管理
　　专用服务器的PGA 内存
　　在Oracle9i 之前，诸如SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE 和CREATE_B

查看本文来源