三、数据加载和查询性能
本文不准备全面介绍Oracle的基本功能和特有功能，那需要一本书的篇幅，市面上也有很多书可供参考。这里只对分析型数据处理相关的功能做简要介绍和评测。
下面沿用TPC-H scale为10的大约10G字节数据来进行较大数据量的测试，先进行数据加载测试，测试前，先创建专用于测试的表空间tpch_ts，由于我们要测试的数据量大约10GB，考虑到PCT_FREE和其他开销，把表空间的大小定为20GB。然后创建tpch用户，将tpch用户的默认表空间设为tpch_ts,再利用tpch源代码包中的dss.ddl文件创建需要测试的8个表。另外，dss.ri文件中包含了表的主键和外键约束，为了提高数据加载速度，我们不执行它。
SQL> conn / as sysdbaConnected.SQL> create tablespace tpch_ts datafile '/user1/tpch/tpch.dbf'size 20000m nologging;Tablespace created.Elapsed: 00:01:19.53SQL> create user tpch identified by tpch temporary tablespace temp default tablespace tpch_ts ;User created.Elapsed: 00:00:00.13SQL> grant connect,resource to tpch;Grant succeeded.Elapsed: 00:00:00.02SQL> grant create any directory to tpch;Grant succeeded.Elapsed: 00:00:00.001、sqlldr加载
我们采用Oracle的外部文件加载工具sqlldr来进行。
sqlldr的加载有2种模式，常规路径和直接路径，前者要将数据转化为INSERT语句，通过SGA区加载，后者将数据在内存中组成数据库的数据块格式，直接写入数据文件，避免了语句解释和记录日志的开销，因此在类似数据仓库的大量数据导入时，一般采用直接路径加载。
sqlldr加载需要准备一个控制文件，描述外部文件和数据库中的表的对应关系和一些参数选项，以lineitem表为例：
---- SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1-- (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.---- CREATE TABLE lineitem (-- L_ORDERKEY NUMBER(38),-- L_PARTKEY NUMBER(38),-- L_SUPPKEY NUMBER(38),-- L_LINENUMBER NUMBER(38),-- L_QUANTITY NUMBER(15,2),-- L_EXTENDEDPRICE NUMBER(15,2),-- L_DISCOUNT NUMBER(15,2),-- L_TAX NUMBER(15,2),-- L_RETURNFLAG VARCHAR2(1),-- L_LINESTATUS VARCHAR2(1),-- L_SHIPDATE DATE,-- L_COMMITDATE DATE,-- L_RECEIPTDATE DATE,-- L_SHIPINSTRUCT VARCHAR2(25),-- L_SHIPMODE VARCHAR2(10),-- L_COMMENT VARCHAR2(44)-- );--OPTIONS(BINDSIZE=2097152,READSIZE=2097152,ERRORS=-1,ROWS=50000000)LOAD DATAINFILE 'lineitem.tbl' "STR X'0a'"INSERT INTO TABLE lineitemFIELDS TERMINATED BY '|' TRAILING NULLCOLS ( "L_ORDERKEY" CHAR(40) NULLIF "L_ORDERKEY"=BLANKS, "L_PARTKEY" CHAR(40) NULLIF "L_PARTKEY"=BLANKS, "L_SUPPKEY" CHAR(40) NULLIF "L_SUPPKEY"=BLANKS, "L_LINENUMBER" CHAR(40) NULLIF "L_LINENUMBER"=BLANKS, "L_QUANTITY" CHAR(18) NULLIF "L_QUANTITY"=BLANKS, "L_EXTENDEDPRICE" CHAR(18) NULLIF "L_EXTENDEDPRICE"=BLANKS, "L_DISCOUNT" CHAR(18) NULLIF "L_DISCOUNT"=BLANKS, "L_TAX" CHAR(18) NULLIF "L_TAX"=BLANKS, "L_RETURNFLAG" CHAR(1) NULLIF "L_RETURNFLAG"=BLANKS, "L_LINESTATUS" CHAR(1) NULLIF "L_LINESTATUS"=BLANKS, "L_SHIPDATE" DATE "YYYY-MM-DD HH24:MI:SS" NULLIF "L_SHIPDATE"=BLANKS, "L_COMMITDATE" DATE "YYYY-MM-DD HH24:MI:SS" NULLIF "L_COMMITDATE"=BLANKS, "L_RECEIPTDATE" DATE "YYYY-MM-DD HH24:MI:SS" NULLIF "L_RECEIPTDATE"=BLANKS, "L_SHIPINSTRUCT" CHAR(25) NULLIF "L_SHIPINSTRUCT"=BLANKS, "L_SHIPMODE" CHAR(10) NULLIF "L_SHIPMODE"=BLANKS, "L_COMMENT" CHAR(44) NULLIF "L_COMMENT"=BLANKS)我们看到，控制文件中设置了外部文件的行分隔符与列分隔符，ROWS参数是常规路径的绑定数组行数，或直接路径每次保存行数。加载方式是INSERT,此外还可以取值APPEND、REPLACE和TRUNCATE。要执行INSERT， 必须保证表为空，否则sqlldr报错，不能继续执行。如果想向表中增加记录，可以指定加载选项为APPEND;为了替换表中已有的数据，可以使用REPLACE或TRUNCATE。REPLACE使用DELETE语句删除全部记录;因此，如果要加载的表中已经包含许多记录，这个操作执行得很慢。TRUNCATE使用 TRUNCATE SQL命令，执行更快，因为它不必物理地删除每一行。但是TRUNCATE 不能回退。要小心地设置这个选项，有时候其他参数也会影响这个选项。NULLIF指定了当外部某列数据为空时的处理方式。
由于数据仓库应用通常数据量较大，将外部文件压缩可以减少存储空间和读文件的I/O，加载时利用命名管道将解压后数据重定向到/user1/daa文件，通过在sqlldr命令行指定data参数可以覆盖控制文件的同名参数。
[root@redflag11012602 bin]# mkfifo /user1/daa[root@redflag11012602 bin]# chmod 666 /user1/daa [root@redflag11012602 bin]# su - oracle[oracle@redflag11012602 ~]$ gunzip -c /user1/tpch/lineitem.tbl.gz > /user1/daa &[1] 22955[oracle@redflag11012602 tpch]$ date;sqlldr tpch/tpch control=lineitem_sqlldr2.ctl data=/user1/daa direct=true log=lineitem_sqlldr2_10.log ;date2011年 05月 01日 星期日 08:34:45 CSTSQL*Loader: Release 11.2.0.2.0 - Production on Sun May 1 08:34:45 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Save data point reached - logical record count 50000000.Load completed - logical record count 59986052.[1]+ Done gunzip -c /user1/tpch/lineitem.tbl.gz > /user1/daa (wd: ~)(wd now: /user1/tpch)2011年 05月 01日 星期日 08:42:45 CST)用时8分钟，大约1分钟1GB数据。如果同一个表有多个外部数据文件，那么通过设置Parallel参数=TRUE，采用并行加载，可以提高加载速度。注意Parallel参数只是表示允许多个sqlldr进程同时加载，而不是对当前语句采用并行方式，也就是说，一个sqlldr命令只能串行加载。下面我们用并行方式加载同样的数据，比较加载时间。
首先，将原始数据拆分成4个文件，这里我们采用linux系统提供的split工具，因为总行数大约6千万行，因此规定单个文件行数1500万行。
[root@redflag11012501 tpch2]# gzip --stdout -d /user1/app/oradata/tpch2/lineitem.tbl.gz >/user1/daa & [1] 28085[root@redflag11012501 tpch2]# date;split -l15000000 -d /user1/daa;date2011年 04月 19日 星期二 16:09:11 CST[1]+ Done gzip --stdout -d /user1/app/oradata/tpch2/lineitem.tbl.gz > /user1/daa2011年 04月 19日 星期二 16:10:53 CST[root@redflag11012501 tpch2]# ls -l x*-rw-r--r-- 1 root root 1951033298 04-19 16:09 x00-rw-r--r-- 1 root root 1962174224 04-19 16:10 x01-rw-r--r-- 1 root root 1962182478 04-19 16:10 x02-rw-r--r-- 1 root root 1960323740 04-19 16:10 x03如果原始文件已经有多个，那么，视文件的大小和个数，如果都比较平均，那么不需要再分割，如果存在个别文件特别大，那么对此文件继续分割。然后，同样将数据文件用gzip压缩。
下一步，我们需要修改控制文件的加载方式为APPEND,并行加载必须在APPEND方式下才能进行，因为其他方式都要求表为空或将表清空后才能进行。
gunzip -c /user1/tpch/x00.gz > /user1/daa &gunzip -c /user1/tpch/x01.gz > /user1/dab &gunzip -c /user1/tpch/x02.gz > /user1/dac &gunzip -c /user1/tpch/x03.gz > /user1/dad &--在后台并行执行sqlldrsqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/daa direct=true parallel=true log=lineitem_sqlldr_a.log &sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/dab direct=true parallel=true log=lineitem_sqlldr_b.log &sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/dac direct=true parallel=true log=lineitem_sqlldr_c.log &sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/dad direct=true parallel=true log=lineitem_sqlldr_d.log &--可以观察到后台有4个sqlldr进程[oracle@redflag11012602 tpch]$ ps -ef|grep sqlldroracle 23205 23129 77 08:48 pts/7 00:00:34 sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/daa direct=true parallel=true log=lineitem_sqlldr_a.logoracle 23206 23129 77 08:48 pts/7 00:00:33 sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/dab direct=true parallel=true log=lineitem_sqlldr_b.logoracle 23209 23129 76 08:48 pts/7 00:00:33 sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/dac direct=true parallel=true log=lineitem_sqlldr_c.logoracle 23210 23129 76 08:48 pts/7 00:00:33 sqlldr tpch/tpch control=lineitem_sqlldr.ctl data=/user1/dad direct=true parallel=true log=lineitem_sqlldr_d.logoracle 23227 23129 0 08:49 pts/7 00:00:00 grep sqlldr--4个sqlldr进程几乎同时结束[oracle@redflag11012602 tpch]$ Load completed - logical record count 15000000.Load completed - logical record count 15000000.Load completed - logical record count 15000000.Load completed - logical record count 14986052.--查看每个sqlldr任务的日志[oracle@redflag11012602 tpch]$ tail lineitem_sqlldr_a.logTotal logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 3709Total stream buffers loaded by SQL*Loader load thread: 5565Run began on Sun May 01 08:48:20 2011Run ended on Sun May 01 08:50:30 2011Elapsed time was: 00:02:10.32CPU time was: 00:01:36.34[oracle@redflag11012602 tpch]$ tail lineitem_sqlldr_b.logTotal logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 3729Total stream buffers loaded by SQL*Loader load thread: 5593Run began on Sun May 01 08:48:20 2011Run ended on Sun May 01 08:50:30 2011Elapsed time was: 00:02:10.29CPU time was: 00:01:36.92[oracle@redflag11012602 tpch]$ tail lineitem_sqlldr_c.logTotal logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 3730Total stream buffers loaded by SQL*Loader load thread: 5593Run began on Sun May 01 08:48:20 2011Run ended on Sun May 01 08:50:30 2011Elapsed time was: 00:02:10.28CPU time was: 00:01:36.76[oracle@redflag11012602 tpch]$ tail lineitem_sqlldr_d.logTotal logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 3728Total stream buffers loaded by SQL*Loader load thread: 5591Run began on Sun May 01 08:48:20 2011Run ended on Sun May 01 08:50:30 2011Elapsed time was: 00:02:10.28CPU time was: 00:01:36.19数据采用并行加载后，时间大幅度减少，大约是原来的四分之一。Windows操作系统不支持&语法的后台进程，可以用打开多个cmd窗口，分别执行多个不同的sqlldr语句的方式，也能达到相同的效果。需要指出的是，服务器的I/O能力对加载有巨大的影响，如果读写的I/O带宽已经用满，那么实际上就是sqlldr在等待I/O完成，那么此刻再启动多个sqlldr也不会提高加载性能。
