oracle logical reads(逻辑读)详细分析

100阅读　0评论2025-03-16　dingjun123
分类：Oracle

oracle读取数据分为物理读、逻辑读，其中大多数时候主要是逻辑读，因为热点数据存在buffer cache里。而物理读也会产生逻辑读，所以逻辑读往往成为衡量SQL性能的重要指标。

逻辑读包括了consistent read（cr读）和db block gets(current reads),其中一致性读是遇到读取的块有DML的，需要从undo里进行一致性块重构，
所以如果select读取的block被修改未提交，读取的时候cr读会增加，这也是会影响性能的，而DML呢，查找时候会进行cr读，更新时候会进行current reads。


另外注意：
1）如果select返回行很多，fetch size也会影响consistent reads,可能一个块被读取多次，因为每次fetch就要从buffer cache里获取数据。
2）逻辑读指标只会显示SGA里的操作，PGA里的比如hash join的IO操作，不会显示在逻辑读里，所以，逻辑读小的，如果是hash join之类的，不一定性能
就比逻辑读大的好，因为，隐藏了PGA里的运算。

先看一条语句及其指标：

点击(此处)折叠或打开

  1. select COUNT(*) from t;

  3. Plan hash value: 2966233522

  5. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
  6. | 0 | SELECT STATEMENT | | 1 | 397 (1)| 00:00:01 |
  7. | 1 | SORT AGGREGATE | | 1 | | |
  8. | 2 | TABLE ACCESS FULL| T | 73216 | 397 (1)| 00:00:01 |
  9. Statistics

  11.   1 CPU used by this session
  12.            1 CPU used when call started
  13.            3 DB time
  14.        18375 Effective IO time
  15.           20 Number of read IOs issued
  16.           41 Requests to/from client
  17.           41 SQL*Net roundtrips to/from client
  18.           29 buffer is not pinned count
  19.          571 bytes received via SQL*Net from client
  20.        81791 bytes sent via SQL*Net to client
  21.           14 calls to get snapshot scn: kcmgss
  22.            5 calls to kcmgcs
  23.     11649024 cell physical IO interconnect bytes
  24.            6 cluster key scan block gets
  25.            4 cluster key scans
  26.         1463 consistent gets
  27.         1421 consistent gets direct
  28.           18 consistent gets examination
  29.           18 consistent gets examination (fastpath)
  30.           42 consistent gets from cache
  31.           24 consistent gets pin
  32.           23 consistent gets pin (fastpath)
  33.            1 enqueue releases
  34.            1 enqueue requests
  35.           13 execute count
  36.        32309 file io wait time
  37.            1 free buffer requested
  38.            8 index fetch by key
  39.            4 index range scans
  40.       344064 logical read bytes from cache
  41.         1441 no work - consistent read gets
  42.           65 non-idle wait count
  43.            2 non-idle wait time
  44.           13 opened cursors cumulative
  45.            1 opened cursors current
  46.            1 parse count (hard)
  47.           12 parse count (total)
  48.           21 physical read IO requests
  49.     11649024 physical read bytes
  50.           21 physical read total IO requests
  51.     11649024 physical read total bytes
  52.           11 physical read total multi block requests
  53.         1422 physical reads
  54.            1 physical reads cache
  55.         1421 physical reads direct
  56.           70 recursive calls
  57.            6 session cursor cache count
  58.            1 session cursor cache hits
  59.         1463 session logical reads
  60.            4 sorts (memory)
  61.         2001 sorts (rows)
  62.            3 table fetch by rowid
  63.         1428 table scan blocks gotten
  64.        73617 table scan disk non-IMC rows gotten
  65.        73617 table scan rows gotten
  66.            1 table scans (direct read)
  67.            1 table scans (long tables)
  68.            1 table scans (short tables)
  69.            2 user I/O wait time
  70.           42 user calls



