我职业生涯中最特殊的Oracle情况-Oracle将记录从直接读取到的块的方式更改为根本不在I/O等待事件中记录

问候，

我有一个非常令人困惑的情况，Oracle改变了它记录块读取的方式。上周，在I/O等待事件中根本没有计算块读取; 本周它开始将其添加到 “直接读取” 等待事件中。这是在我们的生产环境中发生的; 但是，我能够使用测试数据在我们的测试环境中重现这种情况。

我使用all_source视图创建两个测试表，直到我达到表1的120万和表2的400万:
Table1 ( 1.2 Mil记录) 创建表table1作为select * 从dba_source其中rownum;
Table2 ( 4 Mil记录) 创建表table2作为select * 从dba_source;
在表1 (所有者) 上创建索引t1_pk;
在表2上创建索引t2_pk (所有者，行);
exec dbms_stats.gather_schema_stats('JOHN');

然后我运行这个select语句120次:

select count(*) from Table1 where line=1 and owner in (select Table2.owner from Table2 where Table2.owner=Table1.owner) order by owner;
复制

在某些情况下，Oracle 19c将I/O记录在 “直接路径读取” 等待事件中，而在其他情况下，它似乎没有在任何I/O等待事件中报告。太奇怪了。


测试用例1: IOStats摘要不记录I/O，也不会在等待事件中记录:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU                                           20.2             99.6
PGA memory operation                 2,524         .1   20.27us     .3 Other
Disk file operations I/O               520          0   59.49us     .2 User I/O
db file sequential read                211          0   12.33us     .0 User I/O
Parameter File I/O                       8          0  257.00us     .0 User I/O
enq: RO - fast object reuse              2          0  784.50us     .0 Applicat
control file sequential read           209          0    5.32us     .0 System I
log file sync                            1          0     .95ms     .0 Commit
SQL*Net message to client              546          0    1.53us     .0 Network
SQL*Net more data to client             22          0   33.77us     .0 Network

SQL ordered by Gets               DB/Inst: ORACLE/stbyoracle  Snaps: 2727-2728
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - Buffer Gets   as a percentage of Total Buffer Gets
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Buffer Gets:       3,399,948
-> Captured SQL account for   98.1% of Total

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
  3,241,728         120     27,014.4   95.3       14.4  99.5     0 82mps751cqh84
Module: SQL*Plus
select count(*) from Table1 where line=1 and owner in (select Table2.owner from
Table2 where Table2.owner=Table1.owner) order by owner

IOStat by Function summary        DB/Inst: ORACLE/stbyoracle  Snaps: 2727-2728
-> 'Data' columns suffixed with M,G,T,P are in multiples of 1024
    other columns suffixed with K,M,G,T,P are in multiples of 1000
-> ordered by (Data Read + Write) desc

                 Reads:  Reqs    Data   Writes:  Reqs    Data    Waits:    Avg
Function Name      Data per sec per sec    Data per sec per sec   Count    Time
--------------- ------- ------- ------- ------- ------- ------- ------- --------
LGWR                 3M     1.5   .022M     10M     3.6   .075M     678 368.73us
Others               7M    20.0   .052M      2M     1.0   .015M     629 100.16us
Buffer Cache Re      3M    21.0   .022M      0M     0.0      0M     251  11.95us
DBWR                 0M     0.0      0M      0M     0.1      0M      18    .00ns
Direct Reads         0M  1288.7      0M      0M     0.0      0M       0
Direct Writes        0M     0.0      0M      0M     0.0      0M       1    .00ns
RMAN                 0M     0.5      0M      0M     0.0      0M       0
TOTAL:              13M  1331.7   .097M     12M     4.8    .09M    1577 200.38us
                          ------------------------------------------------------

TEST CASE 2: this started to occur after a database restart:  
Now the I/O is recorded in IOStats and wait events, direct path read:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU                                           23.7             98.4
direct path read                    22,134        4.8  217.85us   20.0 User I/O
PGA memory operation                 2,088         .1   28.85us     .2 Other
Disk file operations I/O             1,587          0   18.20us     .1 User I/O
SQL*Net message to client            1,861          0  817.30ns     .0 Network
db file sequential read                 97          0   13.43us     .0 User I/O
log file sync                            2          0  521.50us     .0 Commit
control file sequential read           174          0    5.08us     .0 System I
enq: RO - fast object reuse              1          0  849.00us     .0 Applicat
reliable message                         1          0  194.00us     .0 Other

SQL ordered by Gets               DB/Inst: ORACLE/stbyoracle  Snaps: 2711-2712
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - Buffer Gets   as a percentage of Total Buffer Gets
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Buffer Gets:       3,351,947
-> Captured SQL account for  101.1% of Total

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
  3,214,121         119     27,009.4   95.9       16.9  99.7  28.6 82mps751cqh84
Module: sqlplus@va1idevlclu0002 (TNS V1-V3)
select count(*) from Table1 where line=1 and owner in (select Table2.owner from
Table2 where Table2.owner=Table1.owner) order by owner


IOStat by Function summary        DB/Inst: ORACLE/stbyoracle  Snaps: 2711-2712
-> 'Data' columns suffixed with M,G,T,P are in multiples of 1024
    other columns suffixed with K,M,G,T,P are in multiples of 1000
-> ordered by (Data Read + Write) desc

                 Reads:  Reqs    Data   Writes:  Reqs    Data    Waits:    Avg
Function Name      Data per sec per sec    Data per sec per sec   Count    Time
--------------- ------- ------- ------- ------- ------- ------- ------- --------
Direct Reads      20.5G  1269.6 124.097      0M     0.0      0M   22.1K    .00ns
LGWR                 4M     1.8   .024M     10M     1.7   .059M     578 252.60us
Others               9M     6.4   .053M      2M     0.9   .012M     726  97.80us
Buffer Cache Re      0M     5.2      0M      0M     0.0      0M      77  12.99us
DBWR                 0M     0.0      0M      0M     0.1      0M      15    .00ns
RMAN                 0M     0.1      0M      0M     0.0      0M       0
TOTAL:            20.5G  1283.1 124.174     12M     2.6   .071M   23.5K   9.26us
                          ------------------------------------------------------
复制

这种不寻常的情况可能是什么原因？查询响应时间相同。执行计划是一样的。表中的数据量是相同的。为什么Oracle报告它在测试用例1中读取了13 MB的数据，而在测试用例2中读取了20.5 GB的数据？

我还想知道Oracle使用哪些因素来确定缓冲区读取应添加到 “直接读取” 中。我回想起Oracle曾经在 “db文件分散读取” 等待事件中记录全表扫描读取。但是，在第一个测试用例中，出于某种原因，Oracle并未将其添加到任何I/O等待事件中。

谢谢你帮助解决这个不寻常的案子。

约翰

我希望它将与这篇博客文章的内容有关 (这是一篇exadata文章，但许多原则是相同的)

https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-1

简而言之-有很多因素会影响直接读取的决定 * 包括 * 当前有多少表被认为已经在缓冲区缓存中。

因此，偶尔看到直接阅读的 “消失” 并不是完全出乎意料的。