数据库教程FGMT08‑Oracle性能优化之基准测试与案例分析

数据库教程FGMT08‑Oracle性能优化之基准测试与案例分析

前言

数据库性能基准测试是评估硬件能力、验证参数调优效果、对比版本变更前后性能差异的核心手段。我是风哥，在大量项目实施中，很多上线前性能隐患，都可以通过标准化基准测试提前暴露出来；缺少基准数据，出现业务慢的时候，就没有客观参照来判定是数据库、存储还是应用的问题。风哥 itpux-com

本文基于Oracle19c单机非CDB数据库，硬件规格为单节点64G内存、8CPU，主机名称fgedu.net.cn，数据库实例与数据库名称fgedudb，测试业务用户fgedu，本地软件路径全部统一替换为/fgedudb。教程完整讲解基准测试理论体系、TPC‑C OLTP事务型基准规范、BenchmarkSQL压测工具部署、测试环境调优、压测执行、AWR/ASH/ADDM性能报告采集、指标解读、故障案例分析，配套大量Shell、SQL实战命令。帮助DBA掌握标准化压测流程，建立性能基线，为后续SQL调优、存储选型、版本升级提供客观数据支撑。

内容大纲：

1. Oracle基准测试整体体系，64G/8CPU实例基线参数说明，测试环境约束与注意事项
2. 理论部分：基准测试分类、TPC‑C规范、核心性能指标、压测工具选型；AWR、ASH、ADDM性能采集原理；基准测试执行流程；测试前环境准备要点
3. 实战操作：操作系统与数据库压测环境调优；BenchmarkSQL工具部署；TPC‑C测试用户、表空间创建；配置压测参数；数据初始化；执行压测；压测过程性能数据采集；压测结束后报告分析；多组对比测试；压测后环境清理
4. 基准测试案例分析，常见压测故障排查；标准化压测Shell脚本
5. 全文总结，基准测试生产最佳实践

一、Oracle性能基准测试基础理论

1.1 Oracle基准测试整体体系

基准测试，是在可控、可复现的环境下，模拟业务负载，采集数据库吞吐量、响应时间、资源消耗的一套标准化测试流程。基准测试分为功能性基准与性能基准；性能基准又分为OLTP联机事务处理基准、OLAP联机分析基准。风哥教程 113257174

• OLTP联机事务处理：模拟电商、政务、金融业务，大量短事务，读写混合，DML频繁，代表标准规范TPC‑C；
• OLAP联机分析处理：报表、统计分析，大查询、大批量扫描，代表规范TPC‑H、TPC‑DS。

基线硬件规格64G内存、8CPU，实例fgedudb，压测场景OLTP关键spfile基线参数：
|参数名称|参数值|参数说明|
|—|—|—|
|memory_target|48G|实例总内存，预留16G内存供操作系统|
|processes|3000|压测并发大，调高最大进程数|
|open_cursors|900|压测会话游标上限|
|session_cached_cursors|300|会话游标缓存，降低软解析|
|undo_retention|900|undo保留时间|
|parallel_max_servers|16|适配8CPU并行进程上限|
|db_recovery_file_dest_size|40G|FRA扩容，压测产生大量归档|
|fast_start_mttr_target|300|实例崩溃恢复目标秒数|

基准测试不能直接等同于真实业务，TPC‑C是模型化负载；真实业务还会存在特殊SQL、业务逻辑、数据分布差异。基准的价值在于横向对比：参数修改前后对比、存储更换前后对比、版本升级前后对比。

1.2 TPC‑C基准规范理论

TPC‑C是事务处理性能委员会定义的联机事务处理基准模型，模拟批发订货业务模型。业务模型包含五类事务：New‑Order新订单、Payment支付、Order‑Status订单状态查询、Delivery发货、Stock‑Level库存查询。

• New‑Order新订单是核心指标，NOPM每分钟新订单数；
• TPM为每分钟全部事务总数量；
• Latency延迟，事务平均、最大响应时间，单位毫秒；
• 仓库warehouse：每个仓库对应约100MB测试数据集，仓库数量决定整体测试数据规模。

并发终端terminals，每一个终端代表一个业务会话线程；压测分为数据装载阶段、预热阶段、正式压测阶段。风哥数据库教程 itpux-com

测试经验：64G/8CPU服务器，TPC‑C仓库数建议设置80‑120；并发终端terminals建议32‑64，不要超过CPU核心数8倍，避免线程过多上下文切换过载。

1.3 主流压测工具选型理论 网上搜索风哥教程可以学习全套数据库教程

