面试宝典:Oracle数据库cell list of blocks physical read等待事件处理过程

最新推荐文章于 2025-08-12 12:17:16 发布

原创最新推荐文章于 2025-08-12 12:17:16 发布 · 958 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

Oracle 数据库 “cell list of blocks physical read” 等待事件深度解析

一、等待事件本质与原理

核心定义

“cell list of blocks physical read” 是 Exadata特有的等待事件，发生在存储节点（Cell Server）处理数据库发起的特定块列表请求时。与传统全表扫描不同，该事件表示数据库精确指定了需要读取的物理块列表（如索引访问后的ROWID查找），存储节点直接定位并返回这些块。

工作原理

关键特性

精确块定位：基于ROWID或块地址的直接访问
存储索引加速：利用Exadata存储索引过滤无效块
网络高效传输：仅传输请求的块（而非完整区）
与智能扫描关系：
- 智能扫描：处理全扫描+谓词下推
- 块列表读：处理索引访问后的行定位

二、产生过程与典型场景

1. 请求生命周期

SQL执行：索引扫描获取ROWID列表
生成块列表：将ROWID转换为物理块地址
发送到存储节点：通过InfiniBand传输块列表
存储节点处理：
- 检查存储索引（跳过未修改/空块）
- 从磁盘或闪存读取有效块
返回数据：仅传输实际请求的块

2. 典型高发场景

场景	SQL操作	存储行为
索引范围扫描	`SELECT * FROM t WHERE id=100`	通过索引ROWID定位具体块
嵌套循环连接	小表驱动大表连接	反复请求大表的特定块
分区表局部访问	查询单个分区	读取分区内离散块
DML后读修改块	UPDATE后立即SELECT相同行	读取刚修改的块

三、根本原因分析

1. 存储层问题

问题类型	检测指标	影响
物理读延迟高	`cell physical IO latency` > 10ms	所有块读取操作变慢
存储索引失效	`storage index - number of useless` 持续增长	无法跳过无效块
闪存缓存命中低	`flash cache read hits` < 70%	增加磁盘IO压力
InfiniBand拥塞	`v$cell_network.retransmits` > 5%	数据传输延迟

2. 数据库设计问题

索引效率低：

SELECT index_name, clustering_factor 
FROM dba_indexes 
WHERE clustering_factor / (SELECT blocks FROM dba_tables WHERE table_name='T') > 50; -- 聚簇因子差

频繁小范围请求：

-- 检查高离散读SQL
SELECT sql_id, executions, disk_reads, sql_text
FROM v$sql 
WHERE disk_reads/executions > 1000; -- 每次执行超过1000次物理读

3. 配置问题

存储索引未启用：

SHOW PARAMETER _kcfis_storageidx_enabled -- 需为TRUE

闪存缓存过小：

SELECT * FROM v$cell_flashcache; -- 关注free_percent

四、详细排查流程

步骤1：确认等待事件特征

-- 系统级统计
SELECT event, total_waits, time_waited_micro,
       ROUND(time_waited_micro/NULLIF(total_waits,0)) avg_wait_us
FROM v$system_event 
WHERE event = 'cell list of blocks physical read';

-- 关联SQL与会话
SELECT s.sid, s.sql_id, p.pdb_name, 
       t.table_name, i.index_name
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
LEFT JOIN dba_indexes i ON q.sql_text LIKE '%'||i.index_name||'%'
LEFT JOIN dba_tables t ON i.table_name = t.table_name
WHERE s.event = 'cell list of blocks physical read';

步骤2：分析存储节点性能

-- 存储节点I/O指标
SELECT cell_name, 
       ROUND(physical_read_requests) reads,
       ROUND(physical_read_bytes/1048576) read_mb,
       ROUND(avg_read_time_ms,2) avg_ms
FROM v$cell_iostat_detail
WHERE metric_name = 'cell list of blocks physical read';

-- 存储索引效率
SELECT name, value 
FROM v$sysstat 
WHERE name LIKE '%storage index%' 
AND name NOT LIKE '%useless%';

关键指标：

avg_read_time_ms > 15 需优化
storage index - number of useless > 1000/s 表示存储索引失效

步骤3：检查对象特征

