2021-02-25

TPCH Benchmark 的安装及测试
[root@oracle ~]# ls linux.zip
linux.zip
[root@oracle ~]# pwd
/root
[root@oracle ~]# unzip -q linux.zip
[root@oracle ~]#
2.生成.tbl数据文件
[root@oracle data]# mkdir -p /opt/dbgen/tpch/data
[root@oracle data]# cd /opt/dbgen/tpch/data
[root@oracle data]# pwd
/opt/dbgen/tpch/data
[root@oracle data]# ./dbgen -s 10
TPC-H Population Generator (Version 2.14.0)
Copyright Transaction Processing Performance Council 1994 - 2010
[root@oracle data]# ls *.tbl
customer.tbl nation.tbl partsupp.tbl region.tbl
lineitem.tbl orders.tbl part.tbl supplier.tbl
3.创建dmfldr的控制文件
customer.ctrl

[root@oracle data]# cat customer.ctrl
OPTIONS (
rows = 50000
skip = 0
errors = 1500000
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/customer.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_customer.bad’
APPEND
INTO TABLE CUSTOMER
FIELDS ‘|’
(
C_CUSTKEY ,
C_NAME ,
C_ADDRESS ,
C_NATIONKEY ,
C_PHONE ,
C_ACCTBAL ,
C_MKTSEGMENT ,
C_COMMENT
)
lineitem.ctrl

[root@oracle data]# cat lineitem.ctrl
OPTIONS (
rows = 50000
skip = 0
errors = 60000000
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/lineitem.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_lineitem.bad’
APPEND
INTO TABLE LINEITEM
FIELDS ‘|’
(
L_ORDERKEY ,
L_PARTKEY ,
L_SUPPKEY ,
L_LINENUMBER ,
L_QUANTITY ,
L_EXTENDEDPRICE ,
L_DISCOUNT ,
L_TAX ,
L_RETURNFLAG ,
L_LINESTATUS ,
L_SHIPDATE date format ‘yyyy-mm-dd’,
L_COMMITDATE date format ‘yyyy-mm-dd’,
L_RECEIPTDATE date format ‘yyyy-mm-dd’,
L_SHIPINSTRUCT ,
L_SHIPMODE ,
L_COMMENT
)
nation.ctrl

[root@oracle data]# cat nation.ctrl
OPTIONS (
rows = 50000
skip = 0
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/nation.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_nation.bad’
APPEND
INTO TABLE NATION
FIELDS ‘|’
(
N_NATIONKEY ,
N_NAME ,
N_REGIONKEY ,
N_COMMENT
)
orders.ctrl

[root@oracle data]# cat orders.ctrl
OPTIONS (
rows = 50000
skip = 0
errors = 15000000
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/orders.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_orders.bad’
APPEND
INTO TABLE ORDERS
FIELDS ‘|’
(
O_ORDERKEY ,
O_CUSTKEY ,
O_ORDERSTATUS ,
O_TOTALPRICE ,
O_ORDERDATE date format ‘yyyy-mm-dd’ ,
O_ORDERPRIORITY ,
O_CLERK ,
O_SHIPPRIORITY ,
O_COMMENT
)
part.ctrl

[root@oracle data]# cat part.ctrl
OPTIONS (
rows = 50000
skip = 0
errors = 2000000
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/part.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_part.bad’
APPEND
INTO TABLE PART
FIELDS ‘|’
(
P_PARTKEY ,
P_NAME ,
P_MFGR ,
P_BRAND ,
P_TYPE ,
P_SIZE ,
P_CONTAINER ,
P_RETAILPRICE ,
P_COMMENT
)
partsupp.ctrl

[root@oracle data]# cat partsupp.ctrl
OPTIONS (
rows = 50000
skip = 0
errors = 8000000
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/partsupp.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_partsupp.bad’
APPEND
INTO TABLE PARTSUPP
FIELDS ‘|’
(
PS_PARTKEY ,
PS_SUPPKEY ,
PS_AVAILQTY ,
PS_SUPPLYCOST ,
PS_COMMENT
)
region.ctrl

[root@oracle data]# cat region.ctrl
OPTIONS (
rows = 50000
skip = 0
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/region.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_region.bad’
APPEND
INTO TABLE REGION
FIELDS ‘|’
(
R_REGIONKEY ,
R_NAME ,
R_COMMENT
)
supplier.ctrl

[root@oracle data]# cat supplier.ctrl
OPTIONS (
rows = 50000
skip = 0
errors = 100000
)
LOAD DATA
INFILE ‘/opt/dbgen/tpch/data/supplier.tbl’
BADFILE ‘/opt/dbgen/tpch/data/dfldr_supplier.bad’
APPEND
INTO TABLE SUPPLIER
FIELDS ‘|’
(
S_SUPPKEY ,
S_NAME ,
S_ADDRESS ,
S_NATIONKEY ,
S_PHONE ,
S_ACCTBAL ,
S_COMMENT
)
4.创建表
drop table customer;
drop table lineitem;
drop table nation;
drop table orders;
drop table part;
drop table partsupp;
drop table region;
drop table supplier;

create huge table CUSTOMER
(
C_CUSTKEY int not null,
C_NAME varchar(25) not null,
C_ADDRESS varchar(40) not null,
C_NATIONKEY int not null,
C_PHONE char(15) not null,
C_ACCTBAL float not null,
C_MKTSEGMENT char(10) not null,
C_COMMENT varchar(117) not null,
primary key (C_CUSTKEY)
);

create huge table LINEITEM
(
L_ORDERKEY int not null,
L_PARTKEY int not null,
L_SUPPKEY int not null,
L_LINENUMBER int not null,
L_QUANTITY float not null,
L_EXTENDEDPRICE float not null,
L_DISCOUNT float not null,
L_TAX float 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 char(25) not null,
L_SHIPMODE char(10) not null,
L_COMMENT varchar(44) not null,
primary key(L_ORDERKEY , L_LINENUMBER)
);

create huge table NATION
(
N_NATIONKEY int not null,
N_NAME char(25) not null,
N_REGIONKEY int not null,
N_COMMENT varchar(152) not null,
primary key (N_NATIONKEY)
);

create huge table ORDERS
(
O_ORDERKEY int not null,
O_CUSTKEY int not null,
O_ORDERSTATUS char(1) not null,
O_TOTALPRICE float not null,
O_ORDERDATE date not null,
O_ORDERPRIORITY char(15) not null,
O_CLERK char(15) not null,
O_SHIPPRIORITY integer not null,
O_COMMENT varchar(79) not null,
primary key(O_ORDERKEY)
);

create huge table part
(
P_PARTKEY int not null,
P_NAME varchar(55) not null,
P_MFGR char(25) not null,
P_BRAND char(10) not null,
P_TYPE varchar(25) not null,
P_SIZE int not null,
P_CONTAINER char(10) not null,
P_RETAILPRICE float not null,
P_COMMENT varchar(23) not null,
primary key (P_PARTKEY)
);

create huge table PARTSUPP
(
PS_PARTKEY int not null,
PS_SUPPKEY int not null,
PS_AVAILQTY int not null,
PS_SUPPLYCOST float not null,
PS_COMMENT varchar(199) not null,
primary key (PS_PARTKEY , PS_SUPPKEY)
);

create huge table REGION
(
R_REGIONKEY int not null,
R_NAME char(25) not null,
R_COMMENT varchar(152) not null,
primary key (R_REGIONKEY)
);

create huge table SUPPLIER
(
S_SUPPKEY int not null,
S_NAME char(25) not null,
S_ADDRESS varchar(40) not null,
S_NATIONKEY int not null,
S_PHONE char(15) not null,
S_ACCTBAL float not null,
S_COMMENT varchar(101) not null,
primary key (S_SUPPKEY)
);

5.加载数据
[root@oracle data]# su - dmdba
Last login: Wed Nov 25 23:56:49 CST 2020 on pts/2
[dmdba@oracle ~]$ cd /dm8/bin
[dmdba@oracle bin]$ pwd
/dm8/bin
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/customer.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/lineitem.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/nation.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/orders.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/part.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/partsupp.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/region.ctrl’
[dmdba@oracle bin]$ ./dmfldr userid=SYSDBA/Dameng123:8881 control=’/opt/dbgen/tpch/data/supplier.ctrl’
6.收集统计信息
在disql中执行下面的sql语句

–删除表上所有列的统计信息
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘CUSTOMER’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘LINEITEM’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘NATION’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘ORDERS’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘PART’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘PARTSUPP’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘REGION’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘SUPPLIER’);
–删除表的统计信息
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘CUSTOMER’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘LINEITEM’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘NATION’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘ORDERS’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘PART’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘PARTSUPP’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘REGION’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘SUPPLIER’);
–更新统计信息
SP_TAB_STAT_INIT(‘SYSDBA’,‘REGION’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘NATION’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘PART’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘PARTSUPP’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘SUPPLIER’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘CUSTOMER’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘ORDERS’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘LINEITEM’);

STAT 100 ON REGION(R_NAME) ;
STAT 100 ON REGION(R_REGIONKEY) ;
STAT 100 ON NATION(N_NAME) ;
STAT 100 ON NATION(N_NATIONKEY) ;
STAT 100 ON NATION(N_REGIONKEY) ;
STAT 100 ON SUPPLIER(S_SUPPKEY) ;
STAT 100 ON SUPPLIER(S_NATIONKEY) ;
STAT 100 ON SUPPLIER(S_COMMENT) ;

STAT 100 ON PART(P_SIZE);
STAT 100 ON PART(P_BRAND);
STAT 100 ON PART(P_TYPE);
STAT 100 ON PART(P_NAME);
STAT 100 ON PART(P_PARTKEY);
STAT 100 ON PART(P_CONTAINER);

STAT 100 ON PARTSUPP(PS_SUPPKEY);
STAT 100 ON PARTSUPP(PS_PARTKEY);

STAT 100 ON ORDERS(O_ORDERKEY);
STAT 100 ON ORDERS(O_ORDERDATE);
STAT 100 ON ORDERS(O_ORDERSTATUS);
STAT 100 ON ORDERS(O_ORDERPRIORITY);
STAT 100 ON ORDERS(O_CUSTKEY);
STAT 100 ON ORDERS(O_COMMENT);

STAT 100 ON LINEITEM(L_SUPPKEY);
STAT 100 ON LINEITEM(L_PARTKEY);
STAT 100 ON LINEITEM(L_ORDERKEY);
STAT 100 ON LINEITEM(L_SHIPDATE);
STAT 100 ON LINEITEM(L_SHIPMODE);
STAT 100 ON LINEITEM(L_COMMITDATE);
STAT 100 ON LINEITEM(L_RECEIPTDATE);
STAT 100 ON LINEITEM(L_RETURNFLAG);
STAT 100 ON LINEITEM(L_LINESTATUS);
STAT 100 ON LINEITEM(L_QUANTITY);
STAT 100 ON LINEITEM(L_SHIPINSTRUCT);

STAT 100 ON CUSTOMER(C_CUSTKEY);
STAT 100 ON CUSTOMER(C_MKTSEGMENT);
STAT 100 ON CUSTOMER(C_NATIONKEY);
STAT 100 ON CUSTOMER(C_ACCTBAL);
7.查询验证Q22的执行消耗的时间
测试数据为10G

[root@oracle data]# su - dmdba
Last login: Thu Nov 26 00:23:18 CST 2020 on pts/3
[dmdba@oracle ~]$ cd /dm8/bin
[dmdba@oracle bin]$ ./disql SYSDBA/Dameng123:8881

服务器[LOCALHOST:8881]:处于普通打开状态
登录使用时间: 2.979(毫秒)
disql V8
SQL> select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from ( select substring(c_phone from 1 for 2) as cntrycode, c_acctbal
from customer
where substring(c_phone from 1 for 2) in (‘13’,‘31’,‘23’,‘29’,‘30’,‘18’,‘17’)
and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00
and substring (c_phone from 1 for 2) in (‘13’,‘31’,‘23’,‘29’,‘30’,‘18’,‘17’)
)
and not exists ( select * from orders where o_custkey = c_custkey )
) as custsale
group by cntrycode
order by cntrycode;

行号 CNTRYCODE NUMCUST TOTACCTBAL

---

1 13 2649 1.994812601000001E+07
2 17 2740 2.057487393999999E+07
3 18 2849 2.143699758000001E+07
4 23 2712 2.033765774000004E+07
5 29 2751 2.061271815999995E+07
6 30 2709 2.032755538000004E+07
7 31 2720 2.028712231999998E+07