リポジトリの作成と とりあえず測ってみた

今回のテストはデータがメモリから充分溢れるように、メモリサイズ16GBの倍の 32GB準備することにしました。

データのロードは外部表を使うのが一番簡単で早い。
また 6coreのCPUなのでデータを6つに分けることと、データを圧縮して速度向上を図ってみました。SSDだと圧縮効果が薄れると思うが、HDDを使う場合は圧縮することによりかなり時間が短くなります。

1. $ dbgen -f -C6 -S1 -s32 &  
2. $ dbgen -f -C6 -S2 -s32 &  
3. ...  
4. $ dbgen -f -C6 -S6 -s32 &  
5.   
6. $ gzip *.tbl  

$ dbgen -f -C6 -S1 -s32 &
$ dbgen -f -C6 -S2 -s32 &
...
$ dbgen -f -C6 -S6 -s32 &

$ gzip *.tbl

以下が、データロードするSQL文。lineitemだけを書いているが、これを他の7つのテーブルにも行う。(元になるSQL文は Create Your Own Oracle TPC-H Playground on Linux  の情報を大いに参考にして作成しました。)

1. CREATE OR REPLACE DIRECTORY tpch_dir AS '/home/oracle/....'  
2. /  
3. CREATE OR REPLACE DIRECTORY zcat_dir AS '/bin'  
4. /  
5.   
6. CREATE TABLE lineitem_ext (  
7.     l_orderkey NUMBER(10)  
8.   , l_partkey NUMBER(10)  
9.   , l_suppkey NUMBER(10)  
10.   , l_linenumber NUMBER(38)  
11.   , l_quantity NUMBER  
12.   , l_extendedprice NUMBER  
13.   , l_discount NUMBER  
14.   , l_tax NUMBER  
15.   , l_returnflag CHAR(1)  
16.   , l_linestatus CHAR(1)  
17.   , l_shipdate VARCHAR2(10)  
18.   , l_commitdate VARCHAR2(10)  
19.   , l_receiptdate VARCHAR2(10)  
20.   , l_shipinstruct VARCHAR2(25)  
21.   , l_shipmode VARCHAR2(10)  
22.   , l_comment VARCHAR2(44)  
23.   )  
24.   ORGANIZATION EXTERNAL (  
25.     TYPE oracle_loader  
26.     DEFAULT DIRECTORY tpch_dir  
27.     ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE  
28.        PREPROCESSOR zcat_dir:'zcat'  
29.        BADFILE 'bad_%a_%p.bad'  
30.        LOGFILE 'log_%a_%p.log'  
31.        FIELDS TERMINATED BY '|'  
32.        MISSING FIELD VALUES ARE NULL  
33.     )  
34.     LOCATION (  
35.         'lineitem.tbl.1.gz'  
36.       , 'lineitem.tbl.2.gz'  
37.       , 'lineitem.tbl.3.gz'  
38.       , 'lineitem.tbl.4.gz'  
39.       , 'lineitem.tbl.5.gz'  
40.       , 'lineitem.tbl.6.gz'  
41.     )  
42.   )   
43.   PARALLEL 6 REJECT LIMIT 0 NOMONITORING  
44. /  
45.   
46. CREATE TABLE lineitem (  
47.     l_orderkey NUMBER(10) NOT NULL  
48.   , l_partkey NUMBER(10) NOT NULL  
49.   , l_suppkey NUMBER(10) NOT NULL  
50.   , l_linenumber INTEGER NOT NULL  
51.   , l_quantity NUMBER NOT NULL  
52.   , l_extendedprice NUMBER NOT NULL  
53.   , l_discount NUMBER NOT NULL  
54.   , l_tax NUMBER NOT NULL  
55.   , l_returnflag CHAR(1) NOT NULL  
56.   , l_linestatus CHAR(1) NOT NULL  
57.   , l_shipdate DATE NOT NULL  
58.   , l_commitdate DATE NOT NULL  
59.   , l_receiptdate DATE NOT NULL  
60.   , l_shipinstruct VARCHAR2(25) NOT NULL  
61.   , l_shipmode VARCHAR2(10) NOT NULL  
62.   , l_comment VARCHAR2(44) NOT NULL  
63.   )  
64.   PCTFREE 1 PCTUSED 99 PARALLEL 6  
65. /  
66.   
67. INSERT /*+append*/ INTO lineitem  
68. SELECT   
69.    l_orderkey  
70.  , l_partkey  
71.  , l_suppkey  
72.  , l_linenumber  
73.  , l_quantity  
74.  , l_extendedprice  
75.  , l_discount  
76.  , l_tax  
77.  , l_returnflag  
78.  , l_linestatus  
79.  , to_date(l_shipdate, 'yyyy-mm-dd')  
80.  , to_date(l_commitdate, 'yyyy-mm-dd')  
81.  , to_date(l_receiptdate, 'yyyy-mm-dd')  
82.  , l_shipinstruct  
83.  , l_shipmode  
84.  , l_comment  
85. FROM lineitem_ext  
86. /  
87.   
88. BEGIN  
89.   dbms_stats.gather_table_stats(ownname => 'TPCH_&TESTID.', tabname => 'LINEITEM', degree => 6, cascade => TRUE) ;  
90. END;  
91. /  

