Oracle 为什么索引唯一扫描可以使用高缓冲区gets？

从11g升级到12c后，该过程运行了几个小时，我们从AWR中识别出一个查询突出，它运行了7百万的时间，它过去每次运行只需要3个缓冲区，但现在它采取了300，它是哈希分区索引的哈希分区表。

这是查询和查询计划。

SQL_ID gpajb2bnac80b
--------------------
SELECT 1 FROM PAY_RUN_RESULT_VALUES WHERE RUN_RESULT_ID = :B2 AND
INPUT_VALUE_ID = :B1

Plan hash value: 3686252633

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |       |       |     2 (100)|          |    |  |
|   1 |  PARTITION HASH SINGLE|                          |     1 |    12 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   INDEX UNIQUE SCAN   | PAY_RUN_RESULT_VALUES_PK |     1 |    12 |     2   (0)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------

select sql_id,PLAN_HASH_VALUE plan, to_char(BEGIN_INTERVAL_TIME,'yyyymmdd') rundate,
sum(EXECUTIONS_DELTA) as exec,
to_char(min(BEGIN_INTERVAL_TIME),'hh24mi') start_time,to_char(max(end_intervAL_TIME),'hh24mi') end_time,
sum(BUFFER_GETS_DELTA) as buffer,
sum(physical_read_bytes_delta) as phy_read,
round(sum(cpu_time_delta)/1000000) "cpu(s)",round(sum(ELAPSED_TIME_DELTA)/1000000) "elp(s)",sum(disk_READs_DELTA) read, sum(iowait_delta) iowait, sum(DIRECT_WRITES_DELTA) direct,
sum(BUFFER_GETS_DELTA)/sum(EXECUTIONS_DELTA) buffer_Per_Exec
from dba_hist_sqlstat st, DBA_HIST_SNAPSHOT sn
where st.snap_id=sn.snap_id
and st.instance_number=sn.instance_number
and
BEGIN_INTERVAL_TIME > sysdate-60
and sql_id='&sql_id'
group by sql_id,PLAN_HASH_VALUE,to_char(BEGIN_INTERVAL_TIME,'yyyymmdd')
order by to_char(min(BEGIN_INTERVAL_TIME),'YYYYMMDD-hh24mi')
/
   SQL_ID              PLAN RUNDATE         EXEC START_TIME     END_TIME           BUFFER   PHY_READ     cpu(s)     elp(s)       READ     IOWAIT     DIRECT BUFFER_PER_EXEC
------------- ---------- --------- ---------- -------------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------
gpajb2bnac80b 3686252633 20190906     7853891 1600           2000             39072550     376832     412           413         80    2862227          0      4.97492899
gpajb2bnac80b 3686252633 20190909       91712 1630           1700               275592          0       4             4          4     114028          0      3.00497209
gpajb2bnac80b 3686252633 20190912       88236 0900           1000               265152          0       4             4          1      36222          0      3.00503196
gpajb2bnac80b 3686252633 20190917       83761 1100           1130               694741    1089536       8            11        133    2853173          0      8.29432552
gpajb2bnac80b 3686252633 20190919     7854321 1400           1930           3242896821    5586944      22644      22672        682   30364132          0      412.880607
gpajb2bnac80b 3686252633 20190923      167522 1330           1600               503318      16384       9             9          2     211777          0      3.00448896
gpajb2bnac80b 3686252633 20190924      167522 0930           1700               503330          0       9             9          0          0          0       3.0045606

这个表有206百万行，插入约为7百万。

select count(*) from PAY_RUN_RESULT_VALUES;

  COUNT(*)
----------
206322479

表和索引有32个哈希参数，索引基于列 (“INPUT_VALUE_ID”，“RUN_RESULT_ID”)
与上述历史统计信息相比，bufferPerExec突然从大约4.9 (运行日期: 20190906) 增加到412.8 (运行日期: 20190919)，然后恢复到正常的3。
我认为这与数据库升级无关，使用索引唯一扫描查询计划是相同的。
从AWR开始，该表被插入了700万次，但是在AWR中显示的此表没有巨大的更新/删除/插入。为什么突然每个执行在buffer gets上有这个巨大的跳转？我相信这是使用CPU并增加使用时间的原因。
请帮忙。

谢谢你的耐心。一个问题是我们不能从摘要中推断出细节。因此，当你在逻辑I/O上有一个很大的跳跃，我们不知道 * 所有 * 执行是否跳到了每个执行的412，或者 * 一些 * 跳到了一个更高的值，而其他的在那个时间段是可以的。

我能想到的几个可能性:

1) 您是否使用自动段空间管理 (ASSM)？

在11.2和12.1的时间范围内，插入存储在ASSM表空间中的表时出现了一些问题。在插入过程中可能会看到过多的逻辑IO，以及可能的后续查询。过多的IO不在表/索引块上，而是用于管理ASSM的位图块的管理。

尽管我的理解是，这些基本上已经在12.1.0.2的时间框架内解决了。

2) 您是否为优化器启用了自适应功能？

自适应功能可能会决定执行效率不高，然后后续执行会进行一些动态采样 (在决定保留相同的索引扫描之前)。尽管我不希望最终以如此庞大的总数结束。

3) 未提交交易

如果发生任何大的未解决的事务，那么一个简单的查询可能会做很多工作来撤消当前状态。有关此示例，请参阅Jonathan Lewis的博客文章:

https://jonathanlewis.wordpress.com/2009/05/14/consistent-gets/

老实说，以上所有这些我仍然不认为是根本原因:-(

它是你可以复制的东西吗？