Over 4 Billion Buffer Gets?
Recently I was looking into a long-running statement and noticed a curious thing. One moment, I had just over 4 billion buffer gets, and the next I had around 2 million.
The statement was still processing and the number of physical reads was still rising.
I think the reason for this is that the internal place holder for this value is simply a 32-bit unsigned integer that has the largest number of 4294967295. But my version of Oracle is 64-bit; I thought that maybe it would use 64-bit integers.
So, beware that if you have had a statement running for some time, you cannot necessarily rely on the buffer_gets column in v$sql—it may be that it has run over the limit, been recycled, and is counting from zero again.
The evidence. Read the rest of this entry . . .
