Logical I/O and Row Prefetching

Logical I/O is the sum of current reads and consistent reads, most of these can be satisfied from the buffer cache and any that cannot be satisfied from the cache would result in a phyisical read.Current reads are read of current version of the block.This can be used to resconstruct consistent versions of a block.There will be only one current version of the current read block that can exist at any point in the buffer cache.Consistent read can be potentially a read of historic version of the block, this can also be used to construct read consistent versions of a block.Oracle maintains multiple versions of such blocks in the buffer cache and is not a dirty block(updated blocks) on the other hand current read blocks would be dirty .We would be interested in logical reads per session or logical reads for a query.This is the sum of db block gets(current reads) and consistent gets (consistent reads).In real world we would be accessing the data through a client interface which could be sqlplus,jdbc,OCI etc.When we have queries returning more number of rows,not all the rows are returned in a single network round trip but would need multiple round trips.This essentially means that the session would be re-reading the same blocks again from its PGA.The number of rows fetched per round trip depends on the prefetch size for the respective interfaces.It can be visualized that a small prefetch size would lead to an increase in the number of round trips and hence degrade the perfromance.This does not mean that we should opt for a much higher prefetch size, there is always a trade off as a higher value of the same would result in increased network traffic and would have a negative impact on the performance.The best way would be to bench mark what would be the optimal prefetch size for each interface.Listed below are the mechanisms/calls which should be made use for setting the prefetch size.


OCI ==> OCI_ATTR_PREFETCH_ROWS
Pro*C ==> Host Array
JDBC ==> setRowPrefetch ()
PL/SQL ==> BULK COLLECT
SQL*Plus ==> SET ARRAYSIZE

I am providing reference to another blog which explains in great detail with examples on how to calculate consistent gets and its impact when using sqlplus.

http://viveklsharma.blogspot.com/2007/11/consistent-gets-myth.html



Below is another link which talks about the prefetch size and its impact when accessing the result set through a JDBC connection.

http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/RowPrefetchSample/Readme.html


In case an Object Relationship Mapping tool like Toplink or hibernate is used then prefetch-size and hibernate.jdbc.fetch_size may be used.