数据库教程FGMT12‑Oracle性能诊断与综合分析

数据库教程FGMT12‑Oracle性能诊断与综合分析

前言

在Oracle数据库调优工作当中，AWR、ASH属于实例聚合统计工具，适合做整体数据库负载分析。但遇到单条SQL解析异常、优化器选错执行计划、绑定变量带来性能抖动、会话内部等待细节无法被聚合报告完整呈现的疑难故障时，就需要深度跟踪诊断工具。SQL_TRACE、10046事件、10053优化器跟踪、Oradebug、TKPROF、SQL Monitor、SQLTXPLAIN、SPA、Database Replay共同组成Oracle深度诊断工具集，用来捕获SQL完整解析流程、绑定变量、等待事件、CBO成本计算内部逻辑，完成疑难性能问题定位。风哥教程本文围绕跟踪文件获取、各类跟踪事件底层原理、工具实操、复杂故障完整诊断流程展开完整讲解。风哥 itpux‑com

本套风哥教程面向DBA、数据库运维工程师、后端开发人员，全部实验标准化环境配置：主机名称fgedu‑net‑cn，硬件规格64G物理内存、8颗CPU；数据库实例名fgedudb，数据库名fgedudb，测试业务用户名fgedu，文件根目录统一为/fgedudb，整套实验基于Oracle19c企业版完成。风哥教程本文分为前言大纲介绍、核心理论知识、实战操作演练、总结四大模块；实战章节包含大量可直接复制运行SQL脚本、操作系统操作步骤，读者可以在测试环境完整复现全部实验现象，掌握疑难SQL故障深度定位全套手段。网上搜索风哥教程可以学习全套数据库教程

内容大纲

1. Oracle深度性能诊断工具整体架构与适用场景区分
2. Trace跟踪文件存储位置、关键初始化参数，跟踪文件各类获取方法
3. sql_trace基础跟踪机制，10046事件不同级别跟踪原理与风险说明
4. TKPROF格式化原始trc跟踪文件，跟踪报告指标解读
5. Oradebug工具使用，对外部业务会话开启与关闭跟踪
6. 10053优化器跟踪事件，理解CBO成本计算内部逻辑
7. SQL Monitor实时SQL监控原理、视图查询与报告生成实操
8. SQL Tuning Health Check(SQLHC)轻量级健康检查脚本使用
9. SQLTXPLAIN（SQLT）工具安装部署，单SQL综合诊断使用方法
10. SPA（SQL Performance Analyzer）SQL性能分析器原理与完整工作流程
11. Oracle Database Replay数据库负载重演，捕获、预处理、重放、对比报告
12. 各类工具生产环境风险注意事项，疑难故障标准诊断工作流程

一、核心理论知识

本章节为本套风哥教程理论基础，只有理解各个跟踪诊断工具底层原理，才能够根据故障场景选择合适工具，规避跟踪带来额外性能开销。风哥教程 113257174

1.1 Oracle深度诊断工具整体介绍

AWR、ASH属于聚合采样类工具，做实例、会话级别统计汇总；Trace类工具属于完整捕获工具，会记录每一次解析、执行、fetch获取行、等待事件、绑定变量详细信息。不同工具能力与适用场景有明确边界：

