面试宝典:Oracle数据库free buffer waits等待事件处理过程

Oracle 数据库 free buffer waits 等待事件深度解析

1. 等待事件本质

• free buffer waits：
  当数据库进程需要将数据块读入Buffer Cache时，无法找到空闲缓冲区(free buffer) 而发生的等待。表明Buffer Cache无法及时提供可用空间给新数据块使用。
• 关键特性：
  • 直接反映Buffer Cache的I/O压力
  • 通常伴随物理读(physical reads)增加
  • 高频率出现表明内存配置或SQL效率问题
• 与相关等待的区别：

  等待事件	触发原因	解决方向
  free buffer waits	Buffer Cache空间不足	内存优化/SQL调优
  db file sequential read	单块物理读等待	I/O优化/索引调整
  db file scattered read	多块物理读等待	全表扫描优化

---

2. 产生过程详解

1. 会话请求数据块：
   会话执行SQL需要读取不在内存中的数据块
2. Buffer Cache检查：
   在LRU列表上查找空闲缓冲区
3. 空间不足触发等待：
   当所有缓冲区都在使用中（脏块或正在使用）
4. 唤醒DBWR进程：
   会话通知DBWR进程写入脏块
5. DBWR写脏块：
   将修改过的数据块写入磁盘
6. 缓冲区释放：
   写入后的缓冲区被标记为空闲
7. 分配缓冲区：
   会话获得空闲缓冲区
8. 物理读取：
   将数据块从磁盘读入缓冲区

---

3. 高频场景

全表扫描大表

-- 扫描10GB表
SELECT * FROM sales_history WHERE sale_date > SYSDATE-30;

批量DML操作

-- 更新百万行数据
UPDATE transactions SET status='P' WHERE created < SYSDATE-90;

索引重建

-- 重建大索引
ALTER INDEX sales_idx REBUILD ONLINE;

数据加载

-- SQL*Loader直接路径加载
sqlldr user/pwd control=load.ctl direct=true

备份操作

-- RMAN热备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

---

4. 根本原因分类

内存配置问题

问题类型	典型案例
Buffer Cache过小	SGA_TARGET=8G, db_cache_size=1G → 处理大型数据集不足
内存分配失衡	PGA_AGGREGATE_TARGET=10G, db_cache_size=2G → PGA过大挤压Buffer Cache
多池配置不当	shared_pool_size=4G, db_cache_size=1G → 共享池过大

SQL效率问题

• 全表扫描泛滥：

  SELECT * FROM 100GB_table;  -- 未使用索引
  

• 低效连接操作：

  SELECT * FROM t1, t2 WHERE t1.id=t2.id;  -- 未优化的笛卡尔积
  

• 大结果集排序：

  SELECT * FROM orders ORDER BY order_date;  -- 10M行排序
  

I/O系统问题

• 存储性能不足：
  机械硬盘(HDD)无法满足高IOPS需求
• REDO日志瓶颈：
  REDO日志组过少或大小不足
• DBWR配置不当：

  SHOW PARAMETER db_writer_processes; -- 单CPU架构设为1
  

对象设计问题

• 高PCTFREE设置：

  CREATE TABLE logs (...) PCTFREE 40;  -- 空间利用率低
  

• 频繁更新大对象：
  LOB列频繁修改 → 产生大量脏块

---

5. 深度排查流程

步骤1：系统级诊断

-- 确认等待强度
SELECT event, total_waits, time_waited_micro
FROM v$system_event 
WHERE event = 'free buffer waits';

-- 关联内存统计
SELECT 
  (SELECT value FROM v$sysstat WHERE name='physical reads') phys_reads,
  (SELECT value FROM v$sysstat WHERE name='free buffer requested') free_req
FROM dual;

• 严重性阈值：
  time_waited_micro > 60秒/分钟 + phys_reads > 5000/秒

步骤2：内存配置分析

-- SGA配置检查
SELECT * FROM v$sgnfo;

-- Buffer Cache分配
SELECT 
    component,
    ROUND(current_size/1024/1024) size_mb
FROM v$sga_dynamic_components
WHERE component IN ('DEFAULT buffer cache','KEEP buffer cache');

-- 内存建议
SELECT * FROM v$db_cache_advice;

步骤3：定位热点SQL

-- 高物理读SQL
SELECT 
    sql_id,
    sql_text,
    disk_reads,
    buffer_gets,
    ROUND(disk_reads/buffer_gets,4) read_ratio
FROM v$sql
WHERE disk_reads > 100000
ORDER BY disk_reads DESC;

-- ASH分析
SELECT 
    sql_id,
    COUNT(*) waits,
    current_obj#
