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.