1. sql_trace：基础SQL跟踪，记录解析、执行、fetch阶段CPU、IO统计；粒度较为基础。
2. 10046事件：增强版sql_trace，可以控制是否捕获绑定变量、等待事件，划分多个级别，是生产环境最常用SQL跟踪手段。
3. TKPROF：操作系统工具，对原始二进制trc跟踪文件做格式化，输出可读性文本报告。原始trc文件原始内容杂乱，必须经过tkprof格式化才便于分析。
4. Oradebug：Oracle内部调试工具，可以附着到任意数据库进程，开启/关闭跟踪，适合业务会话无法修改应用代码的场景。
5. 10053事件：CBO优化器专属跟踪，记录优化器计算成本、候选计划筛选、统计信息读取全部内部逻辑；专门用来排查统计信息正常，但优化器依然选错执行计划的疑难场景。10053仅在SQL硬解析阶段产生输出，软解析不会生成跟踪输出。
6. SQL Monitor：实时SQL监控，长耗时SQL会被自动监控，不需要手动开启跟踪，以视图和报告形式展示SQL运行时每一步真实行源统计。
7. SQL Tuning Health Check(SQLHC)：轻量级无安装脚本，快速输出单条SQL的健康检查报告，不会在数据库创建对象。
8. SQLTXPLAIN（SQLT）：Oracle官方免费综合诊断工具，安装独立schema；输入sql_id，自动收集表、索引、统计、直方图、AWR历史、执行计划、绑定变量、跟踪信息，输出html综合报告，极大降低单SQL排查工作量。
9. SPA（SQL Performance Analyzer）：Real Application Testing组件工具；捕获业务SQL集合，在变更（升级、打补丁、参数修改）前后分别执行，对比每条SQL的性能变化，识别性能退化SQL。
10. Database Replay数据库负载重演：捕获生产数据库全部外部业务负载，导出负载文件，在测试库完整重放真实业务流量，用来验证升级、参数、存储变更对整体数据库的真实影响。

许可提示：SQL Monitor依赖Diagnostics Pack；SPA、Database Replay属于Real Application Testing授权组件，商用生产使用需要对应许可；sql_trace、10046、10053、tkprof、SQLTXPLAIN、SQLHC无额外许可约束。网上搜索风哥教程可以学习全套数据库教程

1.2 Trace跟踪文件存储与关键参数

Oracle11g之后使用ADR自动诊断仓库统一管理trace文件。关键初始化参数：

1. DIAGNOSTIC_DEST：ADR根目录，本实验环境配置为/fgedudb/diag，trace文件存放路径 DIAGNOSTIC_DEST/diag/rdbms/fgedudb/fgedudb/trace。
2. TIMED_STATISTICS：必须设置TRUE，跟踪才会采集CPU、等待时间；FALSE情况下trace时间统计全部无效。
3. MAX_DUMP_FILE_SIZE：trace文件最大大小，繁忙会话跟踪会生成巨大trc文件，生产一般设置unlimited。
4. TRACEFILE_IDENTIFIER：给trace文件增加自定义标识字符串，方便海量trace文件快速定位目标跟踪文件。

风险：开启跟踪会带来额外CPU、IO开销，生产环境禁止对全实例长时间开启跟踪；尽量只针对故障会话、故障SQL开启，排查完成立刻关闭跟踪。严禁alter system全局设置10046事件，会造成实例所有会话开启跟踪，数据库性能严重下降，仅允许会话级别、指定进程级别开启。

1.3 10046事件级别定义

10046事件是sql_trace增强，不同级别控制捕获内容：

• Level 1：等价普通sql_trace，记录解析、执行、fetch统计；不包含绑定、等待事件。
• Level 4：level1 + 捕获绑定变量bind values。
• Level 8：level1 + 捕获全部等待事件。
• Level 12：level1+4+8，完整跟踪，包含统计、绑定变量、等待事件，绝大多数故障排查使用level12。
• Level 16：增加行源执行统计，用于分析多次执行的计划突变场景。

1.4 10053优化器跟踪事件

10053不跟踪SQL执行过程，只跟踪CBO优化器解析SQL、计算各个候选计划成本的内部过程。当出现统计信息正常，但优化器依然生成错误执行计划，就需要10053跟踪，查看优化器读取了哪些统计、如何计算各个访问路径与连接方式成本，为什么丢弃了更优计划。10053仅在SQL硬解析阶段产生内容，软解析不会生成跟踪输出。风哥数据库教程 itpux‑com

1.5 SQL Monitor原理

当SQL执行时间超过5秒，会被SQL Monitor自动监控；也可以手工强制开启监控。v$sql_monitor、v$sql_plan_monitor视图实时记录SQL每一步行源的行数、CPU、逻辑读、物理读、内存临时段使用。不需要开启trace，几乎无额外开销，适合长时间运行大SQL实时观察执行进度。

1.6 SQLTXPLAIN与SQLHC原理

