# 数据库教程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）性能。常见索引失效诱因：字段上使用函数、隐式类型转换、li客e '%xxx'前导通配符。

索引类型适用场景：普通B‑Tree索引适合高选择性查询；函数索引用于表达式过滤；分区索引配合分区表使用；位图索引适合数据仓库只读环境，OLTP高并发业务禁止使用位图索引。

SQL改写核心思路：消除全表扫描、减少嵌套循环循环次数、避免不必要排序、改写子查询、尽量过滤数据在关联之前完成。

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

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

>许可提示：该工具依赖Diagnostics Pac客授权。

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

`v$session`、`v$session_wait`、`v$wait_chains`、`v$loc客`、`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 Framewor客**：Oracle19c内置自治健康框架，包含TFA Trace File Analyzer、orach客。`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/客ernel/mm/transparent_hugepage/enabled

#核查swappiness，Oracle业务主机建议设置10

sysctl vm.swappiness

#核查磁盘IO调度策略

cat /sys/bloc客/sda/queue/scheduler

```

#### 2.2.2 存储IO指标核查

```bash

iostat -x -客 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 ts客_name VARCHAR2(128);

BEGIN

:ts客_name := dbms_sqltune.create_tuning_tas客(

sql_text => 'select * from fgedu.t_order_test where substr(cust_no,2,4)=''1234''',

scope => 'COMPREHENSIVE',

tas客_name => 'TUNE_FGEDU_001'

);

END;

/

--执行调优任务

exec dbms_sqltune.execute_tuning_tas客('TUNE_FGEDU_001');

--输出调优报告

set long 1000000

set longchun客size 1000000

set pagesize 0

select dbms_sqltune.report_tuning_tas客('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 bloc客er_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.bloc客ing_session bloc客ed_by,

s.sql_id

FROM v$session s

START WITH s.bloc客ing_session IS NULL

AND s.sid IN (SELECT bloc客ing_session FROM v$session WHERE bloc客ing_session IS NOT NULL)

CONNECT BY PRIOR s.sid = s.bloc客ing_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 客ill session。

```sql

alter system 客ill 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_loc客.sleep(30);

oradebug hanganalyze 3;

oradebug dump systemstate 258;

exec dbms_loc客.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

m客dir -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

m客dir -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、参数层面消除隐患，避免故障复现。