使用TPC-H benchmark测试DM8
TPCH的简介
- TPC Benchmark H测试由一系列商业查询组成,这些查询在某种意义上代表复杂的商业分析应用。这些查询给出了一个实际的环境,描绘了批发商的活动以帮助读者将该基准的组件联系起来。
- TPC-H不代表任何特定商业领域里的活动,而是可以被应用到任何需要管理,销售或在全球范围销售某种商品的行业。(比如汽车租赁,食品销售,供应商等)。但TPC-H并不是如何构建实际信息分析系统的模型。
- 测试的目的是减少在信息分析应用中出现的操作的多样性,同时又保留应用最根本的性能特征,也就是:系统的利用率和操作复杂度。
TPC-H
1.TPC-H 的模型
图例:
- 每个表名后面的括号内为这个表的列名的前缀;
- 箭头指的是表与表之间的一对多的关系;
- 每个表名下方的数字或公式表示的是表的行数。
- 一些是 SF(Scale Factor)中的因子,用来获得数据库的大小。在 LINEITEM 表中的行数是近似值
2.TPC-H 各字段的含义
- suplier表
供应商信息:key 、姓名、地址、电话、国家代码、余货、评论 。其中国家代码需要和 nation 表做 join 以获得详细国家信息。
S_SUPPKEY | 供应商序号 |
---|---|
S_NAME | 供应商名称 |
S_ADDRESS | 地址 |
S_NATIONKEY | 国家代码 |
S_PHONE | 电话 |
S_ACCTBAL | 余货 |
S_COMMENT | 备注 |
- region表
地区信息:key 、 地区名
R_REGIONKEY | 地区代码 |
---|---|
R_NAME | 地区名称 |
R_COMMENT | 备注 |
- customer表
用户表:key 、姓名、地址、国家代号、电话等 。用户表按照 key 做 64 个 hash 分区
C_CUSTKEY | 主键 |
---|---|
C_NAME | 姓名 |
C_ADDRESS | 地址 |
C_NATIONKEY | 国家的KEY |
C_PHONE | 电话 |
C_ACCTBAL | 余额 |
C_MKTSEGMENT | 市场营销部 |
C_COMMENT | 备注 |
- part配件表
配件表:key 、 配件名、厂商、品牌、类型、大小、包装、零售价
P_PARTKEY | 配件序号 |
---|---|
P_NAME | 配件名 |
P_MFGR | 厂商 |
P_BRAND | 品牌 |
P_TYPE | 类型 |
P_SIZE | 大小 |
P_CONTAINER | 包装 |
P_RETAILPRICE | 零售价 |
P_COMMENT | 备注 |
- partsupp配件供应表
配件key 、供应商key 、供应数量、批发价、评论
PS_PARTKEY | 配件序号 |
---|---|
PS_SUPPKEY | 供应商序号 |
PS_AVAILQTY | 供应量 |
PS_SUPPLYCOST | 批发价 |
PS_COMMENT | 备注 |
- orders零售订单表
订单key 、客户key 、订单状态、订单总价、下单日期、优先级、收银员、发货优先级
O_ORDERKEY | 订单编号 |
---|---|
O_CUSTKEY | 顾客序号 |
O_ORDERSTATUS | 订单状态 |
O_TOTALPRICE | 总价 |
O_ORDERDATE | 下单日期 |
O_ORDERPRIORITY | 订单优先级 |
O_CLERK | 收银员 |
O_SHIPPRIORITY | 发货优先级 |
O_COMMENT | 备注 |
- lineitem订单明细表
订单key 、配件key 、 供应商key、流水号、数量、价格、折扣、税、明细状态、发货日期、预计到达日期、实际到达日期、运单处理策略(原返?拒收退回?等)、运输途径(火车、汽运、邮寄等)
L_ORDERKEY | 订单编号 |
---|---|
L_PARTKEY | 配件序号 |
L_SUPPKEY | 供应商序号 |
L_LINENUMBER | 流水号 |
L_QUANTITY | 数量 |
L_EXTENDEDPRICE | 价格 |
L_DISCOUNT | 折扣 |
L_TAX | 税 |
L_RETURNFLAG | 退回标记 |
L_LINESTATUS | 明细状态 |
L_SHIPDATE | 发货日期 |
L_COMMITDATE | 预计到达日期 |
L_RECEIPTDATE | 实际到达日期 |
L_SHIPINSTRUCT | 运单处理策略 |
L_SHIPMODE | 运输途径 |
L_COMMENT | 备注 |
- nation
国家信息:key 、 国家名、地区代号
其中地区代号需要和 region 表做 join 以获得地区详细信息
N_NATIONKEY | 国家的key |
---|---|
N_NAME | 地区的名称 |
N_REGIONKEY | 地区的key |
N_COMMENT | 备注 |
TPCH Benchmark 的安装及测试
1.解压文件
[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;2 3 4 5 6 7 8 9 10 11 12 13 14
行号 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
7 rows got
已用时间: 257.424(毫秒). 执行号:28.
注意
- 本实验数据仅供参考。
- 测试数据大小为10G
- 环境为虚拟机。