SQL Tuning Health Check（SQLHC）为轻量级脚本，不需要安装schema，直接运行脚本输入sql_id即可输出报告，适合快速诊断。
SQLTXPLAIN是Oracle支持部门常用免费工具，安装独立schema；输入sql_id，自动收集：对象定义、统计信息、直方图、当前执行计划、历史AWR执行计划、绑定变量、10046跟踪、10053跟踪，打包输出html报告，一站式完成单SQL全方位信息采集，省去DBA多次执行各类查询。

1.7 SPA与Database Replay工作流程

SPA（SQL性能分析器）

1. 捕获基准负载SQL集合，可以来自AWR、STS、trace文件。
2. 在原环境执行，保存基准性能数据。
3. 执行数据库变更（参数、补丁、升级）。
4. 在变更后环境再次执行同一批SQL。
5. 对比前后两次执行，输出哪些SQL性能退化，哪些得到提升。

Database Replay数据库重演

完整四大阶段：捕获负载 → 预处理负载文件 → 在测试库重放负载 → 重放报告对比。捕获阶段记录所有客户端提交给数据库的调用；重放阶段在测试库原样复现业务流量，用于验证数据库重大变更整体风险。

二、实战操作演练

本套风哥教程全部实战操作，操作主机fgedu‑net‑cn，数据库fgedudb，业务用户fgedu，目录/fgedudb，硬件规格64G内存8CPU。

环境说明：操作系统登录oracle用户，设置环境变量指向fgedudb实例；跟踪、调试类操作大部分需要sysdba权限。trace文件输出路径为/fgedudb/diag/rdbms/fgedudb/fgedudb/trace。

2.1 操作系统与数据库环境校验

2.1.1操作系统层面检查（主机fgedu‑net‑cn）

#确认主机名称
hostname
#确认ORACLE_HOME路径全部指向/fgedudb
echo $ORACLE_HOME
#查看内存硬件，确认64G内存
free -h
#查看CPU，确认8CPU
lscpu
#确认trace目录存在
ls -ld /fgedudb/diag/rdbms/fgedudb/fgedudb/trace

校验输出：hostname输出fgedu‑net‑cn，总内存64G，逻辑CPU数量8，trace目录权限oracle用户可读写。

2.1.2 数据库关键初始化参数确认（64G内存8CPU规格）

登录sqlplus / as sysdba查看关键参数

show parameter statistics_level;
show parameter timed_statistics;
show parameter max_dump_file_size;
show parameter diagnostic_dest;
show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;

适配64G内存主机spfile标准配置

alter system set memory_max_target=48G scope=spfile;
alter system set memory_target=48G scope=spfile;
alter system set statistics_level=TYPICAL scope=spfile;
alter system set timed_statistics=TRUE scope=both;
alter system set max_dump_file_size=unlimited scope=both;
alter system set diagnostic_dest='/fgedudb/diag' scope=spfile;

timed_statistics=TRUE是所有跟踪采集时间指标的前提；max_dump_file_size设置unlimited避免大跟踪文件被截断。重启实例使spfile修改生效。

2.1.3 用户权限准备

create user fgedu identified by fgedudb default tablespace users temporary tablespace temp;
grant connect,resource to fgedu;
grant select_catalog_role to fgedu;
grant advisor to fgedu;
grant execute on dbms_monitor to fgedu;
grant execute on dbms_sqldiag to fgedu;
grant execute on dbms_sqlpa to fgedu;
grant execute on dbms_workload_capture to fgedu;
grant execute on dbms_workload_replay to fgedu;

2.2 Trace跟踪文件定位、sql_trace基础跟踪实操

2.2.1 获取当前会话trace文件名

alter session set tracefile_identifier='fgedu_test_trace';
select value from v$diag_info where name='Default Trace File';

返回的完整路径即为trc原始跟踪文件路径，位于/fgedudb/diag/rdbms/fgedudb/fgedudb/trace目录下。网上搜索风哥教程可以学习全套数据库教程

2.2.2 会话级别开启基础sql_trace

conn fgedu/fgedudb@fgedudb
alter session set sql_trace=true;
--执行待诊断业务SQL
select count(*) from t_big;
alter session set sql_trace=false;

sql_trace等价10046 level1，只记录解析执行统计，不会捕获绑定变量、等待事件，故障排查优先使用10046 level12。

