Oracle 当该段有未提交的脏缓冲区时，直接路径读取检查点

嗨，AskTom!

我有一个关于Oracle内部的问题:

我知道，对于并行查询或FTS/快速全索引扫描，当oracle选择直接路径读取时，整个段将通过传递缓冲区缓存从磁盘读取到我们的uga中，对象检查点是在它之前执行的，所以属于所讨论的段的脏缓冲区对我们的会话是可见的。
我的问题是，当该段有未提交的脏缓冲区时会发生什么; 我想这些会被复制到磁盘上。oracle如何知道存在未提交的数据，它应该看到的价值是什么？
oracle是否检查pga中的每个itl的每个块读取，并在需要时返回缓冲区缓存以构建一致的版本？

非常感谢，
哈维

“oracle是否会检查pga中的每个itl读取的每个块，并在需要时返回缓冲区缓存以构建一致的版本？”

我们不一定要回到缓冲区缓存，但是是的，我们必须为您构建一个一致的视图，例如

--
-- session 1
--
SQL> create table t tablespace demo
  2  as select * from dba_objects;

Table created.

SQL>
SQL> alter system checkpoint;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> update t
  2  set owner = lower(owner)
  3  where rownum <= 10000;

10000 rows updated.
复制

因此，我们清除了所有内容，现在我们在缓冲区缓存中弹出了10,000未提交的行。让我们通过另一个会话进行连接，为了确保我们正在进行直接读取，我们将设置一个参数

--
-- session 1
--
SQL> alter session set "_serial_direct_read"=always;

Session altered.

SQL>
SQL> select max(created) from t;

MAX(CREAT
---------
17-OCT-17
复制

现在让我们看看那届会议上发生了什么

SQL> @sessev

EVENT                                        TOTAL_WAITS TOTAL_TIMEOUTS       SECS PCT
-------------------------------------------- ----------- -------------- ---------- -------------
db file scattered read                                 1              0          0 00.00%
events in waitclass Other                             28              1          0 00.00%
direct path read                                      10              0          0 00.00%
enq: KO - fast object checkpoint                       1              0        .01 04.55%
Disk file operations I/O                               4              0        .02 09.09%
db file sequential read                              116              0        .07 31.82%
CPU                                                                            .12 54.55%
复制

所以我们可以看到我们按照预期做了一个对象检查点。现在让我们看一下会话统计信息

SQL> @mystat
Enter value for statname: undo

NAME                                                              VALUE
------------------------------------------------------------ ----------
DBWR undo block writes                                                0
undo block recovery disk reads skipped                                0
undo change vector size                                             548
transaction tables consistent reads - undo records applied            0
data blocks consistent reads - undo records applied                 539
rollback changes - undo records applied                               0
auto extends on undo tablespace                                       0
total number of undo segments dropped                                 0
global undo segment hints helped                                      0
global undo segment hints were stale                                  0
local undo segment hints helped                                       0
local undo segment hints were stale                                   0
undo segment header was pinned                                        0
IMU undo retention flush                                              0
IMU undo allocation size                                              0
IM populate undo segheader rollback                                   0
IM populate undo records applied                                      0
IM repopulate undo segheader rollback                                 0
IM repopulate undo records applied                                    0
IM scan CUs undo records applied                                      0
IM ADG corrupt undo blocks seen                                       0
IM ADG inv pdb due to corrupt undo                                    0
IM ADG inv all due to corrupt undo                                    0
SMON posted for undo segment recovery                                 0
SMON posted for undo segment shrink                                   0
复制

你可以看到我们不得不应用一些撤销记录。