1. BenchmarkSQL：开源Java实现TPC‑C压测工具，支持Oracle、PostgreSQL、MySQL，配置简单，社区使用广泛，本次教程采用该工具；
2. HammerDB：多数据库压测工具，支持TPC‑C、TPC‑H，支持图形与命令行；
3. SLOB：专门针对存储IO压测工具，只做IO读写，不模拟业务逻辑；
4. 自定义PL/SQL、JDBC程序：适合完全复刻真实业务SQL，适合业务专项基准。

重要提醒：基准测试工具产生大量DML、归档日志，禁止直接在生产业务库执行完整TPC‑C压测，会造成磁盘IO打满、归档暴涨业务中断，只能在独立测试环境执行。

1.4 基准测试核心性能指标理论

1.5 AWR / ASH / ADDM性能采集理论

1. AWR自动负载信息库：快照采集数据库全量负载，基准测试压测前手动创建快照，压测结束立刻手动创建快照，基于两次快照生成报告，精准覆盖压测时间段；
2. ASH活动会话历史：每秒采集活跃会话样本，分析压测过程瞬时性能抖动；
3. ADDM自动数据库诊断监控：基于AWR快照，自动输出性能问题诊断建议。

默认AWR快照1小时一次，压测持续时间很短，必须手动执行DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()，不能依赖自动快照。

1.6 完整基准测试执行流程理论

1. 环境检查：操作系统资源、磁盘空间、归档、FRA空间充足；数据库参数按照压测基线调整；统计信息收集；
2. 部署压测工具，创建测试表空间、测试用户；
3. 装载TPC‑C测试数据；
4. 执行数据库统计信息收集；
5. 压测前手动创建AWR快照；启动操作系统层面监控（CPU、内存、IO）；
6. 执行预热压测，消除buffer cache冷启动影响；
7. 正式执行基准压测；
8. 压测结束，立刻手动创建AWR快照；停止操作系统监控；
9. 生成AWR、ASH、ADDM报告；提取BenchmarkSQL输出指标；
10. 数据分析，定位瓶颈CPU/IO/锁/日志；
11. 多组变量重复测试（修改参数、存储）做对比；
12. 测试完成清理测试对象，恢复数据库生产基线参数。

1.7 基准测试常见瓶颈理论

1. CPU瓶颈：DB CPU接近100%，SQL解析、逻辑读消耗CPU；优化方向：优化SQL、加大游标缓存、调整并行参数；
2. IO存储瓶颈：top等待事件db file sequential read、db file scattered read，存储IOPS、带宽打满；优化：更换高性能存储，调大buffer cache；
3. Redo日志瓶颈：top等待log file sync，事务提交刷盘慢；优化：调大redo日志组大小，高速存储存放redo；
4. 锁与冲突瓶颈：enq: TX‑row lock contention行锁冲突，TPC‑C模型本身会产生库存锁竞争，降低并发终端数；
5. 内存瓶颈：SGA不足，大量物理读；调高memory_target，检查内存分配。

二、生产完整实战操作

说明：操作系统RHEL7，主机fgedu.net.cn，硬件规格64GB内存，8核CPU；数据库实例fgedudb，全部路径替换/fgedudb；oracle用户执行数据库操作，root操作系统操作；本套操作仅在独立测试环境执行，禁止业务生产库运行完整TPC‑C压测。

2.1 测试环境前期检查与数据库参数调优

登录数据库

su - oracle
export ORACLE_BASE=/fgedudb/app
export ORACLE_HOME=/fgedudb/app/product/19.0.0/dbhome_1
export ORACLE_SID=fgedudb
sqlplus / as sysdba

查看当前归档、FRA空间，确认磁盘有足够余量，TPC‑C会产生大量归档日志。

SELECT name,log_mode,open_mode FROM v$database;
SELECT file_type,percent_space_used FROM v$flash_recovery_area_usage;

调整压测基线参数，部分参数spfile，需要重启实例生效。

ALTER SYSTEM SET memory_target=48G SCOPE=BOTH;
ALTER SYSTEM SET processes=3000 SCOPE=SPFILE;
ALTER SYSTEM SET open_cursors=900 SCOPE=BOTH;
ALTER SYSTEM SET session_cached_cursors=300 SCOPE=BOTH;
ALTER SYSTEM SET parallel_max_servers=16 SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest_size=40G SCOPE=BOTH;
ALTER SYSTEM SET fast_start_mttr_target=300 SCOPE=BOTH;

如果修改spfile参数，需要重启数据库

SHUTDOWN IMMEDIATE;
STARTUP;

2.2 创建TPC‑C测试专用表空间与测试用户fgedu_tpcc

创建大表空间存放TPC‑C仓库数据，ASM磁盘组+DATA。

CREATE TABLESPACE tpcc_data
DATAFILE '+DATA/fgedudb/tpcc_data01.dbf' SIZE 10G AUTOEXTEND ON NEXT 2G MAXSIZE 80G
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE tpcc_temp
TEMPFILE '+DATA/fgedudb/tpcc_temp01.tmp' SIZE 4G AUTOEXTEND ON NEXT 1G MAXSIZE 20G;

