数据库教程FGMT17‑Oracle性能优化之故障诊断与性能优化

前言

Oracle数据库性能故障的成因横跨操作系统、存储硬件、数据库实例参数、对象设计、索引、SQL语句、统计信息等多个层面，故障处理不能只局限于单一层面。一套完整的调优体系包含自上而下的分析方法论、多维度诊断工具、SQL改写、索引调优、应急处置、自动化分析工具、巡检与安全评估。风哥教程本文围绕全链路调优体系、操作系统与存储调优、数据库实例调优、SQL索引调优、SQL改写实战、SQL Tuning Advisor自动化调优、动态性能视图应急排查、hanganalyze/systemstate转储分析、AHF自治健康框架、OSWatcher、RDA巡检工具，以及故障处理标准流程展开完整讲解。风哥 itpux‑com

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

内容大纲

1. Oracle全链路性能调优体系，自上而下故障诊断方法论
2. 操作系统层面调优要点、存储系统调优核心经验
3. Oracle数据库实例层调优：内存、参数、并行、undo、redo调优
4. SQL语句调优总体思路，索引设计与索引优化原则
5. SQL改写实战典型案例，常见不良SQL模式改造
6. SQL Tuning Advisor自动化SQL调优工具原理与使用
7. 动态性能视图、故障现场应急处置脚本，锁、阻塞、高负载快速定位
8. hanganalyze、systemstate转储，实例hang无响应场景故障收集
9. Oracle AHF自治健康框架，tfactl工具故障采集分析
10. OSWatcher操作系统持续性能采集工具部署与使用
11. RDA数据库巡检工具，配置、执行巡检报告输出
12. 数据库安全评估、风险问题修复；完整故障排查闭环工作流程

一、核心理论知识

本章节为本套风哥教程理论基础，理解分层调优体系与各类诊断工具适用边界，才能避免只盯着SQL而忽略底层硬件，形成完整闭环故障处理思路。风哥教程 113257174

1.1 自上而下性能调优体系

调优遵循由底层向上逐层分析顺序：操作系统→存储设备→数据库实例参数→对象与索引设计→SQL语句。

1. 操作系统层：CPU负载、内存Swap、磁盘IO延迟、网络链路质量；
2. 存储层：IO读写延迟、条带化、redo日志存储隔离、ASM磁盘组平衡；
3. 数据库实例层：SGA/PGA内存配置、redo/undo、并行参数、资源管理器、统计信息配置；
4. 对象索引层：表设计、分区策略、B树索引、函数索引、位图索引，索引失效场景；
5. SQL语句层：执行计划、绑定变量、谓词条件、子查询、关联逻辑改写。

故障处理禁忌：直接跳到SQL调优，如果瓶颈来自存储IO抖动、内存swap，单纯改写SQL无法解决根本问题。网上搜索风哥教程可以学习全套数据库教程

1.2 操作系统与存储调优核心要点

操作系统层面，Oracle业务主机尽量关闭不必要服务，禁用透明大页，配置合适的内核参数（vm.swappiness），降低swap触发概率。
存储系统调优重点：

1. redo日志文件放置高速低延迟存储，redo写直接影响log file sync提交等待；
2. 区分OLTP随机IO、OLAP顺序大IO，业务数据文件、redo、temp、undo尽量做存储隔离；
3. 监控存储平均IO等待时间，OLTP业务单块读延迟建议小于20ms；
4. ASM环境监控磁盘组平衡度，避免磁盘倾斜，局部磁盘热点。

1.3 数据库实例层调优

针对64G内存8CPU主机，SGA+PGA合计分配48G，操作系统预留内存。核心调优对象：内存参数、redo日志大小与组数、undo表空间管理、并行参数、统计信息自动任务、资源管理器。
关键风险：内存设置过大触发OS swap；redo日志过小会频繁触发日志切换等待；undo表空间不足会产生快照过旧ORA‑01555错误。风哥数据库教程 itpux‑com

1.4 SQL与索引调优理论

索引不是越多越好，索引提升查询性能，但会降低DML（insert/update/delete）性能。常见索引失效诱因：字段上使用函数、隐式类型转换、like '%xxx'前导通配符。
索引类型适用场景：普通B‑Tree索引适合高选择性查询；函数索引用于表达式过滤；分区索引配合分区表使用；位图索引适合数据仓库只读环境，OLTP高并发业务禁止使用位图索引。
SQL改写核心思路：消除全表扫描、减少嵌套循环循环次数、避免不必要排序、改写子查询、尽量过滤数据在关联之前完成。

1.5 SQL Tuning Advisor（STA）自动化调优原理