CREATE OR REPLACE DIRECTORY tpch_dir AS '/home/oracle/....'
/
CREATE OR REPLACE DIRECTORY zcat_dir AS '/bin'
/

CREATE TABLE lineitem_ext (
    l_orderkey NUMBER(10)
  , l_partkey NUMBER(10)
  , l_suppkey NUMBER(10)
  , l_linenumber NUMBER(38)
  , l_quantity NUMBER
  , l_extendedprice NUMBER
  , l_discount NUMBER
  , l_tax NUMBER
  , l_returnflag CHAR(1)
  , l_linestatus CHAR(1)
  , l_shipdate VARCHAR2(10)
  , l_commitdate VARCHAR2(10)
  , l_receiptdate VARCHAR2(10)
  , l_shipinstruct VARCHAR2(25)
  , l_shipmode VARCHAR2(10)
  , l_comment VARCHAR2(44)
  )
  ORGANIZATION EXTERNAL (
    TYPE oracle_loader
    DEFAULT DIRECTORY tpch_dir
    ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
       PREPROCESSOR zcat_dir:'zcat'
       BADFILE 'bad_%a_%p.bad'
       LOGFILE 'log_%a_%p.log'
       FIELDS TERMINATED BY '|'
       MISSING FIELD VALUES ARE NULL
    )
    LOCATION (
        'lineitem.tbl.1.gz'
      , 'lineitem.tbl.2.gz'
      , 'lineitem.tbl.3.gz'
      , 'lineitem.tbl.4.gz'
      , 'lineitem.tbl.5.gz'
      , 'lineitem.tbl.6.gz'
    )
  ) 
  PARALLEL 6 REJECT LIMIT 0 NOMONITORING
/

CREATE TABLE lineitem (
    l_orderkey NUMBER(10) NOT NULL
  , l_partkey NUMBER(10) NOT NULL
  , l_suppkey NUMBER(10) NOT NULL
  , l_linenumber INTEGER NOT NULL
  , l_quantity NUMBER NOT NULL
  , l_extendedprice NUMBER NOT NULL
  , l_discount NUMBER NOT NULL
  , l_tax NUMBER NOT NULL
  , l_returnflag CHAR(1) NOT NULL
  , l_linestatus CHAR(1) NOT NULL
  , l_shipdate DATE NOT NULL
  , l_commitdate DATE NOT NULL
  , l_receiptdate DATE NOT NULL
  , l_shipinstruct VARCHAR2(25) NOT NULL
  , l_shipmode VARCHAR2(10) NOT NULL
  , l_comment VARCHAR2(44) NOT NULL
  )
  PCTFREE 1 PCTUSED 99 PARALLEL 6