CREATE USER fgedu_tpcc IDENTIFIED BY Tpcc@123
DEFAULT TABLESPACE tpcc_data
TEMPORARY TABLESPACE tpcc_temp
ACCOUNT UNLOCK;

GRANT CREATE SESSION,CREATE TABLE,CREATE SEQUENCE,CREATE VIEW TO fgedu_tpcc;
GRANT CONNECT,RESOURCE TO fgedu_tpcc;
GRANT UNLIMITED TABLESPACE TO fgedu_tpcc;

2.3 操作系统部署BenchmarkSQL压测工具（root与oracle）

BenchmarkSQL依赖Java环境，服务器安装JDK1.8。

#root安装jdk1.8
yum install -y java‑1.8.0‑openjdk java‑1.8.0‑openjdk‑devel ant
java -version

#oracle用户准备工具目录
su - oracle
mkdir -p /fgedudb/soft/benchmarksql
cd /fgedudb/soft/benchmarksql

将BenchmarkSQL‑5.0源码包上传到/fgedudb/soft/benchmarksql，解压。

unzip benchmarksql‑5.0.zip
cd benchmarksql‑5.0
#编译，ant构建
ant

拷贝Oracle JDBC驱动ojdbc8.jar到工具lib目录

cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar ./lib/oracle/

2.4 BenchmarkSQL配置文件编写

复制oracle模板配置文件

cd /fgedudb/soft/benchmarksql/benchmarksql‑5.0/run
cp props.ora props_fgedudb_tpcc.properties
vi props_fgedudb_tpcc.properties

配置文件完整内容，适配64G/8CPU，warehouses=100，terminals=48，正式压测运行15分钟。

db=oracle
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@fgedu.net.cn:1521:fgedudb
user=fgedu_tpcc
password=Tpcc@123
warehouses=100
terminals=48
runTxnsPerTerminal=0
runMins=15
limitTxnsPerMin=0
terminalWarehouseFixed=true

#五类TPC‑C事务比例，标准TPC‑C配比
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

resultDirectory=./my‑tpcc‑results

2.5 初始化装载TPC‑C测试数据

装载100个warehouse，数据总量约10G，耗时视存储性能而定。

cd /fgedudb/soft/benchmarksql/benchmarksql‑5.0/run
#执行建表
./runSQL.sh props_fgedudb_tpcc.properties sqlTableCreates
#导入仓库数据
./loadData.sh props_fgedudb_tpcc.properties numWarehouses=100
#创建索引
./runSQL.sh props_fgedudb_tpcc.properties sqlIndexCreates

装载完成，登录数据库检查表是否生成。

ALTER SESSION SET CURRENT_SCHEMA=fgedu_tpcc;
SELECT table_name FROM user_tables;

收集统计信息，压测前必须执行，否则SQL执行计划错乱。

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('FGEDU_TPCC',degree=>8);

2.6 基准压测前准备，手动创建AWR快照

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 1;

记录输出的begin_snap编号，操作系统开启sar监控，记录CPU、IO。

#后台sar采集，输出到文件，压测全程记录
nohup sar -o /fgedudb/soft/tpcc_sar_01.out 5 >/dev/null 2>&1 &
echo $! > /fgedudb/soft/sar_pid.txt

2.7 BenchmarkSQL预热压测（5分钟，消除buffer cache冷读）

预热不采集正式报告，目的把热点数据加载进SGA buffer cache。

cd /fgedudb/soft/benchmarksql/benchmarksql‑5.0/run
#修改runMins=5做预热，执行runBenchmark
./runBenchmark.sh props_fgedudb_tpcc.properties

2.8 正式执行TPC‑C基准压测

预热完成后，再次手动打AWR起始快照，然后执行正式15分钟压测。

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 1;

cd /fgedudb/soft/benchmarksql/benchmarksql‑5.0/run
./runBenchmark.sh props_fgedudb_tpcc.properties

压测结束，立刻操作：

1. 数据库端创建结束AWR快照

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20;

2. 停止后台sar操作系统监控

kill -9 `cat /fgedudb/soft/sar_pid.txt`

3. BenchmarkSQL会在./my‑tpcc‑results目录输出文本结果，记录NOPM、TPM、延迟指标。

2.9 生成AWR、ASH、ADDM性能分析报告

--AWR报告，输入压测开始、结束snap_id
@?/rdbms/admin/awrrpt.sql

--ASH活动会话报告，分析压测期间瞬时抖动
@?/rdbms/admin/ashrpt.sql

--ADDM自动诊断报告
@?/rdbms/admin/addmrpt.sql