1. 逻辑读 (Logical Reads)
逻辑读是指从 Oracle 数据库中读取数据块到内存的过程，它并不一定涉及物理 I/O（磁盘读取），可能直接从缓冲区缓存（Buffer Cache）中获取数据块。逻辑读可以分为两类：

一致性读 (Consistent Reads)：用于提供查询时刻的一致性视图，通常是 SELECT 操作产生的。
当前读 (DB Block Gets)：用于获取当前块的{BANNED}{BANNED}最佳佳新版本，通常是 DML 操作（如 INSERT、UPDATE、DELETE）产生的。

总逻辑读可以表示为：
Logical Reads = Consistent Reads + DB Block Gets

2. 一致性读 (Consistent Reads)
一致性读是逻辑读的重要组成部分，具体表现为 Oracle 在查询时根据当前事务的 SCN（System Change Number）提供一致性视图。以下是一致性读的来源和分类：

一致性读的分类
(1) Consistent Reads From Cache
来源：
数据块已经存在于内存的 Buffer Cache 中。
不需要对数据块进行重构，直接从缓存中读取。
相关指标：
consistent gets from cache
包括 consistent gets pin 和其他缓存的逻辑读。

(2) Consistent Gets Pin
来源：
当一个数据块被固定（Pinned）在内存缓冲区中且需要访问时产生。
例如多个会话同时读取同一个数据块，或频繁访问数据块。
相关指标：
consistent gets pin：逻辑读时将块 Pin 住。
consistent gets pin (fastpath)：快速路径访问。

(3) Consistent Reads From Direct Path (Direct Path Read)
来源：
如果查询使用直接路径读（Direct Path Read），则数据块绕过 Buffer Cache，直接从磁盘读取到 PGA。
数据不会加载到 SGA 的 Buffer Cache 中，但仍会计入一致性读。
相关指标：
consistent gets direct：直接路径读取产生的一致性读。

(4) Consistent Reads From Undo (Consistent Gets Examination)
来源：
如果数据块在查询开始后被其他事务修改，Oracle 会根据 Undo 表空间中的信息重构数据块的快照。
重构的过程会产生额外的一致性读。
相关指标：
consistent gets examination
consistent gets examination (fastpath)

一致性读公式
综合一致性读的来源，我们可以用以下公式表示：
Consistent Reads = Consistent Reads From Cache + Consistent Reads Direct
Consistent Reads From Cache = Consistent Gets Pin + Consistent Reads From Undo
Consistent Reads = (Consistent Gets Pin + Consistent Reads From Undo) + Consistent Reads Direct

consistent gets = consistent gets from cache + consistent gets direct
consistent gets from cache = consistent gets pin + consistent gets examination
consistent gets examination = consistent gets examination (fastpath)

3. 当前读 (DB Block Gets)
当前读是 DML 操作（如 INSERT、UPDATE、DELETE）或某些查询（如 SELECT FOR UPDATE）需要访问数据块的{BANNED}{BANNED}最佳佳新版本时产生的逻辑读。


当前读的来源
直接从缓冲区缓存中读取当前版本：
如果数据块已经加载到 Buffer Cache 并且是{BANNED}{BANNED}最佳佳新版本，Oracle 会直接读取该块。
从磁盘读取当前版本：
如果数据块不在内存中或是旧版本，Oracle 会从磁盘中读取{BANNED}{BANNED}最佳佳新版本的数据块，并更新到 Buffer Cache 中。
当前读的作用
DML 写入操作：
在修改数据之前，Oracle 必须读取数据块的当前版本。
比如 UPDATE 或 DELETE，需要先读取当前块，再修改它。
SELECT FOR UPDATE：
查询需要锁定某些行时，会读取当前块。
当前读与一致性读的区别
一致性读：用于查询，提供的是数据块的快照版本。
当前读：用于修改或锁定数据，提供的是数据块的{BANNED}{BANNED}最佳佳新版本。
逻辑读公式的完整版本
结合一致性读和当前读的来源，逻辑读公式可以表示为：