SQL Tuning Advisor属于Oracle调优顾问组件，输入可以是单条SQL、SQL调优集STS。内部执行自动调优优化器，会做四类分析：统计信息校验、索引建议、SQL Profile生成、SQL语句重构改写，输出完整调优报告，给出可执行建议脚本。

许可提示：该工具依赖Diagnostics Pack授权。

1.6 数据库应急故障视图原理

v$session、v$session_wait、v$wait_chains、v$lock、v$sql、v$sql_monitor是应急现场核心视图。
v$wait_chains可以直接展示完整阻塞等待链，定位根阻塞会话，不需要逐层递归查询锁视图。业务突发卡顿优先查询等待链，快速区分是锁阻塞、IO等待、CPU耗尽。网上搜索风哥教程可以学习全套数据库教程

1.7 hanganalyze与systemstate转储原理

当数据库实例hang，普通sqlplus登录卡顿、无法执行普通查询，需要使用sqlplus -prelim / as sysdba附着进程做转储。

1. hanganalyze：分析会话hang等待链条，识别互相阻塞会话；
2. systemstate dump：导出全部会话进程内存状态，包含cursor、锁、会话堆栈，用于实例完全挂起的深度故障分析；

注意：hang发生时收集两次转储，间隔几十秒，便于对比会话状态变化，不要在业务高峰期频繁执行，会带来短暂性能冲击。

1.8 AHF自治健康框架、OSWatcher、RDA工具

1. AHF Autonomous Health Framework：Oracle19c内置自治健康框架，包含TFA Trace File Analyzer、orachk。tfactl命令统一管理，自动分析alert日志、trace、OS指标，一键收集故障诊断包，用于SR服务请求上传。
2. OSWatcher（OSWbb）：操作系统轻量采集工具，持续采集top、vmstat、iostat、mpstat、netstat数据，保存在本地归档，用于回溯历史操作系统性能抖动。
3. RDA Remote Diagnostic Agent：Oracle官方巡检采集工具，收集操作系统配置、数据库参数、对象、日志、性能统计，输出完整HTML巡检报告，用于定期巡检、故障环境信息收集。

1.9 故障闭环处理流程

1. 故障现象确认，记录故障起止时间；
2. 优先采集现场诊断信息（操作系统指标、数据库视图、trace、转储，防止故障恢复后现场消失）；
3. 分层定位根因：操作系统→存储→实例参数→索引对象→SQL；
4. 实施优化，准备回退脚本；
5. 验证优化效果；
6. 输出故障根因报告，完善监控，避免故障复现。风哥数据库教程 itpux‑com

二、实战操作演练

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

环境说明：操作系统登录oracle用户，设置环境变量指向fgedudb实例；部分操作系统工具需要root权限，数据库管理操作使用sysdba。

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

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

#确认主机名称
hostname
#确认ORACLE_HOME路径全部指向/fgedudb
echo $ORACLE_HOME
#确认内存64G，CPU 8核
free -h
lscpu
#查看内核swap参数
cat /proc/sys/vm/swappiness
#确认诊断目录
ls -ld /fgedudb/diag /fgedudb/tools /fgedudb/report

校验输出：hostname输出fgedu‑net‑cn，物理内存64G，逻辑CPU为8颗。

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

登录sqlplus / as sysdba

show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;
show parameter redo_log_files;
show parameter undo_tablespace;
show parameter statistics_level;

适配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;

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_sqltune to fgedu;

2.2 操作系统与存储调优核查实战

2.2.1操作系统内核参数核查（root执行）

#核查透明大页是否关闭
cat /sys/kernel/mm/transparent_hugepage/enabled
#核查swappiness，Oracle业务主机建议设置10
sysctl vm.swappiness
#核查磁盘IO调度策略
cat /sys/block/sda/queue/scheduler

2.2.2 存储IO指标核查

iostat -x -k 2

重点观察await、avgqu‑sz，确认redo对应磁盘写延迟。网上搜索风哥教程可以学习全套数据库教程

2.3 数据库实例层调优核查与调整实战

2.3.1 Redo日志核查

select group#,bytes/1024/1024 mb,status from v$log;
select sequence#,first_time,next_time from v$log_history order by sequence# desc fetch first 20 rows only;

频繁日志切换，增加redo日志文件大小。示例修改：

alter database add logfile group 4 ('/fgedudb/oradata/fgedudb/redo04.log') size 2048M;

2.3.2 Undo表空间核查

select tablespace_name,autoextensible,max_bytes/1024/1024 max_mb from dba_data_files where tablespace_name='UNDOTBS1';
show parameter undo_retention;

2.4 SQL与索引调优实战，SQL改写案例

2.4.1 创建测试业务表

