Oracle 23ai TPC-H 测试环境部署
最近，我在 Oracle Database 23ai 上进行了 TPC-H 100GB 测试，并整理了完整的实施步骤和优化经验。如果你也想评估 Oracle 数据库在决策支持场景下的性能，可以参考我的步骤快速上手。<hr>1. 环境准备
在开始测试之前，先介绍下本次测试环境基本情况：
[*]数据库版本：Oracle Database 23ai
[*]存储：500GB 可用硬盘空间（存放临时文件、数据和索引表空间等）
[*]内存：32GB
[*]CPU：4核处理器（优化并行查询）
[*]操作系统：Oracle Linux 8.10（或 RHEL 兼容版本）
首先要确定在此环境成功安装了23ai数据库，安装数据库的具体步骤如果不清楚可参考《在OCI上快速静默安装23ai数据库》。
需要注意23ai只支持多租户架构，所以我这里测试是在ALFRED的租户下，在这个租户中新建一个tpch测试用户，用户的默认表空间指定为TBS_ALFRED，另外，再建立一个专门存放索引的表空间TBS_ALFRED_INDEX用于后续优化。
alter session set container=ALFRED;--TABLESPACE:tbs_alfred,120Gcreate tablespace tbs_alfred datafile '/u01/app/oracle/oradata/DEMO/alfred/tbs_alfred01.dbf' size 30G;--增加数据文件时报错，发现23ai默认创建的就是大文件表空间，那就直接resize 150G(避坑，这里大方点儿，之前我给了120G发现都不够..)ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DEMO/alfred/tbs_alfred01.dbf' RESIZE 150G;--TABLESPACE:tbs_alfred_index,60G,直接size指定create tablespace tbs_alfred_index datafile '/u01/app/oracle/oradata/DEMO/alfred/tbs_alfred_index01.dbf' size 60G;--创建测试用户tpch，并指定默认表空间为tbs_alfred，赋基本权限create user tpch identified by tpch default tablespace tbs_alfred;grant connect, resource to tpch;ALTER USER TPCH QUOTA UNLIMITED ON TBS_ALFRED;ALTER USER TPCH QUOTA UNLIMITED ON TBS_ALFRED_INDEX;这里创建数据文件时，同时监控看到，目前这套测试环境的IO能力并不强，极限也就是每秒200多M的写入。
为了方便后续测试，顺手配置sqlplus能够直接连接到PDB：
# 配置PDB连接，更新tnsnames.ora后测试连接OKvi /u01/app/oracle/product/23.0.0/db_1/network/admin/tnsnames.orasqlplus tpch/tpch@alfred<hr>2. 下载并安装 TPC-H 工具
TPC 组织官方提供 TPC-H Tools，包括：
[*]DBGEN：生成测试数据（8 张表的 .tbl 数据文件）
[*]QGEN：生成 SQL 查询（TPC-H 22 条查询语句）
(1) 下载 TPC-H 工具
从 TPC 官网 tpc.org 下载 TPC-H 工具包：
[*]https://www.tpc.org/
[*]访问 TPC-H 下载页面
[*]选择 TPC-H Tools，下载 TPC-H_Tools_v3.0.1.zip
这里需要免费注册下，系统会发给你留的邮箱发送一个下载链接，注意这个链接只能下载一次，且有时间限制：
[*]下载成功后解压：(这里用oracle用户解压，方便测试，对于压测类工具个人习惯是放在介质目录/u01/media下)unzip TPC-H_Tools_v3.0.1.zipcd TPC-H V3.0.1
(2) 编译 DBGEN 和 QGEN
TPC-H 需要手动配置 makefile.suite，以指定 数据库类型 和 系统环境。
修改 Makefile
编辑 dbgen 目录下的 makefile.suite，实测只需搜索并修改以下行内容即可：
CC=gccDATABASE=ORACLEMACHINE=LINUXWORKLOAD=TPCH编译
执行make命令进行编译：
make -f makefile.suite成功后，当前目录下会生成：
[*]dbgen（用于生成数据）
[*]qgen（用于生成查询 SQL）
<hr>3. 生成 100GB TPC-H 数据
TPC-H 允许不同规模的数据集，我这里选择 100GB（Scale Factor = 100）：
# vi dbgen100.shtime ./dbgen -s 100 -vf100G也不小了，所以我这里担心时间过长，因此记录下时间，并把它放到后台执行防止期间中断：
nohup sh dbgen100.sh &生成的数据文件包括，可使用命令 ls -lrth *.tbl 查看，直观的了解下各个表大小：
$ ls -lrth *.tbl-rw-r--r--. 1 oracle oinstall 137M Mar6 09:31 supplier.tbl-rw-r--r--. 1 oracle oinstall389 Mar6 09:31 region.tbl-rw-r--r--. 1 oracle oinstall 2.3G Mar6 09:31 part.tbl-rw-r--r--. 1 oracle oinstall12G Mar6 09:31 partsupp.tbl-rw-r--r--. 1 oracle oinstall17G Mar6 09:31 orders.tbl-rw-r--r--. 1 oracle oinstall 2.2K Mar6 09:31 nation.tbl-rw-r--r--. 1 oracle oinstall75G Mar6 09:31 lineitem.tbl-rw-r--r--. 1 oracle oinstall 2.3G Mar6 09:31 customer.tbl后续优化建议：
[*]可以考虑拆分文件，dbgen支持拆分文件，这样同时可方便并行导入，本次100G的测试体量还OK，暂时就先这样。
<hr>4. 在 Oracle 数据库 23ai 中创建 TPC-H 表
TPC-H 提供了 dss.ddl，可以直接创建 8 张表。
这样基础测试基本不用改dss.ddl内容，默认表创建就会在TBS_ALFRED表空间中。
第一轮测试不做优化调整类工作。
另外特别提下，脚本中的字段类型定义，比如INTEGER、VARCHAR这些，并不需要手工改为NUMBER和VARCHAR2类型，Oracle自己就会做这个工作，直接执行创建即可，执行完可以desc检查下表结构。
后续优化建议：
[*]评估使用 分区表 是否可以提高查询性能，比如针对最大的几张表：lineitem、orders、partsupp，有没有合适的分区方式。
<hr>5. 使用 SQL*Loader 批量加载数据
TPC-H 生成的 .tbl 文件需要导入到 Oracle 数据库。我使用 SQL*Loader 进行高效批量导入。
[*]创建sqlldr导入所需要控制文件：
对应测试的8张表，每张表单独一个控制文件，这里我故意将控制文件名字大写，更便于区分：
--1. vi NATION.ctlLOAD DATAINFILE 'nation.tbl'INTO TABLE NATIONTRUNCATEFIELDS TERMINATED BY '|'( N_NATIONKEY INTEGER EXTERNAL, N_NAME CHAR(25), N_REGIONKEY INTEGER EXTERNAL, N_COMMENT CHAR(152))--2. vi REGION.ctlLOAD DATAINFILE 'region.tbl'INTO TABLE REGIONTRUNCATEFIELDS TERMINATED BY '|'( R_REGIONKEY INTEGER EXTERNAL, R_NAME CHAR(25), R_COMMENT CHAR(152))--3. vi SUPPLIER.ctlLOAD DATAINFILE 'supplier.tbl'INTO TABLE SUPPLIERTRUNCATEFIELDS TERMINATED BY '|'( S_SUPPKEY INTEGER EXTERNAL, S_NAME CHAR(25), S_ADDRESS CHAR(40), S_NATIONKEY INTEGER EXTERNAL, S_PHONE CHAR(15), S_ACCTBAL DECIMAL EXTERNAL, S_COMMENT CHAR(101))--4. vi PART.ctlLOAD DATAINFILE 'part.tbl'INTO TABLE PARTTRUNCATEFIELDS TERMINATED BY '|'( P_PARTKEY INTEGER EXTERNAL, P_NAME CHAR(55), P_MFGR CHAR(25), P_BRAND CHAR(10), P_TYPE CHAR(25), P_SIZE INTEGER EXTERNAL, P_CONTAINER CHAR(10), P_RETAILPRICE DECIMAL EXTERNAL, P_COMMENT CHAR(23))--5. vi PARTSUPP.ctlLOAD DATAINFILE 'partsupp.tbl'INTO TABLE PARTSUPPTRUNCATEFIELDS TERMINATED BY '|'( PS_PARTKEY INTEGER EXTERNAL, PS_SUPPKEY INTEGER EXTERNAL, PS_AVAILQTY INTEGER EXTERNAL, PS_SUPPLYCOST DECIMAL EXTERNAL, PS_COMMENT CHAR(199))--6. vi CUSTOMER.ctlLOAD DATAINFILE 'customer.tbl'INTO TABLE CUSTOMERTRUNCATEFIELDS TERMINATED BY '|'( C_CUSTKEY INTEGER EXTERNAL, C_NAME CHAR(25), C_ADDRESS CHAR(40), C_NATIONKEY INTEGER EXTERNAL, C_PHONE CHAR(15), C_ACCTBAL DECIMAL EXTERNAL, C_MKTSEGMENTCHAR(10), C_COMMENT CHAR(117))--7. vi ORDERS.ctlLOAD DATAINFILE 'orders.tbl'INTO TABLE ORDERSTRUNCATEFIELDS TERMINATED BY '|'( O_ORDERKEY INTEGER EXTERNAL, O_CUSTKEY INTEGER EXTERNAL, O_ORDERSTATUS CHAR(1), O_TOTALPRICE DECIMAL EXTERNAL, O_ORDERDATE DATE "YYYY-MM-DD", O_ORDERPRIORITY CHAR(15), O_CLERK CHAR(15), O_SHIPPRIORITY INTEGER EXTERNAL, O_COMMENT CHAR(79))--8. vi LINEITEM.ctlLOAD DATAINFILE 'lineitem.tbl'INTO TABLE LINEITEMTRUNCATEFIELDS TERMINATED BY '|'( L_ORDERKEY INTEGER EXTERNAL, L_PARTKEY INTEGER EXTERNAL, L_SUPPKEY INTEGER EXTERNAL, L_LINENUMBER INTEGER EXTERNAL, L_QUANTITY DECIMAL EXTERNAL, L_EXTENDEDPRICE DECIMAL EXTERNAL, L_DISCOUNT DECIMAL EXTERNAL, L_TAX DECIMAL EXTERNAL, L_RETURNFLAG CHAR(1), L_LINESTATUS CHAR(1), L_SHIPDATE DATE "YYYY-MM-DD", L_COMMITDATE DATE "YYYY-MM-DD", L_RECEIPTDATE DATE "YYYY-MM-DD", L_SHIPINSTRUCT CHAR(25), L_SHIPMODE CHAR(10), L_COMMENT CHAR(44))
[*]运行 SQL*Loader：
# 1.NATIONsqlldr userid=tpch/tpch@alfred control=NATION.ctl log=NATION.log bad=NATION.bad direct=true# 2.REGIONsqlldr userid=tpch/tpch@alfred control=REGION.ctl log=REGION.log bad=REGION.bad direct=true# 3.SUPPLIERsqlldr userid=tpch/tpch@alfred control=SUPPLIER.ctl log=SUPPLIER.log bad=SUPPLIER.bad direct=true# 4.PARTsqlldr userid=tpch/tpch@alfred control=PART.ctl log=PART.log bad=PART.bad direct=true# 5.PARTSUPPsqlldr userid=tpch/tpch@alfred control=PARTSUPP.ctl log=PARTSUPP.log bad=PARTSUPP.bad direct=true# 6.CUSTOMERsqlldr userid=tpch/tpch@alfred control=CUSTOMER.ctl log=CUSTOMER.log bad=CUSTOMER.bad direct=true# 7.ORDERSsqlldr userid=tpch/tpch@alfred control=ORDERS.ctl log=ORDERS.log bad=ORDERS.bad direct=true# 8.LINEITEMsqlldr userid=tpch/tpch@alfred control=LINEITEM.ctl log=LINEITEM.log bad=LINEITEM.bad direct=true# OPTION1：我这里因为先前只给了120G表空间，结果空间不够用，所以调整后继续加载。此外需要注意，更改LINEITEM.ctl为append，否则会清空之前记录sqlldr userid=tpch/tpch@alfred control=LINEITEM.ctl log=LINEITEM.log bad=LINEITEM.bad discard=LINEITEM.dsc direct=true skip=582854696# OPTION2：因为尚未建立任何主键唯一约束，看测试数据不是整数，误以为是数据多了一些，干脆还是全新导入，重新修改LINEITEM.ctl为truncate模式，重复步骤8，结果发现还是600037902行：sqlldr userid=tpch/tpch@alfred control=LINEITEM.ctl log=LINEITEM.log bad=LINEITEM.bad direct=true<hr>6. 运行 TPC-H 22 条查询
TPC-H 预定义了 22 条 SQL 查询，可以使用 qgen 生成 SQL 语句：
这里遇到一个问题，简单记录下：
$ ./qgen -s 100 1 > query1.sqlOpen failed for ./1.sql at qgen.c:170这个报错浪费了些许时间，最后发现是要设置下变量，然后再次执行就OK：
$ export DSS_QUERY=./queries$ ./qgen -s 100 1 > query1.sql确认OK后，直接使用下面命令，快速生成这22个query：
for i in $(seq 1 22); do./qgen -s 100 $i > query$i.sqlecho "Generated query$i.sql"done然后就可以选择在 Oracle 数据库中运行，并记录执行时间：
SQL>@query1@query2...@query22至此，Oracle 23ai TPC-H 测试环境准备已经完成，注意此时还没有任何优化，甚至个别SQL的默认生成语法和Oracle并不兼容，后续文章将记录这些详细调整方法和一些优化技巧，敬请期待！
页:
[1]