2.3 10046事件跟踪三种实现方式实操

方式一：当前会话直接开启10046（可控会话，最简单）

conn fgedu/fgedudb@fgedudb
alter session set tracefile_identifier='fgedu_10046_level12';
alter session set events '10046 trace name context forever, level 12';
--复现故障SQL
select * from t_big where code=100;
--关闭跟踪
alter session set events '10046 trace name context off';

方式二：DBMS_MONITOR包跟踪指定SID,SERIAL#（已知会话信息）

首先查询目标业务会话sid与serial#

select sid,serial#,username,program from v$session where username='FGEDU';

传入查询得到的sid、serial_num

conn / as sysdba
exec dbms_monitor.session_trace_enable(session_id=>142,serial_num=>234,waits=>true,binds=>true);
--通知业务复现故障SQL
exec dbms_monitor.session_trace_disable(session_id=>142,serial_num=>234);

方式三：Oradebug附着操作系统进程跟踪外部会话（应用无法修改会话）

1. 查询目标会话操作系统spid

select s.sid,s.serial#,p.spid from v$session s join v$process p on s.paddr=p.addr where s.username='FGEDU';

拿到spid，登录sqlplus / as sysdba执行oradebug

oradebug setospid 27891
oradebug event 10046 trace name context forever,level 12;
--业务侧复现问题SQL
oradebug event 10046 trace name context off;
oradebug tracefile_name;

oradebug tracefile_name直接打印完整trc文件路径，省去操作系统查找文件步骤。

2.4 TKPROF工具格式化trc跟踪文件实操

原始.trc文件可读性极差，需要使用操作系统tkprof工具格式化，主机fgedu‑net‑cnoracle用户下执行bash命令。

#进入trace目录
cd /fgedudb/diag/rdbms/fgedudb/fgedudb/trace
#tkprof格式化，sys=no过滤系统递归SQL，waits=yes输出等待事件
tkprof fgedudb_ora_27891_fgedu_10046_level12.trc fgedu_10046_report.txt \
sort=prsela,exeela,fchela sys=no waits=yes aggregate=yes explain=fgedu/fgedudb

参数说明：

• sort=prsela,exeela,fchela：按照解析、执行、获取行消耗时间降序排序，慢SQL排在报告最上方；
• sys=no：屏蔽SYS递归内部SQL，减少报告干扰信息；
• waits=yes：汇总所有等待事件统计；
• explain：连接数据库，为每条SQL生成执行计划写入报告。

注意：tkprof无法处理10053优化器跟踪文件，10053只能直接阅读原始trc文本。风哥数据库教程 itpux‑com

2.5 10053 CBO优化器跟踪实操（排查选错执行计划）

注意：10053只在硬解析时产生输出；如果SQL已经存在于共享池，需要刷新共享池或者增加SQL注释触发硬解析。

conn fgedu/fgedudb@fgedudb
alter session set tracefile_identifier='fgedu_10053_trace';
alter session set events '10053 trace name context forever, level 1';
--增加注释，强制硬解析
select /*+ test_10053 */ * from t_big where code=100;
alter session set events '10053 trace name context off';
--获取trace完整路径
select value from v$diag_info where name='Default Trace File';

拿到trc文件，操作系统直接vi查看，tkprof不能解析10053内容。报告重点看：优化器读取的表统计、各个访问路径cost计算值、为什么抛弃候选访问路径。

2.6 SQL Monitor实时SQL监控实操

SQL Monitor适合长时间运行SQL，不需要开启trace，几乎无额外开销。

2.6.1 查询v$sql_monitor视图实时监控信息

set linesize 200 pagesize 100
col sql_text format a80
select sql_id,sql_text,status,elapsed_time,cpu_time,buffer_gets,disk_reads
from v$sql_monitor
where status<>'DONE';

2.6.2 生成SQL Monitor html报告

set long 1000000
set longchunksize 1000000
set pagesize 0
set linesize 200
select dbms_sqltune.report_sql_monitor(sql_id=>'&input_sql_id',type=>'HTML') from dual;

输出html内容复制保存为本地html文件，可以浏览器打开，直观看到每一步行源真实行数、IO、CPU消耗。

