一、问题概述

数据库整体响应缓慢，AWR 报告无法生成，核心等待事件为 read by other session ，推测存在资源竞争或执行计划不稳定导致的性能瓶颈。本方案从系统层→数据库层→SQL 优化层逐步定位并解决问题。

二、第一步：系统层面健康检查（优先排查）

目的 ：排除服务器硬件资源（CPU / 内存 / I/O）瓶颈，确定是否为系统资源不足导致数据库整体缓慢。

1. 内存使用检查

操作 ：

free -h

判断标准 ：

• 若 Swap 的 used 持续增长（ si/so 不为 0），说明内存不足，数据库进程频繁换页，导致整体卡顿。
• 正常状态： available 内存应占物理内存的 10% 以上， Swap used 接近 0。

临时处理 ：

• 关闭非必要进程释放内存： kill -9 <无用进程PID>
• 若数据库内存配置过高（如 SGA+PGA 超过物理内存 80%），临时调低：

alter system set sga_max_size=32G scope=spfile;  # 需重启生效，结合实际内存调整

2. CPU 负载检查

操作 ：

top  # 实时查看，按P排序CPU使用率
# 或查看10分钟内平均负载
uptime

判断标准 ：

• load average （10 分钟值）超过 CPU 核心数（如 8 核 CPU 负载 > 10），说明 CPU 饱和。
• 若 oracle 进程占比 > 80%，可能是 SQL 执行消耗过多 CPU（如全表扫描、复杂排序）。

临时处理 ：

• 定位高 CPU SQL：在 top 中记录 oracle 进程 PID，查询对应 SQL：

select sql_id, sql_text from gv$process p, gv$sql s where p.spid='<PID>' and p.addr=s.prev_sql_addr;

• 临时终止消耗过高的 SQL 会话：

alter system kill session 'sid,serial#,@inst_id' immediate;  # sid/serial#从gv$session获取

3. I/O 性能检查

操作 ：

iostat -dmx 1 10  # 间隔1秒，采集10次数据

判断标准 ：

• %util （设备利用率）>80%，说明磁盘 I/O 饱和；
• await （平均 I/O 等待时间）>20ms，说明 I/O 响应延迟过高（正常应 < 10ms）。

临时处理 ：

• 暂停非必要的后台任务（如备份、统计信息收集）；
• 若数据库文件和临时文件在同一磁盘，优先将临时文件迁移到 I/O 空闲的磁盘。

三、第二步：数据库层面等待事件分析

1. 查看当前活跃等待事件

操作 ：

set linesize 300
col event for a50
select sql_id, event, count(*) as wait_count 
from gv$session 
where wait_class <> 'Idle' 
group by sql_id, event 
order by wait_count desc;

结果解读 ：

• 若 read by other session 等待数占比 > 50%，说明存在大量会话竞争同一数据块（多会话同时读取某热表，导致缓存融合等待）。

2. 定位引发等待的核心 SQL

操作 ：

-- 结合上一步的sql_id，查询具体SQL文本
select sql_id, sql_fulltext 
from gv$sql 
where sql_id in ('<上一步获取的SQL_ID>');

关键分析 ：

• 此类 SQL 通常涉及 热表读取 （如频繁访问的核心业务表），或执行计划不稳定（时而走索引、时而全表扫描）。

四、第三步：执行计划稳定性检查

目的 ：确认核心 SQL 是否存在执行计划抖动（多次执行使用不同计划），导致 read by other session 等待加剧。

1. 查询 SQL 的历史执行计划

操作 ：

set linesize 300
col begin_interval_time for a25
col plan_hash_value for 9999999999
select 
  q.INSTANCE_NUMBER,
  to_char(s.begin_interval_time, 'yyyy-mm-dd hh24:mi') as begin_time,
  q.plan_hash_value,
  nvl(executions_delta, 0) as exec_count,
  round(elapsed_time_delta/1000000/ nvl(executions_delta,1), 2) as avg_elapsed_sec  -- 平均执行时间
from dba_hist_sqlstat q
join dba_hist_snapshot s on q.snap_id = s.snap_id and q.INSTANCE_NUMBER = s.INSTANCE_NUMBER
where q.sql_id = '<核心SQL_ID>'
order by s.begin_interval_time;

判断标准 ：

• 若存在多个 plan_hash_value ，且 avg_elapsed_sec 差异大（如从 0.1 秒增至 5 秒），说明执行计划抖动。
• 优先选择 执行次数多、平均耗时短 的 plan_hash_value （最优计划）。

五、第四步：绑定最优执行计划（快速解决）

目的 ：通过绑定 SQL Profile，强制 SQL 使用最优执行计划，减少 read by other session 等待。

1. 使用 coe_xfr_sql_profile.sql 脚本绑定计划

操作步骤 ：

1. 登录数据库：

sqlplus / as sysdba

1. 执行脚本（需提前获取脚本并上传至服务器）：

@/path/to/coe_xfr_sql_profile.sql  # 替换为实际脚本路径

1. 按提示输入信息：

Enter value for sql_id: <核心SQL_ID>  # 输入引发等待的SQL_ID
Enter value for plan_hash_value: <最优计划的PHV>  # 输入上一步确定的最优plan_hash_value
Enter value for filename: coe_bind_<SQL_ID>.sql  # 输出脚本名，如coe_bind_2jgtwm5zgsvqh.sql

1. 执行生成的绑定脚本：

@/path/to/coe_bind_<SQL_ID>.sql  # 执行后自动创建SQL Profile

2. 验证绑定结果

操作 ：

-- 确认SQL Profile已创建且生效
select name, sql_id, status 
from dba_sql_profiles 
where sql_id = '<核心SQL_ID>';
-- 状态应为'ENABLED'

效果判断 ：

• 重新执行 SQL，观察响应时间是否降低；
• 再次查询等待事件： read by other session 等待数应显著减少（通常下降 50% 以上）。

六、第五步：清理与长期优化

1. 清理无效 SQL Profile（若绑定后效果不佳）

操作 ：

-- 查看需清理的Profile名称
select name as profile_name 
from dba_sql_profiles 
where sql_id = '<核心SQL_ID>';
-- 删除指定Profile
exec dbms_sqltune.drop_sql_profile(name => '<profile_name>');  # 替换为实际名称

2. 长期优化建议

1. 优化热表访问 ：

• 对引发 read by other session 的热表，新增合适索引（减少全表扫描）；
• 若表数据量大，进行分区（如按时间 / 业务分区），分散访问压力。

1. 优化统计信息收集：

• 定期检查统计信息是否过期，手动收集短时间变更大量数据的表统计信息；