conn fgedu/fgedudb@fgedudb
create table t_order_test(
order_id number,
cust_no varchar2(20),
order_status number,
create_time date
);
insert into t_order_test select rownum,'C'||mod(rownum,2000),mod(rownum,4),sysdate‑mod(rownum,365) from dual connect by rownum<=200000;
create index idx_order_cust on t_order_test(cust_no);
commit;

2.4.2 隐式转换索引失效案例（不良SQL）

explain plan for select * from t_order_test where cust_no=12345;
select * from table(dbms_xplan.display());

cust_no字段varchar2，传入数字发生隐式转换，索引失效。改写后正确写法：

explain plan for select * from t_order_test where cust_no='12345';
select * from table(dbms_xplan.display());

2.4.3 函数操作索引失效案例与函数索引改造

--原始SQL，字段包裹函数，普通索引失效
explain plan for select * from t_order_test where substr(cust_no,2,4)='1234';
select * from table(dbms_xplan.display());

--创建函数索引解决
create index idx_order_sub_cust on t_order_test(substr(cust_no,2,4));

explain plan for select * from t_order_test where substr(cust_no,2,4)='1234';
select * from table(dbms_xplan.display());

2.5 SQL Tuning Advisor自动化调优实操

conn / as sysdba
VARIABLE tsk_name VARCHAR2(128);
BEGIN
:tsk_name := dbms_sqltune.create_tuning_task(
sql_text => 'select * from fgedu.t_order_test where substr(cust_no,2,4)=''1234''',
scope => 'COMPREHENSIVE',
task_name => 'TUNE_FGEDU_001'
);
END;
/

--执行调优任务
exec dbms_sqltune.execute_tuning_task('TUNE_FGEDU_001');

--输出调优报告
set long 1000000
set longchunksize 1000000
set pagesize 0
select dbms_sqltune.report_tuning_task('TUNE_FGEDU_001') from dual;

报告输出会包含统计信息检查、索引建议、SQL Profile建议、SQL重构建议。

2.6 故障应急动态性能视图脚本实战

2.6.1 查看阻塞等待链v$wait_chains

set linesize 220 pagesize 100
col wait_event format a40
col blocker_osid format a20
select * from v$wait_chains;

2.6.2 递归查询完整阻塞会话树

set linesize 220
SELECT
LPAD(' ', 2 * LEVEL)||s.sid session_id,
s.serial#,
s.username,
s.event,
s.seconds_in_wait sec_wait,
s.blocking_session blocked_by,
s.sql_id
FROM v$session s
START WITH s.blocking_session IS NULL
AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY s.sid;

2.6.3 查找数据库当前高CPU消耗会话

set linesize 200
select s.sid,s.serial#,s.username,s.sql_id,p.spid,ss.value cpu_time
from v$session s
join v$process p on s.paddr=p.addr
join v$sesstat ss on s.sid=ss.sid
join v$statname sn on ss.statistic#=sn.statistic#
where sn.name='CPU used by this session'
order by ss.value desc fetch first 15 rows only;

应急处置：确认根阻塞会话，业务评估后可以执行alter system kill session。

alter system kill session 'sid,serial#' immediate;

2.7 hang实例无响应 hanganalyze与systemstate转储实操 上51CTO搜索风哥学习全套数据库教程

数据库实例hang，普通sqlplus登录卡住，使用‑prelim模式附着进程，不完整打开实例，用于收集故障现场。

#操作系统oracle用户执行
sqlplus -prelim / as sysdba

oradebug setmypid;
oradebug unlimit;
oradebug hanganalyze 3;
--间隔30秒，再次收集，便于对比会话状态
exec dbms_lock.sleep(30);
oradebug hanganalyze 3;
oradebug dump systemstate 258;
exec dbms_lock.sleep(30);
oradebug dump systemstate 258;
oradebug tracefile_name;

输出trc路径为/fgedudb/diag/rdbms/fgedudb/fgedudb/trace/xxx.trc，保存trace文件用于事后分析，收集完成退出sqlplus。网上搜索风哥教程可以学习全套数据库教程

2.8 AHF自治健康框架tfactl工具实操

Oracle19c自带AHF，安装路径一般在/fgedudb/oracle-support。

#查看ahf组件状态
tfactl toolstatus

#查看alert日志摘要
tfactl alertsummary

#按时间范围一键收集诊断包，输出到/fgedudb/report
tfactl diagcollect -from "2026‑09‑11 10:00:00" -to "2026‑09‑11 10:30:00" -dest /fgedudb/report

收集完成后在/fgedudb/report生成zip压缩诊断包，包含alert、trace、部分操作系统指标。

