数据库教程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。

基准测试不能直接等同于真实业务，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.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 测试环境前期检查与数据库参数调优
登录数据库
“`bash
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会产生大量归档日志。
“`sql
SELECT name,log_mode,open_mode FROM v$database;
SELECT file_type,percent_space_used FROM v$flash_recovery_area_usage;
“`
调整压测基线参数，部分参数spfile，需要重启实例生效。
“`sql
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参数，需要重启数据库
“`sql
SHUTDOWN IMMEDIATE;
STARTUP;
“`

### 2.2 创建TPC‑C测试专用表空间与测试用户fgedu_tpcc
创建大表空间存放TPC‑C仓库数据，ASM磁盘组+DATA。
“`sql
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。
“`bash
#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`，解压。
“`bash
unzip benchmarksql‑5.0.zip
cd benchmarksql‑5.0
#编译，ant构建
ant
“`
拷贝Oracle JDBC驱动ojdbc8.jar到工具lib目录
“`bash
cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar ./lib/oracle/
“`

### 2.4 BenchmarkSQL配置文件编写
复制oracle模板配置文件
“`bash
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分钟。
“`ini
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，耗时视存储性能而定。
“`bash
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
“`
装载完成，登录数据库检查表是否生成。
“`sql
ALTER SESSION SET CURRENT_SCHEMA=fgedu_tpcc;
SELECT table_name FROM user_tables;
“`
**收集统计信息**，压测前必须执行，否则SQL执行计划错乱。
“`sql
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘FGEDU_TPCC’,degree=>8);
“`

### 2.6 基准压测前准备，手动创建AWR快照
“`sql
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。
“`bash
#后台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。
“`bash
cd /fgedudb/soft/benchmarksql/benchmarksql‑5.0/run
#修改runMins=5做预热，执行runBenchmark
./runBenchmark.sh props_fgedudb_tpcc.properties
“`

### 2.8 正式执行TPC‑C基准压测
预热完成后，再次手动打AWR起始快照，然后执行正式15分钟压测。
“`sql
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 1;
“`
“`bash
cd /fgedudb/soft/benchmarksql/benchmarksql‑5.0/run
./runBenchmark.sh props_fgedudb_tpcc.properties
“`
压测结束，立刻操作：
1. 数据库端创建结束AWR快照
“`sql
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20;
“`
2. 停止后台sar操作系统监控
“`bash
kill -9 `cat /fgedudb/soft/sar_pid.txt`
“`
3. BenchmarkSQL会在`./my‑tpcc‑results`目录输出文本结果，记录NOPM、TPM、延迟指标。

### 2.9 生成AWR、ASH、ADDM性能分析报告
“`sql
–AWR报告，输入压测开始、结束snap_id
@?/rdbms/admin/awrrpt.sql

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

–ADDM自动诊断报告
@?/rdbms/admin/addmrpt.sql
“`
报告输出文件保存，同时查看top等待事件。
“`sql
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、等待事件。
“`sql
–修改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，恢复数据库生产基线参数。
“`sql
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`
“`bash
#!/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
“`
执行权限
“`bash
chmod +x /fgedudb/soft/tpcc_pre_snap.sh
./fgedudb/soft/tpcc_pre_snap.sh
“`

### 2.13 压测常见故障排查SQL
“`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

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

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

相关推荐

联系我们