Logical Reads = Consistent Reads + DB Block Gets
Consistent Reads = (Consistent Reads From Cache + Consistent Reads Direct)
Consistent Reads From Cache = (Consistent Gets Pin + Consistent Reads From Undo)
logical reads = consistent gets + db block gets
consistent gets = (consistent gets from cache + consistent gets direct)
consistent gets from cache = (consistent gets pin + consistent gets examination)
consistent gets examination = consistent gets examination (fastpath)

4. Direct Path Read 的特点
当查询使用 Direct Path Read 时，会绕过 SGA 的 Buffer Cache，直接将数据块从磁盘读取到 PGA 中。尽管数据块未经过 Buffer Cache，但仍会产生逻辑读（一致性读）。以下是 Direct Path Read 的特点：

不会加载到 SGA 的 Buffer Cache 中：
数据块直接读取到 PGA，减少了对 Buffer Cache 的压力。
产生物理读和逻辑读：
从磁盘读取数据块产生物理读。
同时记录一致性读（consistent gets direct）。
Direct Path Read 适用于以下场景：


全表扫描（如大表扫描）。
并行查询（Parallel Query）。
表或索引的直接路径访问。

5. 总结公式
Logical Reads = Consistent Reads + DB Block Gets
Consistent Reads = (Consistent Reads From Cache + Consistent Reads Direct)
Consistent Reads From Cache = (Consistent Gets Pin + Consistent Reads From Undo)
Consistent Reads Direct = Direct Path Read 的一致性读
Consistent Reads From Undo = Consistent Gets Examination = Consistent Gets Examination (Fastpath)
Consistent Gets Pin = Consistent Gets Pin (Fastpath)

物理读与逻辑读的关系
Buffer Cache 读取：物理读（physical reads cache）会加载到 Buffer Cache，随后产生逻辑读。
Direct Path Read：物理读（physical reads direct）直接将数据加载到 PGA，同时产生逻辑读。

DML对逻辑读影响
session1有修改：
修改时候会先查找数据产生consistent gets甚至物理读，物理读会产生逻辑读,然后修改本身产生当前读db block gets
同一个会话的后续普通select不会产生当前读，但是如果事务未提交或回滚，其他事务读相同数据，会产生更多的逻辑读（主要从undo里读构造一致性块）



点击(此处)折叠或打开

  1. dingjun123@ORCLPDB> update t set data_object_id=null;

  3. 73216 rows updated.

  5. Elapsed: 00:00:00.76

  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 931696821

  11. ---------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. ---------------------------------------------------------------------------
  14. | 0 | UPDATE STATEMENT | | 73216 | 143K| 397 (1)| 00:00:01 |
  15. | 1 | UPDATE | T | | | | |
  16. | 2 | TABLE ACCESS FULL| T | 73216 | 143K| 397 (1)| 00:00:01 |
  17. ---------------------------------------------------------------------------


  20. Statistics
  21. ----------------------------------------------------------
  22.          55 recursive calls
  23.      168861 db block gets
  24.       76238 consistent gets
  25.        3214 physical reads
  26.    20404340 redo size
  27.         494 bytes sent via SQL*Net to client
  28.         926 bytes received via SQL*Net from client
  29.           2 SQL*Net roundtrips to/from client
  30.           3 sorts (memory)
  31.           0 sorts (disk)
  32.       73216 rows processed

  34. dingjun123@ORCLPDB> select count(*) from t;

  36. 1 row selected.

  38. Elapsed: 00:00:00.00

  40. Execution Plan
  41. ----------------------------------------------------------
  42. Plan hash value: 2966233522

  44. -------------------------------------------------------------------
  45. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
  46. -------------------------------------------------------------------
  47. | 0 | SELECT STATEMENT | | 1 | 397 (1)| 00:00:01 |
  48. | 1 | SORT AGGREGATE | | 1 | | |
  49. | 2 | TABLE ACCESS FULL| T | 73216 | 397 (1)| 00:00:01 |
  50. -------------------------------------------------------------------


  53. Statistics
  54. ----------------------------------------------------------
  55.           0 recursive calls
  56.           0 db block gets
  57.        1425 consistent gets
  58.           0 physical reads
  59.           0 redo size
  60.         552 bytes sent via SQL*Net to client
  61.         384 bytes received via SQL*Net from client
  62.           2 SQL*Net roundtrips to/from client
  63.           0 sorts (memory)
  64.           0 sorts (disk)
  65.           1 rows processed

