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

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里的运算。

先看一条语句及其指标：

select COUNT(*) from t;

Plan hash value: 2966233522

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

  1  CPU used by this session
           1  CPU used when call started
           3  DB time
       18375  Effective IO time
          20  Number of read IOs issued
          41  Requests to/from client
          41  SQL*Net roundtrips to/from client
          29 buffer is not pinned count
         571  bytes received via SQL*Net from client
       81791  bytes sent via SQL*Net to client
          14  calls to get snapshot scn: kcmgss
           5  calls to kcmgcs
    11649024  cell physical IO interconnect bytes
           6  cluster key scan block gets
           4  cluster key scans
        1463  consistent gets
        1421  consistent gets direct
          18  consistent gets examination
          18  consistent gets examination (fastpath)
          42  consistent gets from cache
          24  consistent gets pin
          23  consistent gets pin (fastpath)
           1  enqueue releases
           1  enqueue requests
          13  execute count
       32309 file io wait time
           1  free buffer requested
           8  index fetch by key
           4  index range scans
      344064  logical read bytes from cache
        1441  no work - consistent read gets
          65  non-idle wait count
           2  non-idle wait time
          13  opened cursors cumulative
           1  opened cursors current
           1  parse count (hard)
          12  parse count (total)
          21  physical read IO requests
    11649024  physical read bytes
          21  physical read total IO requests
    11649024  physical read total bytes
          11  physical read total multi block requests
        1422  physical reads
           1  physical reads cache
        1421  physical reads direct
          70  recursive calls
           6 session cursor cache count
           1 session cursor cache hits
        1463 session logical reads
           4  sorts (memory)
        2001  sorts (rows)
           3 table fetch by rowid
        1428 table scan blocks gotten
       73617 table scan disk non-IMC rows gotten
       73617 table scan rows gotten
           1 table scans (direct read)
           1 table scans (long tables)
           1 table scans (short tables)
           2  user I/O wait time
          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里读构造一致性块）

dingjun123@ORCLPDB> update t set data_object_id=null;

73216 rows updated.

Elapsed: 00:00:00.76

Execution Plan
----------------------------------------------------------
Plan hash value: 931696821

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 73216 | 143K| 397 (1)| 00:00:01 |
| 1 | UPDATE | T | | | | |
| 2 | TABLE ACCESS FULL| T | 73216 | 143K| 397 (1)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         55  recursive calls
     168861  db block gets
      76238  consistent gets
       3214  physical reads
   20404340  redo size
        494  bytes sent via SQL*Net to client
        926  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      73216 rows processed

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

1 row selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 397 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 73216 | 397 (1)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1425  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1 rows processed

session2:产生大量一致性读，可以看到一致性读主要来源于undo:consistent gets examination

这里使用"_serial_direct_path"=always,所以有consistent gets direct

select count(*)
  2* from t;

  COUNT(*)
----------
     73216

Explain Plan
-----------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 397 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 73216 | 397 (1)| 00:00:01 |
-------------------------------------------------------------------

Statistics
-----------------------------------------------------------
               9  CPU used by this session
               9  CPU used when call started
              13  DB time
             422  Effective IO time
              20  Number of read IOs issued
              42  Requests to/from client
              41  SQL*Net roundtrips to/from client
            1421  active txn count during cleanout
               1  application wait time
               2 buffer is not pinned count
             571  bytes received via SQL*Net from client
           81813  bytes sent via SQL*Net to client
               2  calls to get snapshot scn: kcmgss
               4  calls to kcmgcs
        11640832  cell physical IO interconnect bytes
            1421  cleanout - number of ktugct calls
            1421  cleanouts and rollbacks - consistent read gets
           82716  consistent gets
            1421  consistent gets direct
           81293  consistent gets examination
           81293  consistent gets examination (fastpath)
           81295  consistent gets from cache
               2  consistent gets pin
               2  consistent gets pin (fastpath)
           79872 data blocks consistent reads - undo records applied
               4  enqueue conversions
               2  enqueue releases
               2  enqueue requests
               1  enqueue waits
               2  execute count
           94000 file io wait time
             786  heap block compress
            1421  immediate (CR) block cleanout applications
       665968640  logical read bytes from cache
              52  non-idle wait count
               1  non-idle wait time
               2  opened cursors cumulative
               1  opened cursors current
               2  parse count (total)
              20  physical read IO requests
        11640832  physical read bytes
              20  physical read total IO requests
        11640832  physical read total bytes
              11  physical read total multi block requests
            1421  physical reads
            1421  physical reads direct
             117  process last non-idle time
               1 session cursor cache count
           82716 session logical reads
               1  sorts (memory)
            1974  sorts (rows)
            1421 table scan blocks gotten
           73216 table scan disk non-IMC rows gotten
           73216 table scan rows gotten
               1 table scans (direct read)
               1 table scans (long tables)
              42  user calls