2.9 OSWatcher（OSWbb）操作系统采集部署实操

把OSWbb上传主机/fgedudb/tools目录解压。

cd /fgedudb/tools
tar -xvf oswbb.tar
cd oswbb
#设置归档输出目录
export OSWBB_ARCHIVE_DEST=/fgedudb/report/osw
mkdir -p /fgedudb/report/osw
#后台启动，每10秒采集一次，保存数据7天（168小时）
nohup ./startOSWbb.sh 10 168 None /fgedudb/report/osw &

#查看进程是否运行
ps -ef |grep osw

#停止采集
./stopOSWbb.sh

采集生成归档文件，故障发生后可以使用osw分析工具解析生成图表。

2.10 RDA巡检工具实操

RDA脚本放置/fgedudb/tools/rda

cd /fgedudb/tools/rda
#设置输出报告目录
export RDA_OUTPUT=/fgedudb/report/rda
mkdir -p /fgedudb/report/rda
#执行数据库完整巡检
perl rda.sh -S

执行完成后，在输出目录生成全套html格式巡检报告，包含操作系统、数据库参数、对象、告警、配置信息。

2.11 数据库安全评估与风险修复核查

--核查默认弱账号
select username,account_status from dba_users where account_status!='OPEN';

--核查公开权限风险对象
select grantee,privilege,table_name from dba_tab_privs where grantee='PUBLIC';

--核查过期密码配置
select profile,resource_name,limit from dba_profiles where resource_name='PASSWORD_LIFE_TIME';

针对巡检发现的风险：锁定无用账号、回收PUBLIC不必要权限、调整密码安全策略，完成风险闭环修复。

2.12 完整故障排查闭环工作流程实战演示

1. 接收业务故障反馈，记录故障精确时间窗口；
2. 优先保存现场：如果故障正在发生，执行：操作系统top/vmstat/iostat；数据库查询v$wait_chains、v$session；实例hang则执行prelim模式hanganalyze转储；开启OSW采集；使用tfactl抓取诊断包；
3. 分层排查：操作系统资源 → 存储IO延迟 → 数据库参数、redo/undo → 索引对象设计 → SQL语句；
4. 定位根因，编写优化脚本，同步准备完整回退脚本；
5. 业务低峰窗口执行变更；
6. 变更完成，观察操作系统指标、数据库等待事件、业务响应时间，验证优化效果；
7. 输出故障根因文档，补充监控告警规则，规避同类故障再次发生。

生产重要提醒：故障恢复优先保证业务，现场信息采集排在第二位；如果业务已经完全不可用，优先恢复业务，事后回溯历史AWR、OSW历史数据。

三、风哥针对本文总结

本套风哥教程完整覆盖Oracle故障诊断与综合性能调优全套知识，自上而下分层调优理论，操作系统存储调优、数据库实例调优、SQL索引调优、SQL改写案例、SQL Tuning Advisor自动化调优；应急动态视图脚本，实例hang场景hanganalyze/systemstate转储，AHF‑tfactl、OSWatcher、RDA巡检工具，安全风险核查，完整故障闭环处理流程。上51CTO搜索风哥学习全套数据库教程

1. 调优坚持自上而下分层思路：操作系统、存储优先排查，不要上来直接调SQL，底层硬件瓶颈无法依靠SQL改写彻底解决性能问题。
2. 索引不是越多越好，索引优化重点关注隐式转换、字段函数、前导通配符导致索引失效；函数索引用于表达式查询，OLTP环境谨慎使用位图索引。
3. SQL Tuning Advisor自动化调优工具给出的建议仅作参考，DBA需要结合业务评估，不要直接不加校验就全部采纳建议上线。
4. 数据库实例hang无法正常登录，使用sqlplus -prelim / as sysdba做hanganalyze与systemstate转储，务必收集两次转储间隔几十秒，便于分析会话变化；转储操作会短暂消耗实例资源，业务正常运行时不要频繁执行。
5. AHF(TFA)、OSWatcher、RDA三类工具定位不一样：OSWatcher专注操作系统持续指标采集；AHF‑TFA用于日志trace一键打包分析；RDA用于完整环境配置巡检，定期巡检建议使用RDA。
6. 故障处理第一原则：优先保存故障现场，故障消失之后很多动态视图、内存现场信息会全部丢失，事后只能依赖AWR、OSWatcher历史归档数据。
7. 所有变更操作必须准备对应的回退脚本，上线前在测试环境完整验证效果；生产变更尽量避开业务高峰窗口。
8. 故障处理完成后，必须完成闭环：输出根因文档，完善监控告警，从架构、索引、SQL、参数层面消除隐患，避免故障复现。