session2:产生大量一致性读，可以看到一致性读主要来源于undo:consistent gets examination
这里使用"_serial_direct_path"=always,所以有consistent gets direct


点击(此处)折叠或打开

  1. select count(*)
  2.   2* from t;

  4.   COUNT(*)
  5. ----------
  6.      73216

  8. Explain Plan
  9. -----------------------------------------------------------

  11. PLAN_TABLE_OUTPUT
  12. --------------------------------------------------------------------------------
  13. Plan hash value: 2966233522

  15. -------------------------------------------------------------------
  16. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
  17. -------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 1 | 397 (1)| 00:00:01 |
  19. | 1 | SORT AGGREGATE | | 1 | | |
  20. | 2 | TABLE ACCESS FULL| T | 73216 | 397 (1)| 00:00:01 |
  21. -------------------------------------------------------------------

  23. Statistics
  24. -----------------------------------------------------------
  25.                9 CPU used by this session
  26.                9 CPU used when call started
  27.               13 DB time
  28.              422 Effective IO time
  29.               20 Number of read IOs issued
  30.               42 Requests to/from client
  31.               41 SQL*Net roundtrips to/from client
  32.             1421 active txn count during cleanout
  33.                1 application wait time
  34.                2 buffer is not pinned count
  35.              571 bytes received via SQL*Net from client
  36.            81813 bytes sent via SQL*Net to client
  37.                2 calls to get snapshot scn: kcmgss
  38.                4 calls to kcmgcs
  39.         11640832 cell physical IO interconnect bytes
  40.             1421 cleanout - number of ktugct calls
  41.             1421 cleanouts and rollbacks - consistent read gets
  42.            82716 consistent gets
  43.             1421 consistent gets direct
  44.            81293 consistent gets examination
  45.            81293 consistent gets examination (fastpath)
  46.            81295 consistent gets from cache
  47.                2 consistent gets pin
  48.                2 consistent gets pin (fastpath)
  49.            79872 data blocks consistent reads - undo records applied
  50.                4 enqueue conversions
  51.                2 enqueue releases
  52.                2 enqueue requests
  53.                1 enqueue waits
  54.                2 execute count
  55.            94000 file io wait time
  56.              786 heap block compress
  57.             1421 immediate (CR) block cleanout applications
  58.        665968640 logical read bytes from cache
  59.               52 non-idle wait count
  60.                1 non-idle wait time
  61.                2 opened cursors cumulative
  62.                1 opened cursors current
  63.                2 parse count (total)
  64.               20 physical read IO requests
  65.         11640832 physical read bytes
  66.               20 physical read total IO requests
  67.         11640832 physical read total bytes
  68.               11 physical read total multi block requests
  69.             1421 physical reads
  70.             1421 physical reads direct
  71.              117 process last non-idle time
  72.                1 session cursor cache count
  73.            82716 session logical reads
  74.                1 sorts (memory)
  75.             1974 sorts (rows)
  76.             1421 table scan blocks gotten
  77.            73216 table scan disk non-IMC rows gotten
  78.            73216 table scan rows gotten
  79.                1 table scans (direct read)
  80.                1 table scans (long tables)
  81.               42 user calls




上一篇：oracle,mysql,pg的having转换为where规则
下一篇：oracle,mysql范围越界的选择率估算