Buffer gets increase on the same SQL

同样一条SQL ,有的时候 buffer get 会暴增?! Oracle-L 中有人提了一个这样的问题：

I have a batch process that executes individual transactions, normally
a transaccion e.g. a simple select would take 8-10 buffer gets but in
the batch processing it takes 45 buffer gets.

Zhu Chao (Chao_ping,这家伙现在一篇文章都不写，只能从邮件列表里看到他的踪迹) 给了一个解释：

the job is processing some very hot blocks. So it always need to reverse back and find the CR block from buffer, so it will generate some more buffer gets for that execution.

如果是因为Hot Block 的原因,那么主要的症状应该是 Wait. 如果这个 SQL 在运行的时候数据已经发生了变化,那么为了维持一致性不可避免的会生成回滚,所以这个解释更为准确一些:

If a query does a consistent get on a block that has been changed since that query began or that had uncommitted changes at the time that that query began, then it is necessary to rollback those changes for read consistency. The consistent changes statistics counts the number changes rolled back. However, most consistent gets do not require any such rollback, and so it is normal for the number of consistent gets to be much greater than the number of consistent changes. This is reflected in the no work – consistent read gets statistic

我们不妨来做个例子.假定我们现在有两个Session,首先在第一个窗口做如下操作

SQL> create table t as select * from all_objects where rownum<1001;
Table created.
SQL> select owner,object_name from t where owner='SYS' for update;

在第二个Session,进行一个简单的统计:

SQL> SELECT COUNT(*) FROM T;
......
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
1055  consistent gets
0  physical reads
1352  redo size
379  bytes sent via SQL*Net to client
504  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

注意到产生了 1055 个一致读.接着我们rollback 第一个Session ,然后在第二个Session中继续这个统计:

SQL> SELECT COUNT(*) FROM T;
.....
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
29  consistent gets
0  physical reads
0  redo size
379  bytes sent via SQL*Net to client
504  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

只有 29 个一致读了.验证了前面的解释.

又: xzh 说”这样肯定不行的,再说生产库上不可能锁表这么久的”. 其实我这个例子不过是模拟了一个比较特殊的情况而已.邮件列表中提问者的场景是在一个批处理中的,这样的可能性是有的.

Google+