-- 定位高等待块所属对象
SELECT file_id, block_id, tablespace_name, owner, segment_name
FROM dba_extents 
WHERE file_id = &P1 
  AND &P2 BETWEEN block_id AND block_id + blocks - 1; -- 从v$session_wait获取P1/P2

-- 检查表/索引状态
SELECT table_name, blocks, num_rows, chain_cnt 
FROM dba_tables WHERE table_name='&TABLE';

SELECT index_name, clustering_factor, distinct_keys 
FROM dba_indexes WHERE table_name='&TABLE';

五、解决方案与优化建议

1. 存储层优化

启用存储索引：

ALTER SYSTEM SET "_kcfis_storageidx_enabled"=TRUE SCOPE=SPFILE;

扩容闪存缓存：

# CellCLI配置
CellCLI> alter flashcache all size = 2T

调整InfiniBand：
```
# 检查网络配置
ibcheckerrors -v
```

2. 数据库优化

重建聚簇因子差的索引：
```
ALTER INDEX &INDEX_NAME REBUILD;
```
表重组：
```
ALTER TABLE &TABLE_NAME MOVE ONLINE;
```

批量处理优化：

-- 将多次小请求合并为单次批量请求
SELECT /*+ INDEX_RS_ASC(t) BATCHING(t) */ * 
FROM t WHERE id IN (1,2,3...1000);

3. SQL优化

避免嵌套循环：

-- 使用哈希连接替代
SELECT /*+ USE_HASH(t1 t2) */ * 
FROM t1 JOIN t2 ON t1.id=t2.id;

减少回表查询：

-- 使用覆盖索引
CREATE INDEX idx_cover ON t(id, name); 
SELECT id, name FROM t WHERE id>100; -- 无需回表

六、高级诊断工具

1. ExaWatcher分析

# 收集存储节点性能数据
exawatcher -d /tmp -duration 3600
# 分析输出文件中的"PhysicalRead_BlockList"指标

2. SQL Monitor报告

-- 生成实时报告
SET LONG 1000000
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => '&SQL_ID',
  type => 'TEXT') 
FROM dual;

关注点：

Cell Offload 标签
Table Access By Rowid 操作耗时

3. 存储节点跟踪

-- 启用Cell跟踪
ALTER SYSTEM SET EVENTS 'trace[cell_client.*] disk=medium';
-- 重现问题后检查cell trace文件

七、优化案例参考

问题场景：

嵌套循环连接导致每秒20万次"cell list of blocks physical read"

优化步骤：

识别问题SQL：

SELECT /*+ LEADING(dept) USE_NL(emp) */ * 
FROM dept, emp 
WHERE dept.id = emp.dept_id;

优化执行计划：

SELECT /*+ USE_HASH(emp) */ * 
FROM dept, emp 
WHERE dept.id = emp.dept_id;

结果对比：

指标 优化前 优化后
等待事件次数/秒 200,000 0
查询响应时间 8.2秒 0.3秒
存储节点IOPS 25,000 500

指标	优化前	优化后
等待事件次数/秒	200,000	0
查询响应时间	8.2秒	0.3秒
存储节点IOPS	25,000	500

八、预防性维护措施

1. 实时监控

-- 创建阻塞监控
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_CELL_BLOCK_READS',
    job_type => 'PLSQL_BLOCK',
    job_action => '
      INSERT INTO cell_waits_log
      SELECT SYSDATE, SUM(total_waits) waits
      FROM v$system_event 
      WHERE event = ''cell list of blocks physical read''',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY');
END;

2. 定期健康检查

-- 存储索引健康报告
SELECT cell_name,
       SUM(CASE metric_name 
          WHEN 'storage index - number of useful' THEN value END) useful,
       SUM(CASE metric_name 
          WHEN 'storage index - number of useless' THEN value END) useless
FROM v$cell_metric
WHERE metric_name LIKE '%storage index%'
GROUP BY cell_name;

优化黄金法则：

聚簇因子为王：保持clustering_factor < 表块数*0.8
批量处理优先：单次请求 >100个块效率最高
存储索引必开：确保_kcfis_storageidx_enabled=TRUE
当平均等待时间 > 10ms 或事件占比 > 5% 时需立即优化。典型优化后性能提升可达10-100倍。

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