FROM v$active_session_history
WHERE event = 'free buffer waits'
AND sample_time > SYSDATE - 10/1440
GROUP BY sql_id, current_obj#
ORDER BY waits DESC;

步骤4：对象分析

-- 高物理读对象
SELECT 
    owner,
    object_name,
    object_type,
    logical_reads,
    physical_reads
FROM v$segment_statistics
WHERE physical_reads > 1000000
ORDER BY physical_reads DESC;

-- 表空间I/O
SELECT 
    ts.name tablespace,
    phyrds physical_reads,
    ROUND(phyrds/(phyblkrd+1),2) avg_reads_per_block
FROM v$filestat fs
JOIN v$tablespace ts ON ts.ts# = fs.ts#;

步骤5：I/O系统检查

-- 文件级I/O
SELECT 
    file_name,
    phyrds,
    phywrts,
    ROUND((readtim*1000)/DECODE(phyrds,0,1,phyrds)) avg_read_ms
FROM v$filestat
JOIN dba_data_files USING (file_id);

-- DBWR性能
SELECT 
    process,
    buffers_written,
    write_complete
FROM v$bgprocess 
WHERE name LIKE 'DBW%';

步骤6：等待链分析

-- 会话级等待
SELECT 
    sid,
    sql_id,
    event,
    p1 file#,
    p2 block#,
    p3 blocks
FROM v$session
WHERE event = 'free buffer waits';

-- 阻塞会话
SELECT 
    holding_sid,
    holding_sql_id,
    COUNT(*) contention_count
FROM v$active_session_history
WHERE event = 'free buffer waits'
GROUP BY holding_sid, holding_sql_id
ORDER BY contention_count DESC;

步骤7：高级诊断

-- Buffer Cache转储
ALTER SESSION SET events 'immediate trace name buffers level 10';

-- 检查点诊断
ALTER SESSION SET events 'immediate trace name checkpoint_debug level 5';

---

6. 根治方案

紧急处置

-- 终止高负载会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 强制刷新脏页
ALTER SYSTEM CHECKPOINT;

-- 临时扩大Buffer Cache
ALTER SYSTEM SET db_cache_size=4G SCOPE=MEMORY;

内存优化

-- 永久调整Buffer Cache
ALTER SYSTEM SET db_cache_size=8G SCOPE=SPFILE;

-- 使用多缓冲池
ALTER SYSTEM SET db_keep_cache_size=2G;  -- 保留热点表
ALTER SYSTEM SET db_recycle_cache_size=1G; -- 临时对象

-- 平衡内存分配
ALTER SYSTEM SET pga_aggregate_target=4G;
ALTER SYSTEM SET sga_target=16G;

SQL优化

• 减少全表扫描：

  -- 添加缺失索引
  CREATE INDEX sales_date_idx ON sales(sale_date);
  

• 批处理优化：

  -- 原语句
  DELETE FROM log WHERE created < SYSDATE-365;
  
  -- 分批删除
  BEGIN
    LOOP
      DELETE FROM log 
      WHERE created < SYSDATE-365 
      AND ROWNUM <= 10000;
      EXIT WHEN SQL%ROWCOUNT = 0;
      COMMIT;
    END LOOP;
  END;
  

• 结果集限制：

  SELECT * FROM orders 
  WHERE order_date > SYSDATE-30 
  AND ROWNUM <= 1000;  -- 限制返回行数
  

对象优化

-- 分区大表
CREATE TABLE sales (...) 
PARTITION BY RANGE (sale_date) (
  PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD'))
);

-- 压缩表
ALTER TABLE sales_history COMPRESS FOR OLTP;

-- 调整PCTFREE
ALTER TABLE orders PCTFREE 10;  -- 原值40

I/O系统优化

-- 增加DBWR进程
ALTER SYSTEM SET db_writer_processes=4 SCOPE=SPFILE;  -- 多CPU环境

-- 使用异步I/O
ALTER SYSTEM SET disk_asynch_io=TRUE; 

-- 分离REDO日志
ALTER DATABASE ADD LOGFILE GROUP 4 '+FAST_DATA' SIZE 1G;

架构级优化

• 读写分离：
  将报表查询路由到物理备库
• 内存数据库：

  -- 18c+特性
  ALTER TABLE hot_table INMEMORY;
  

• 列式存储：

  -- 12c+ Hybrid Columnar Compression
  ALTER TABLE sales MODIFY COMPRESS FOR QUERY HIGH;
  

根治原则：
解决free buffer waits = 扩大有效缓存 + 减少物理I/O + 优化写入机制。
核心在于通过内存优化降低磁盘访问需求，这是提升性能的关键路径。

通过此方案可显著降低Buffer Cache争用，提升数据库整体吞吐量。

欢迎关注我的公众号《IT小Chen》