In Oracle what does 'Buffer Gets' actually refer to?

Question

I'm dealing with an Oracle DBA at the moment, who has sent me some profiling he's done. One of the terms in his report is 'Buffer Gets', any idea what this actually means? My guess is bytes retrieved from a buffer, but I have no idea really. Here is some sample output:

  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
    137,948,100       31,495        4,380.0   98.4  6980.57   6873.46 4212400674
Module: JDBC Thin Client
SELECT fieldOne, fieldTwo, fieldThree, fieldFour, fieldFive FROM TableExample
WHERE fieldOne = 'example'

It would also be handy to know what 'Gets per Exec' means, as I guess they are related? I'm a programmer, but not a DBA.

Answer 1

Oracle storage is arranged into blocks of a given size (e.g. 8k). Tables and indexes are made up of a series of blocks on the disk. When these blocks are in memory they occupy a buffer.

When Oracle requires a block it does a buffer get. First it checks to see if it already has the block it needs in memory. If so, the in-memory version is used. If it does not have the block in memory then it will read it from disk into memory.

So a buffer get represents the number of times Oracle had to access a block. The reads could have been satisfied either from memory (the buffers) or have resulted in a physical IO.

Since physical IO is so expensive (compared to memory or CPU) one approach to tuning is to concentrate on reduction in buffer gets which is assumed will flow on to reduce physical IO.

Answer 2

Just to answer one part of your question that WW didn't:

"Gets per Exec" is simply the gets divided by the number of executions of the statement. You might have a statement that is very efficient but executed many times; this would have a high number of total buffer gets but a low gets per exec ratio.