2.7 SQLHC(SQL Tuning Health Check)轻量级诊断脚本实操

SQLHC是Oracle支持免费脚本，不需要安装schema，下载sqlhc.sql上传主机fgedu‑net‑cn的/fgedudb/tmp目录。

#上传脚本到/fgedudb/tmp
cd /fgedudb/tmp

sysdba登录执行脚本，参数T代表拥有Diagnostics Pack许可，传入待分析sql_id。

conn / as sysdba
@sqlhc.sql T &sql_id

脚本执行完毕，在当前目录生成zip压缩包，解压后包含html、txt全套诊断信息：表定义、统计、直方图、执行计划、AWR历史信息。网上搜索风哥教程可以学习全套数据库教程

2.8 SQLTXPLAIN(SQLT)综合诊断工具部署实操

SQLT功能比SQLHC更加完整，需要安装独立schema。将sqlt安装包上传至/fgedudb/tmp。

cd /fgedudb/tmp
unzip sqlt.zip

sysdba执行安装脚本，创建sqlt用户。

conn / as sysdba
@sqlt/install/sqltinstall.sql

安装完成之后，针对目标sql_id执行诊断：

conn sqlt/sqlt
@sqlt/run/sqlt.sql &sql_id

执行完毕生成zip报告，包含10046、10053跟踪输出、各类字典视图查询结果，适合提交Oracle支持做疑难SQL分析。

2.9 SPA（SQL Performance Analyzer）SQL性能分析器实操

SPA需要Real Application Testing许可；适用场景：数据库升级、参数变更、索引变更前，评估业务SQL性能退化风险。

1. 第一步：创建SQL调优集STS，从AWR加载SQL。

conn / as sysdba
declare
  v_sts_name varchar2(60):='FGEDU_STS';
begin
  dbms_sqltune.create_sqlset(sqlset_name=>v_sts_name,description=>'fgedu业务SQL集合');
  dbms_sqltune.load_sqlset(
    sqlset_name=>v_sts_name,
    populate_cursor=>cursor(select * from table(dbms_sqltune.select_workload_repository(begin_snap=>100,end_snap=>120))));
end;
/

2. 创建SPA分析任务

declare
  v_task_name varchar2(60):='FGEDU_SPA_TASK';
begin
  dbms_sqlpa.drop_analysis_task(task_name=>v_task_name);
  :v_task_name:=dbms_sqlpa.create_analysis_task(sqlset_name=>'FGEDU_STS',task_name=>v_task_name);
end;
/

3. 执行变更前基准测试

begin
dbms_sqlpa.execute_analysis_task(
task_name=>'FGEDU_SPA_TASK',
execution_type=>'TEST EXECUTE',
execution_name=>'BEFORE_CHANGE');
end;
/

4. 执行数据库变更（修改参数、建索引、打补丁等操作）
5. 变更之后再次执行测试

begin
dbms_sqlpa.execute_analysis_task(
task_name=>'FGEDU_SPA_TASK',
execution_type=>'TEST EXECUTE',
execution_name=>'AFTER_CHANGE');
end;
/

6. 执行对比分析，生成报告

begin
dbms_sqlpa.execute_analysis_task(
task_name=>'FGEDU_SPA_TASK',
execution_type=>'COMPARE PERFORMANCE',
execution_name=>'COMPARE_AFTER');
end;
/

set long 1000000
select dbms_sqlpa.report_analysis_task(task_name=>'FGEDU_SPA_TASK',type=>'HTML') from dual;

报告中可以直接看到哪些SQL性能退化，哪些得到优化。

2.10 Database Replay数据库负载重演完整实操

Database Replay属于Real Application Testing组件，适合完整回放数据库业务负载，验证重大变更整体影响。

1. 创建操作系统目录，创建database directory对象

mkdir -p /fgedudb/replay_capture
chown oracle:oinstall /fgedudb/replay_capture

conn / as sysdba
create directory replay_cap_dir as '/fgedudb/replay_capture';

2. 开启负载捕获

begin
dbms_workload_capture.start_capture(
name=>'FGEDU_CAPTURE_01',
dir=>'REPLAY_CAP_DIR',
duration=>3600); --捕获时长，单位秒
end;
/