报告输出文件保存，同时查看top等待事件。

SELECT event_name,total_waits,time_waited_micro FROM v$system_event ORDER BY time_waited_micro DESC FETCH FIRST 15 ROWS ONLY;

2.10 多组对比测试示例（调整redo大小做对比实验）

做对比基准，只修改一个变量，其他环境完全不变。例如增大redo日志文件，重复整套流程：打快照→压测→打快照→输出报告，对比两组NOPM、等待事件。

--修改redo每组4G
ALTER DATABASE ADD LOGFILE GROUP 4 ('+DATA/fgedudb/redo04.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('+DATA/fgedudb/redo05.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('+DATA/fgedudb/redo06.log') SIZE 4G;

2.11 压测完成环境清理实战

压测全部完成，删除TPCC测试schema，恢复数据库生产基线参数。

DROP USER fgedu_tpcc CASCADE;
DROP TABLESPACE tpcc_data INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tpcc_temp INCLUDING CONTENTS AND DATAFILES;

--恢复生产基线参数
ALTER SYSTEM SET processes=2000 SCOPE=SPFILE;
ALTER SYSTEM SET open_cursors=500 SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size=30G SCOPE=BOTH;

SHUTDOWN IMMEDIATE;
STARTUP;

2.12 基准测试自动化辅助Shell脚本，保存/fgedudb/soft/tpcc_pre_snap.sh

#!/bin/bash
export ORACLE_BASE=/fgedudb/app
export ORACLE_HOME=/fgedudb/app/product/19.0.0/dbhome_1
export ORACLE_SID=fgedudb
export PATH=$ORACLE_HOME/bin:$PATH
echo "====生成压测前AWR快照===="
sqlplus -S / as sysdba <<EOF
set pagesize 80 linesize 140
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT snap_id,to_char(begin_interval_time,'yyyy‑mm‑dd hh24:mi:ss') snap_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 1;
EOF

执行权限

chmod +x /fgedudb/soft/tpcc_pre_snap.sh
./fgedudb/soft/tpcc_pre_snap.sh

2.13 压测常见故障排查SQL

--查看会话与等待
SELECT sid,serial#,username,program,event,sql_id FROM v\$session WHERE username='FGEDU_TPCC';
--查看锁冲突
SELECT sid,type,lmode,request,id1,id2 FROM v\$lock WHERE TYPE IN('TX','TM');
--查看归档生成速率
SELECT sequence#,first_time FROM v\$archived_log ORDER BY sequence# DESC FETCH FIRST 20;

三、总结

基准测试不是简单跑一遍压测工具，它是一套严谨可复现的性能验证流程。我是风哥，在很多项目中，不少同事拿到压测工具直接执行，没有预热、没有打AWR快照、环境变量混杂，最后拿到的指标完全没有参考价值。风哥 itpux-com

本文基于硬件规格64G内存、8CPU的Oracle19c单机实例，主机fgedu.net.cn，全部路径替换为/fgedudb，数据库实例fgedudb。完整讲解基准测试理论、TPC‑C OLTP基准规范、BenchmarkSQL完整部署、数据装载、预热、正式压测、AWR/ASH/ADDM报告采集、瓶颈分析、测试环境清理，配套自动化辅助脚本。

生产做基准测试，必须记住几个核心关键点：

1. 严禁直接在业务生产库执行完整TPC‑C压测，大量DML会打满IO，暴涨归档，造成业务中断，基准测试只能在独立隔离测试环境；
2. 基准测试对比原则：一次测试只修改一个变量，其余环境保持完全一致，才能判定性能差异来自修改项；
3. TPC‑C模型压测必须做预热，消除buffer cache冷读影响；压测前后手动创建AWR快照，不能依赖默认一小时自动快照；
4. 不能只看NOPM、TPM数字，要结合AWR报告的等待事件、CPU、IO综合定位瓶颈；瓶颈分为CPU瓶颈、存储IO瓶颈、redo日志瓶颈、锁冲突、内存瓶颈；
5. 测试结束一定要清理压测schema，把数据库参数恢复到生产基线，不要把压测参数留在测试库；网上搜索风哥教程可以学习全套数据库教程
6. TPC‑C是标准模型负载，不等同于真实业务；真实业务评估，优先采集生产AWR，使用真实业务SQL做专项基准。风哥教程 113257174

掌握基准测试能力，是DBA走向性能调优的重要一步。后续可以延伸学习HammerDB、SLOB存储压测，以及把基准测试流程结合shell做自动化批量对比测试。运维人员不能只会运行压测工具，要理解每一项指标含义，看懂AWR等待事件，区分模型负载与真实业务负载，才可以输出可信、客观的性能评估报告。风哥数据库教程 itpux-com