应用峰值期Select查询被LGWR会话阻塞的解决方案咨询

当峰值时段大量写入导致SELECT等待LGWR，本质是LGWR的资源竞争或查询被卷入未完成的写入资源依赖。针对你查询历史数据的场景，可通过以下方式解决：

1. 利用一致性读规避LGWR依赖 #

Oracle的一致性读机制会通过undo段构建数据快照，无需等待LGWR将脏块写入磁盘。确保你的查询使用默认的READ COMMITTED隔离级别（或显式指定），即可自动触发该机制：

-- 会话级别设置隔离级别
ALTER SESSION SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 或在查询中添加提示（可选）
SELECT /*+ READ_CONSISTENT */ * FROM your_history_table WHERE your_condition;

这种方式下，查询会读取undo中的历史版本，完全不受当前LGWR写入操作的影响。

2. 优化LGWR性能，降低全局竞争 #

LGWR的瓶颈会引发全局资源（如redo latch）竞争，间接影响SELECT。可从以下维度优化：

• 调整redo日志配置：确保至少3组redo日志，每组大小建议512M-2G（避免频繁切换），且日志文件分布在独立物理磁盘上，减少IO冲突。
• 优化相关参数：LOG_BUFFER不要设置过大（建议100M-500M），避免LGWR单次写入压力过大；调整LOG_CHECKPOINT_TIMEOUT和LOG_CHECKPOINT_INTERVAL，避免检查点操作过于频繁。
• 拆分大事务：将批量插入/更新拆分为多个小事务提交，减少LGWR单次写入的数据量，降低latch竞争概率。

3. 隔离历史数据与活跃写入 #

• 迁移历史数据到单独的表空间或磁盘：将不再频繁修改的历史数据移至独立存储，与活跃写入的表物理隔离，避免查询历史数据时卷入活跃数据的LGWR写入竞争。
• 分区历史表：按时间对历史表分区，查询时仅访问目标分区，减少扫描的块数量，降低与LGWR资源冲突的可能性。

4. 使用只读物化视图或只读表 #

• 创建历史数据的物化视图：在非峰值时段定期刷新物化视图，让SELECT直接查询物化视图。物化视图的数据是静态快照，不受原表写入操作影响，自然不会等待LGWR。
• 标记历史表为只读：如果历史数据不再被修改，执行ALTER TABLE your_history_table READ ONLY;，Oracle会对只读表做锁和latch优化，彻底避免写入相关的资源竞争。

5. 定位具体等待事件，精准优化 #

先确认具体的等待事件，而非仅依赖阻塞会话信息：

SELECT event, COUNT(*)
FROM v$session_wait
WHERE blocking_session = (SELECT pid FROM v$process WHERE program LIKE '%LGWR%')
GROUP BY event;

比如log file sync通常是提交等待LGWR写入redo，需优化事务提交频率；log file parallel write是LGWR自身IO瓶颈，需优化存储性能。

内容的提问来源于stack exchange，提问作者Lokesh