TPC-H 研究和优化尝试
2025-03-13 00:06 AlfredZhao 阅读(100) 评论(0) 编辑 收藏 举报TPC-H测试提供了8张表，最近做这个测试，记录下过程中的关键点备忘。
- 1.整体理解TPC-H 8张表
- 2.建立主外键约束后测试22条SQL
- 3.分区表改造，确认分区字段
- 4.重新测试22条SQL
- 5.In-Memory效果测试
- 6.大表并行度设置
1.整体理解TPC-H 8张表
TPC-H 属于 雪花型查询（Snowflake Schema）。
- 雪花型结构，是在星型查询的基础上，维度表进一步规范化，形成多层级的关联关系。
TPC-H 的 8 张表之间的关系如下：
事实表：
- LINEITEM：记录订单明细信息。主键为l_orderkey,l_linenumber
- ORDERS：记录订单信息。主键为o_orderkey
维度表：
- PART：记录零件信息。主键为p_partkey
- SUPPLIER：记录供应商信息。主键为s_suppkey
- PARTSUPP：记录零件与供应商的关系。主键为ps_partkey
- CUSTOMER：记录客户信息。主键为c_custkey
- NATION：记录国家信息。主键为n_nationkey
- REGION：记录地区信息。主键为r_regionkey
这些表之间的关系是多层级的，例如：
- LINEITEM 关联到 PARTSUPP，PARTSUPP 关联到 PART 和 SUPPLIER。
- SUPPLIER 关联到 NATION，NATION 关联到 REGION。
这种多层级的规范化结构符合 雪花型查询 的特点，下图ER图来源于官方tpc-h压缩包中的specification说明文档内容：
2.建立主外键约束后测试22条SQL
原环境未建立任何主键和索引，可以先参考dss.ri修改创建必要的主键和外键约束，我依照本次测试环境微调如下：
-- 1.主键约束（显式指定索引表空间tbs_alfred_index）：
-- For table REGION
ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table NATION
ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table PART
ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table SUPPLIER
ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table CUSTOMER
ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table ORDERS
ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY) USING INDEX TABLESPACE tbs_alfred_index;
-- For table LINEITEM
ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY, L_LINENUMBER) USING INDEX TABLESPACE tbs_alfred_index;
-- 2.外键约束：
-- For table NATION
ALTER TABLE NATION
ADD CONSTRAINT NATION_FK1 FOREIGN KEY (N_REGIONKEY) REFERENCES REGION;
-- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD CONSTRAINT SUPPLIER_FK1 FOREIGN KEY (S_NATIONKEY) references NATION;
-- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_FK1 FOREIGN KEY (C_NATIONKEY) references NATION;
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD CONSTRAINT PARTSUPP_FK1 FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
ALTER TABLE PARTSUPP
ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references PART;
-- For table ORDERS
ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY (O_CUSTKEY) references CUSTOMER;
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) references ORDERS;
ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;
这些其实并没有针对性优化，效果反而不如之前。
比如其中的query5，就因为走错执行计划，比之前什么都没有的情况还要慢很多倍。
3.分区表改造，确认分区字段
对最大的两张事实表做分区改造。
简单浏览了相关SQL，虽然查询的数据很多，但也都是对日期有一定过滤的，因此先尝试简单的按时间range分区：
- LINEITEM：分区字段：l_shipdate 。
- ORDERS：分区字段：o_orderdate。
目前两张表分区字段的数据存放都是7年：1992~1998。可以验证：
--验证数据（选做，我这里已经帮大家验证过脚本生成的固定是这些年的数据）
sqlplus tpch/tpch@alfred
select to_char(l_shipdate, 'yyyy'), count(*) from LINEITEM group by to_char(l_shipdate, 'yyyy') order by 1;
select to_char(o_orderdate, 'yyyy'), count(*) from ORDERS group by to_char(o_orderdate, 'yyyy') order by 1;
两张事实表的分区语句参考，为了考虑灵活性，我选择了interval分区，并显示指定了当前存在数据的年份：
--drop table ORDERS purge;
--ALTER TABLE LINEITEM DROP CONSTRAINT LINEITEM_FK1;
CREATE TABLE ORDERS (O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) 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)
PARTITION BY RANGE (o_orderdate)
INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) -- 一年一个分区
(
PARTITION p_1992 VALUES LESS THAN (TO_DATE('1993-01-01', 'YYYY-MM-DD')),
PARTITION p_1993 VALUES LESS THAN (TO_DATE('1994-01-01', 'YYYY-MM-DD')),
PARTITION p_1994 VALUES LESS THAN (TO_DATE('1995-01-01', 'YYYY-MM-DD')),
PARTITION p_1995 VALUES LESS THAN (TO_DATE('1996-01-01', 'YYYY-MM-DD')),
PARTITION p_1996 VALUES LESS THAN (TO_DATE('1997-01-01', 'YYYY-MM-DD')),
PARTITION p_1997 VALUES LESS THAN (TO_DATE('1998-01-01', 'YYYY-MM-DD')),
PARTITION p_1998 VALUES LESS THAN (TO_DATE('1999-01-01', 'YYYY-MM-DD'))
);
--drop table LINEITEM purge;
CREATE TABLE LINEITEM (L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) 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)
PARTITION BY RANGE (l_shipdate)
INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) -- 一年一个分区
(
PARTITION p_1992 VALUES LESS THAN (TO_DATE('1993-01-01', 'YYYY-MM-DD')),
PARTITION p_1993 VALUES LESS THAN (TO_DATE('1994-01-01', 'YYYY-MM-DD')),
PARTITION p_1994 VALUES LESS THAN (TO_DATE('1995-01-01', 'YYYY-MM-DD')),
PARTITION p_1995 VALUES LESS THAN (TO_DATE('1996-01-01', 'YYYY-MM-DD')),
PARTITION p_1996 VALUES LESS THAN (TO_DATE('1997-01-01', 'YYYY-MM-DD')),
PARTITION p_1997 VALUES LESS THAN (TO_DATE('1998-01-01', 'YYYY-MM-DD')),
PARTITION p_1998 VALUES LESS THAN (TO_DATE('1999-01-01', 'YYYY-MM-DD'))
);
sqlldr重新导入数据
还是使用之前的导入方法，重新导入这两张表数据到新建的分区表中。
# 7.ORDERS
sqlldr userid=tpch/tpch@alfred control=ORDERS.ctl log=ORDERS.log bad=ORDERS.bad direct=true
# 8.LINEITEM
sqlldr userid=tpch/tpch@alfred control=LINEITEM.ctl log=LINEITEM.log bad=LINEITEM.bad direct=true
记得维护好主键和外键约束。
这里也给出主外键的删除约束语句，以备测试期间的不时之需。
--删除所有主键和外键？
-- 1.删除主键约束：
-- For table REGION
ALTER TABLE REGION drop PRIMARY KEY ;
-- For table NATION
ALTER TABLE NATION drop PRIMARY KEY ;
-- For table PART
ALTER TABLE PART drop PRIMARY KEY ;
-- For table SUPPLIER
ALTER TABLE SUPPLIER drop PRIMARY KEY ;
-- For table PARTSUPP
ALTER TABLE PARTSUPP drop PRIMARY KEY ;
-- For table CUSTOMER
ALTER TABLE CUSTOMER drop PRIMARY KEY ;
-- For table ORDERS
ALTER TABLE ORDERS drop PRIMARY KEY ;
-- For table LINEITEM
ALTER TABLE LINEITEM drop PRIMARY KEY ;
-- 2.删除外键约束：
-- For table NATION
ALTER TABLE NATION drop CONSTRAINT NATION_FK1;
-- For table SUPPLIER
ALTER TABLE SUPPLIER drop CONSTRAINT SUPPLIER_FK1;
-- For table CUSTOMER
ALTER TABLE CUSTOMER drop CONSTRAINT CUSTOMER_FK1;
-- For table PARTSUPP
ALTER TABLE PARTSUPP drop CONSTRAINT PARTSUPP_FK1;
ALTER TABLE PARTSUPP drop CONSTRAINT PARTSUPP_FK2;
-- For table ORDERS
ALTER TABLE ORDERS drop CONSTRAINT ORDERS_FK1;
-- For table LINEITEM
ALTER TABLE LINEITEM drop CONSTRAINT LINEITEM_FK1;
ALTER TABLE LINEITEM drop CONSTRAINT LINEITEM_FK2;
4.重新测试22条SQL
尝试了一些分析索引，比如连接条件的字段建立索引，重新测试整体效果并不好，个别SQL有效，但对整体并起不到啥作用，甚至会更差，最后我都删掉或者invisible了，因此我个人是不太建议折腾这方面，但你如果是优化高手，或者研究时间很充裕的话，可以试试看。
5.In-Memory效果测试
嗯，我这时间有限，怎么办呢，来试试大名鼎鼎的In-Memory吧，上篇文章《优化利器In-Memory开启和效果》已经简单介绍了下这个特性，这里正好用TPC-H的压力测试场景来验证下In-Memory的威力，总之优化原则就是简单高效。
ALTER TABLE REGION inmemory;
ALTER TABLE NATION inmemory;
ALTER TABLE PART inmemory;
ALTER TABLE SUPPLIER inmemory;
ALTER TABLE PARTSUPP inmemory;
ALTER TABLE CUSTOMER inmemory;
ALTER TABLE ORDERS inmemory;
ALTER TABLE LINEITEM inmemory;
注意，设置完inmemory属性可不算完，要想用到，还需确认其发布成功：
SELECT OWNER, SEGMENT_NAME NAME,
POPULATE_STATUS STATUS
FROM V$IM_SEGMENTS;
--或者干脆查所有字段，更详细的展示：
SELECT * FROM V$IM_SEGMENTS order by 2;
这里起初遇到一些问题，内存不太够，部分分区发布时会报错：out of memory..
我没有使用更高的压缩方式，临时申请了一些内存资源再次尝试，还是报错。
明明按当前压缩比例计算是够用的，究竟咋回事呢，原来默认inmemory area只有70%的空间可用，可以通过查看看到：【1MB的Pool默认只有70%，剩下30%基本都给了64k的Pool】
select * from v$inmemory_area;
这里请教了同事@萧宇，快速得知是有个隐藏参数_inmemory_64k_percent控制inmemory的pool分布比例，默认值就是30：
NAME DESCRIPTION VALUE
----------------------------------- ------------------------------------------------------------------ ------------------------------
_inmemory_64k_percent percentage of in-memory area for 64k pools 30
Elapsed: 00:00:00.01
注意，生产环境自然不建议修改隐藏参数！
但我这里是测试，资源实在是有限，所以在观察目前测试64k的pool其实用不了太多，就直接把它改成了5：
alter system set "_inmemory_64k_percent"=5 scope=spfile;
需要重启数据库生效。
发布的时候，顺便dstat监控下I/O情况，调整内存后，I/O也比以前好一些了？
不重要，只要还走I/O，就一定慢。这跟数据库能力无关，硬件的I/O瓶颈就在那里。
发布的脚本示例：
--vi im_init.sh
select count(*) from REGION;
select count(*) from NATION;
select count(*) from PART;
select count(*) from SUPPLIER;
select count(*) from PARTSUPP;
select count(*) from CUSTOMER;
select count(*) from ORDERS;
select count(*) from LINEITEM;
注意：这些SQL查询完成，不一定就代表发布也完成了，实际观察是会有个过程的，还是要监控等待发布状态确定是完成，然后再执行TPC-H的22条SQL。
SELECT * FROM V$IM_SEGMENTS order by 2;
select * from v$inmemory_area;
终于确认本次全部发布成功，没有任何OOM的报错。
再次跑22条SQL看效果，大部分SQL都很快，秒级别，但是个别慢的是用到了temp表空间，这还是受限于硬件的I/O，不过这也比之前好很多了。
同时，alert日志也报出ORA-04036: 实例或 PDB 使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT。，嗯，PGA又尽可能的给扩了扩。
6.大表并行度设置
本次测试环境只有4个OCPU，也就是8个vCPU；
并行不能开太大，就针对两张大表，设置了表级别的并行度为8，效果提升明显。
alter table LINEITEM parallel(degree 8);
alter table ORDERS parallel(degree 8);
之前个别慢的SQL也都提升到了秒级别，虽然具体测试数据不方便放出来，但可以说结论是本次整体测试执行效果是达到甚至超过了预期。
干脆将其他几张中表也进行设置：
alter table PARTSUPP parallel(degree 4);
alter table PART parallel(degree 4);
alter table SUPPLIER parallel(degree 4);
alter table CUSTOMER parallel(degree 4);
测试结果又进一步提升了。
当然，如果硬件资源充足，进一步加大并行度，理论上还会有更进一步的性能提升，大家如果有条件的话，可以试试看。
AlfredZhao©版权所有「从Oracle起航，领略精彩的IT技术。」