业务在捕获时间段正常运行，模拟真实业务流量；时间到达自动结束捕获，也可以手动结束：

exec dbms_workload_capture.finish_capture();

3. 预处理捕获文件，将捕获数据转换为重放可用格式

begin
dbms_workload_replay.process_capture(capture_name=>'FGEDU_CAPTURE_01',dir=>'REPLAY_CAP_DIR');
end;
/

4. 在测试库，初始化重放任务，启动wrc客户端程序（操作系统oracle用户执行）

cd /fgedudb/replay_capture
wrc system/******@fgedudb mode=replay replaydir=/fgedudb/replay_capture

5. 数据库端启动重放

begin
dbms_workload_replay.initialize_replay(replay_name=>'FGEDU_REPLAY_01',replay_dir=>'REPLAY_CAP_DIR');
dbms_workload_replay.prepare_replay(synchronization=>TRUE);
dbms_workload_replay.start_replay();
end;
/

6. 重放结束之后，生成重放对比报告，对比捕获阶段与重放阶段性能差异。

2.11 疑难SQL故障标准诊断完整工作流程

1. 业务反馈SQL性能异常，优先抓取sql_id，查看AWR、ASH，确认是实例整体瓶颈还是单SQL问题。
2. 如果是单SQL计划异常：优先查看dbms_xplan.display_cursor真实执行计划，对比E‑ROWS预估行数与A‑ROWS实际行数，判断统计信息是否失真。
3. 统计信息无明显异常，但是计划依然错误：
   • 会话可控：使用10046 level12跟踪拿到绑定变量、等待事件；
   • 会话不可控：使用dbms_monitor或者oradebug附着进程开启跟踪；
   • 硬解析计划异常：开启10053跟踪，分析CBO优化器内部成本计算。
4. 拿到trc文件，tkprof格式化10046跟踪，分析主要等待、逻辑读、CPU消耗点。
5. 使用SQLHC/SQLTXPLAIN一站式收集对象、统计、历史执行计划信息。
6. 若是数据库变更升级前风险评估，使用SPA做SQL集合对比；若是全实例业务验证，使用Database Replay负载重演。
7. 生产硬性约束：所有跟踪操作完成，务必确认关闭跟踪，禁止残留开启跟踪，避免持续消耗CPU与磁盘IO；所有操作保留回退步骤。

三、风哥针对本文总结

本套风哥教程完整覆盖Oracle深度性能诊断全套工具，从Trace底层原理、10046/10053事件原理，tkprof格式化，oradebug进程附着跟踪，到SQL Monitor、SQLHC、SQLT、SPA、Database Replay整套实操命令，同时明确各个工具许可边界、生产风险约束。

1. 区分工具定位：AWR/ASH做聚合采样；10046用于捕获SQL运行阶段完整等待、绑定变量；10053只用于硬解析CBO优化器内部问题排查，不会跟踪SQL执行阶段；tkprof仅能格式化10046的trc，不能处理10053跟踪文件。
2. 生产环境红线：禁止alter system全局开启10046事件，会造成实例所有会话开启跟踪，引发严重性能故障；跟踪结束必须确认已经关闭跟踪事件，避免trace文件持续暴涨占满磁盘。
3. oradebug属于未公开调试工具，优先使用dbms_monitor包；oradebug仅用于无法获取会话代码修改权限、后台进程跟踪场景。
4. SQLHC无安装依赖，适合快速应急诊断；SQLTXPLAIN需要安装schema，适合深度疑难问题；SQL Monitor几乎无开销，优先用来监控长时间运行SQL。
5. SPA、Database Replay依赖Real Application Testing许可，商用生产环境必须确认许可合规，不可随意上线使用；仅用于测试库做变更风险评估。
6. 10053跟踪必须触发SQL硬解析才会产生内容，如果SQL已经缓存在共享池，需要增加注释或者刷新共享池，否则跟踪文件几乎没有有效输出。
7. 故障排查遵循由浅入深：优先AWR/ASH/真实执行计划，其次使用SQLHC快速采集；确认需要再开启10046/10053跟踪；不要一上来直接开跟踪，避免不必要的系统开销。