/

INSERT /*+append*/ INTO lineitem
SELECT 
   l_orderkey
 , l_partkey
 , l_suppkey
 , l_linenumber
 , l_quantity
 , l_extendedprice
 , l_discount
 , l_tax
 , l_returnflag
 , l_linestatus
 , to_date(l_shipdate, 'yyyy-mm-dd')
 , to_date(l_commitdate, 'yyyy-mm-dd')
 , to_date(l_receiptdate, 'yyyy-mm-dd')
 , l_shipinstruct
 , l_shipmode
 , l_comment
FROM lineitem_ext
/

BEGIN
  dbms_stats.gather_table_stats(ownname => 'TPCH_&TESTID.', tabname => 'LINEITEM', degree => 6, cascade => TRUE) ;
END;
/

また、SSDなので書き込みが発生しないように、テーブルスペースを readonlyにしておくことも忘れない。

1. ALTER TABLESPACE tpch_&TESTID. READ ONLY;  

ALTER TABLESPACE tpch_&TESTID. READ ONLY;

あと、もう１つ大人な事情があった！
今回はインデックスを利用できない時の処理速度を測定したかったので、TPC-Hを正確に測定する際は必ず作成しなければいけない primary key, foreign key を全て作成しないようにしました。
なので全てのデータに対して FULL Table Scanとなります。例えば lineitemであれば、次のようなインデックスを作成する必要があるのです。

1. -- PK Constraints  
2. CREATE UNIQUE INDEX lineitem_pk   
3.   ON lineitem(l_linenumber, l_orderkey) PARALLEL 2  
4. /  
5. ALTER TABLE lineitem ADD CONSTRAINT lineitem_pk   
6.   PRIMARY KEY(l_linenumber, l_orderkey)  
7.   USING INDEX lineitem_pk  
8. /  
9.   
10. -- FK Constraints  
11. ALTER TABLE lineitem ADD CONSTRAINT lineitem_partsupp_fk   
12.   FOREIGN KEY(l_partkey, l_suppkey)   
13.   REFERENCES partsupp(ps_partkey, ps_suppkey) NOT DEFERRABLE  
14. /  
15. ALTER TABLE lineitem ADD CONSTRAINT lineitem_orders_fk   
16.   FOREIGN KEY (l_orderkey)   
17.   REFERENCES orders(o_orderkey) NOT DEFERRABLE  
18. /  

-- PK Constraints
CREATE UNIQUE INDEX lineitem_pk 
  ON lineitem(l_linenumber, l_orderkey) PARALLEL 2
/
ALTER TABLE lineitem ADD CONSTRAINT lineitem_pk 
  PRIMARY KEY(l_linenumber, l_orderkey)
  USING INDEX lineitem_pk
/

-- FK Constraints
ALTER TABLE lineitem ADD CONSTRAINT lineitem_partsupp_fk 
  FOREIGN KEY(l_partkey, l_suppkey) 
  REFERENCES partsupp(ps_partkey, ps_suppkey) NOT DEFERRABLE
/
ALTER TABLE lineitem ADD CONSTRAINT lineitem_orders_fk 
  FOREIGN KEY (l_orderkey) 
  REFERENCES orders(o_orderkey) NOT DEFERRABLE
/

まあ、正確なTPC-Hの測定にはなりませんが、余計なインデックスは邪魔なので良しとします。

で、前回説明したとおり qgenで作成したSQL文 22個を
デフォルトの blocksize=8k で作成したデータで、とりあえず実行してみた。
そしたら 3436秒 = 57分16秒 だった。

果たしてこれは速いのか。

次回は TPC-Hで使う QphHの計算方法について触れ、
上記の処理時間が、どれ位なものなのかを考えてみることにしよう。

※ 2011/4/5 追記: TPC-H用リポジトリを作成するSQL文の元ネタの場所を追記しました。