sql - 减少查询的缓冲区缓存命中会导致随机性能问题

Question

我们在生产中(下面)有一个 UPDATE，它每天处理或多或少相同数量的行，但运行时却截然不同。有时查询会在 2 分钟内完成，而其他天，查询可能需要 20 分钟。根据我对 AWR 数据的分析，罪魁祸首是 I/O 等待时间，每当查询变慢时，由于物理读取增加，缓存命中率就会下降。

查询本身的概要如下:

update /*+ nologging parallel ( a 12 )  */ huge_table1 a  
set   col = 1
where  col1 > 'A'
  and    col2 < 'B'
  and    exists ( select /*+ parallel ( b 12 ) */ 1
                from   huge_table2 b
                where  b.col3 = a.col3 );

巨大的表1和巨大的表2包含大约1亿行，执行统计如下:

Day     EXECUTIONS ELAPSED_TIME_S_1EXEC CPU_TIME_S_1EXEC IOWAIT_S_1EXEC ROWS_PROCESSED_1EXEC BUFFER_GETS_1EXEC  DISK_READS_1EXEC DIRECT_WRITES_1EXEC
------- ----------- -------------------- ---------------- -------------- -------------------- ----------------- ----------------- -------------------
      1           1              133.055           69.110         23.325          2178085.000       3430367.000         90522.000           42561.000
      2           1              123.580           65.020         20.282          2179404.000       3341566.000         86614.000           38925.000
      3           1             1212.762           72.800       1105.084          1982658.000       3131695.000        268260.000           38446.000
      4           1             1085.773           59.600        996.642          1965309.000       2954480.000        200612.000           26790.000

如上所示，LIO 在每种情况下几乎保持不变，尽管由于 IO 等待时间增加，在第 3 天和第 4 天耗时增加了，如果我的假设是正确的，这是由 PIO 增加引起的。根据 Tom Kyte 的说法，调整的重点应该是减少 LIO 而不是 PIO，随着 LIO 的减少，PIO 也会减少。但是在这种情况下，LIO 始终保持不变，而 PIO 却发生了显着变化。

我的问题 - 这里可以采用什么调整策略？

Answer 1

我会:

-> 检查两种情况的执行计划。
-> 检查 IO 子系统健康状况。
-> 监控服务器运行的时间并确保 IO sybsystem 没有被另一个进程饱和。

另外，什么样的 I/O 导致读取事件？顺序、并行、分散？...在​​这里您可以了解计划执行更新所遵循的策略...

缓冲区缓存是否正在调整大小？在这个大执行期间调整大小的小而冷的缓冲区缓存可能导致需要将块读入缓冲区缓存以更新它们。

基于你展示的数据的一些想法......请让我们知道结果!