SQL> DECLARE
2 l_capture_id NUMBER;3 BEGIN
4 l_capture_id := DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
5 name => ‘Production Workload Capture’,
6 directory => ‘CAPTURE_DIR’,
7 duration => 3600,
8 capture_sts => TRUE,
9 auto_filter => TRUE
10 );11 DBMS_OUTPUT.PUT_LINE(‘Capture ID: ‘ || l_capture_id);12 END;13 /Capture ID: 1

SQL> SELECT capture_id, name, status, start_time, end_time,
2 total_size, capture_size
3 FROM dba_workload_captures
4 WHERE status = ‘RUNNING’;CAPTURE_ID NAME STATUS START_TIME END_TIME TOTAL_SIZE CAPTURE_SIZE
———- ————————- ——— ——————- ——————- ———- ————
1 Production Workload Capture RUNNING 2026-04-01 10:00:00 0 0

— 1. 创建工作负载重演目录
CREATE DIRECTORY replay_dir AS ‘/u01/app/oracle/replay’;– 2. 准备工作负载重演
DECLARE
l_replay_id NUMBER;BEGIN
l_replay_id := DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
replay_name => ‘Test Environment Replay’,
directory => ‘REPLAY_DIR’
);DBMS_OUTPUT.PUT_LINE(‘Replay ID: ‘ || l_replay_id);END;/– 3. 初始化工作负载重演
EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name => ‘Test Environment Replay’,
directory => ‘REPLAY_DIR’
);– 4. 开始工作负载重演
EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY(
synchronization => ‘SYNCHRONIZATION’,
capture_sts => TRUE
);– 5. 监控工作负载重演
SELECT replay_id, name, status, start_time, end_time,
divergence_count, error_count
FROM dba_workload_replays
WHERE status = ‘RUNNING’;– 6. 停止工作负载重演
EXEC DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY;

— 案例：评估数据库升级对性能的影响

— 1. 在生产环境捕获工作负载
DECLARE
l_capture_id NUMBER;BEGIN
l_capture_id := DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
name => ‘Pre-Upgrade Workload’,
directory => ‘CAPTURE_DIR’,
duration => 7200, — 2小时
capture_sts => TRUE,
auto_filter => TRUE
);DBMS_OUTPUT.PUT_LINE(‘Capture ID: ‘ || l_capture_id);END;/– 2. 停止工作负载捕获
EXEC DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;– 3. 将捕获的工作负载传输到测试环境
— 使用操作系统命令复制文件
— cp -r /u01/app/oracle/capture/* /u01/app/oracle/replay/

— 4. 在测试环境准备数据库（升级前）
— 安装与生产环境相同版本的Oracle数据库
— 恢复生产环境的备份

— 5. 在测试环境重放工作负载（升级前）
DECLARE
l_replay_id NUMBER;BEGIN
l_replay_id := DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
replay_name => ‘Pre-Upgrade Replay’,
directory => ‘REPLAY_DIR’
);DBMS_OUTPUT.PUT_LINE(‘Replay ID: ‘ || l_replay_id);END;/EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name => ‘Pre-Upgrade Replay’,
directory => ‘REPLAY_DIR’
);EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY(
synchronization => ‘SYNCHRONIZATION’,
capture_sts => TRUE
);– 6. 升级测试环境数据库
— 执行数据库升级操作

— 7. 在测试环境重放工作负载（升级后）
DECLARE
l_replay_id NUMBER;BEGIN
l_replay_id := DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
replay_name => ‘Post-Upgrade Replay’,
directory => ‘REPLAY_DIR’
);DBMS_OUTPUT.PUT_LINE(‘Replay ID: ‘ || l_replay_id);END;/EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name => ‘Post-Upgrade Replay’,
directory => ‘REPLAY_DIR’
);EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY(
synchronization => ‘SYNCHRONIZATION’,
capture_sts => TRUE
);

— 1. 查看重演摘要
SELECT replay_id, name, status, start_time, end_time,
divergence_count, error_count,
elapsed_time, think_time
FROM dba_workload_replays
WHERE name IN (‘Pre-Upgrade Replay’, ‘Post-Upgrade Replay’);– 2. 生成重演报告
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 100
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL replay_report.html

SELECT DBMS_WORKLOAD_REPLAY.REPORT_REPLAY(
replay_id => replay_id_value,
format => ‘HTML’
) FROM dual;SPOOL OFF;– 3. 分析性能差异
SELECT metric_name,
pre_replay.value AS pre_value,
post_replay.value AS post_value,
((post_replay.value – pre_replay.value) / pre_replay.value) * 100 AS percent_change
FROM (
SELECT metric_name, value
FROM dba_workload_replay_stats
WHERE replay_id = pre_replay_id
) pre_replay,
(
SELECT metric_name, value
FROM dba_workload_replay_stats
WHERE replay_id = post_replay_id
) post_replay
WHERE pre_replay.metric_name = post_replay.metric_name;– 4. 分析SQL语句性能
SELECT sql_id,
pre_replay.executions AS pre_executions,
post_replay.executions AS post_executions,
pre_replay.elapsed_time AS pre_elapsed,
post_replay.elapsed_time AS post_elapsed
FROM (
SELECT sql_id, executions, elapsed_time
FROM dba_workload_replay_sqlstats
WHERE replay_id = pre_replay_id
) pre_replay,
(
SELECT sql_id, executions, elapsed_time
FROM dba_workload_replay_sqlstats
WHERE replay_id = post_replay_id
) post_replay
WHERE pre_replay.sql_id = post_replay.sql_id
ORDER BY ABS(post_replay.elapsed_time – pre_replay.elapsed_time) DESC;

— 1. 检查重演错误
SELECT error_number, error_message, count(*) AS error_count
FROM dba_workload_replay_errors
WHERE replay_id = replay_id_value
GROUP BY error_number, error_message
ORDER BY error_count DESC;– 2. 检查重演分歧
SELECT divergence_type, count(*) AS divergence_count
FROM dba_workload_replay_divergences
WHERE replay_id = replay_id_value
GROUP BY divergence_type
ORDER BY divergence_count DESC;– 3. 检查系统资源使用情况
SELECT * FROM v$system_event
WHERE event LIKE ‘%wait%’
ORDER BY total_waits DESC;– 4. 检查SQL语句执行情况
SELECT sql_id, executions, elapsed_time, buffer_gets, disk_reads
FROM dba_workload_replay_sqlstats
WHERE replay_id = replay_id_value
ORDER BY elapsed_time DESC;– 5. 检查数据库日志
SELECT * FROM v$diag_info;– 查看告警日志
— SELECT * FROM v$alert_log;