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

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

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

$ 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  の情報を大いに参考にして作成しました。)

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にしておくことも忘れない。

ALTER TABLESPACE tpch_&TESTID. READ ONLY;

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

-- 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文の元ネタの場所を追記しました。