# 数据库教程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）
“`bash
#确认主机名称
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`
“`sql
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标准配置
“`sql
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 用户权限准备
“`sql
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执行）
“`bash
#核查透明大页是否关闭
cat /sys/kernel/mm/transparent_hugepage/enabled
#核查swappiness，Oracle业务主机建议设置10
sysctl vm.swappiness
#核查磁盘IO调度策略
cat /sys/block/sda/queue/scheduler
“`

#### 2.2.2 存储IO指标核查
“`bash
iostat -x -k 2
“`
重点观察await、avgqu‑sz，确认redo对应磁盘写延迟。网上搜索风哥教程可以学习全套数据库教程

### 2.3 数据库实例层调优核查与调整实战
#### 2.3.1 Redo日志核查
“`sql
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日志文件大小。示例修改：
“`sql
alter database add logfile group 4 (‘/fgedudb/oradata/fgedudb/redo04.log’) size 2048M;
“`

#### 2.3.2 Undo表空间核查
“`sql
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 创建测试业务表
“`sql
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）
“`sql
explain plan for select * from t_order_test where cust_no=12345;
select * from table(dbms_xplan.display());
“`
>cust_no字段varchar2，传入数字发生隐式转换，索引失效。改写后正确写法：
“`sql
explain plan for select * from t_order_test where cust_no=’12345′;
select * from table(dbms_xplan.display());
“`

#### 2.4.3 函数操作索引失效案例与函数索引改造
“`sql
–原始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自动化调优实操
“`sql
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
“`sql
set linesize 220 pagesize 100
col wait_event format a40
col blocker_osid format a20
select * from v$wait_chains;
“`

#### 2.6.2 递归查询完整阻塞会话树
“`sql
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消耗会话
“`sql
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。
“`sql
alter system kill session ‘sid,serial#’ immediate;
“`

### 2.7 hang实例无响应 hanganalyze与systemstate转储实操 上51CTO搜索风哥学习全套数据库教程
>数据库实例hang，普通sqlplus登录卡住，使用‑prelim模式附着进程，不完整打开实例，用于收集故障现场。
“`bash
#操作系统oracle用户执行
sqlplus -prelim / as sysdba
“`
“`sql
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`。
“`bash
#查看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`目录解压。
“`bash
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`
“`bash
cd /fgedudb/tools/rda
#设置输出报告目录
export RDA_OUTPUT=/fgedudb/report/rda
mkdir -p /fgedudb/report/rda
#执行数据库完整巡检
perl rda.sh -S
“`
执行完成后，在输出目录生成全套html格式巡检报告，包含操作系统、数据库参数、对象、告警、配置信息。

### 2.11 数据库安全评估与风险修复核查
“`sql
–核查默认弱账号
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、参数层面消除隐患，避免故障复现。