Pythian Group

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. First, here is the SQL that I used:

select LOADED_VERSIONS, OPEN_VERSIONS, USERS_EXECUTING,
DISK_READS, PARSE_CALLS, BUFFER_GETS from v$sql where sql_id = '6un665wcnkvbg';

Here’s the result:

LOADED_VERSIONS OPEN_VERSIONS USERS_EXECUTING DISK_READS PARSE_CALLS BUFFER_GETS
--------------- ------------- --------------- ---------- ----------- -----------
              1             1               1       2479           1  4293561854

And then again a few seconds later

LOADED_VERSIONS OPEN_VERSIONS USERS_EXECUTING DISK_READS PARSE_CALLS BUFFER_GETS
--------------- ------------- --------------- ---------- ----------- -----------
              1             1               1       2480           1     2075096

These details were obtained on Oracle V10.2.0.4.0, on AIX 5.3.